So I have fetched data from a MySQL database and I want to assign it to a variable based on an nth row. Only the bottom three rows are filtered and I want to assign values from the firs and last row to two variables.
$sql3 = "SELECT * FROM login_attempts WHERE login_attempt_user_id=? AND login_attempt_result=? ORDER BY login_attempt_id DESC LIMIT 3;";
$stmt3 = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt3, $sql3)) {
echo 6;
exit();
} else {
mysqli_stmt_bind_param($stmt3, "ss", $login_attempt_user_id, $login_attempt_success);
mysqli_stmt_execute($stmt3);
$result2 = mysqli_stmt_get_result($stmt3);
if (mysqli_num_rows($result2) > 0) {
while ($row2 = mysqli_fetch_assoc($result2)) {
$test1 = $row2['login_attempt_time'][0];
$test2 = $row2['login_attempt_time'][2];
echo $test1.$test2;
exit();
}
} else {
//echo 4;
exit();
}
}
The section of code is within the while loop and I realize that what I am attempting is for arrays. I want to do something similar to achieve my goal.
My code is not assigning the database entry to the variable: $test = $row2['login_attempt_time'] assigns the last value of the fetched row, however $test = $row2['login_attempt_time'][0] only returns the value 1.
Would anyone please be able to help with this?
Fetch all (three) rows into an array (2-dimensional) with mysqli::fetch_all(). Then get the first and the last elements (rows) with reset() and end().
Change
while ($row2 = mysqli_fetch_assoc($result2)) {
$test1 = $row2['login_attempt_time'][0];
$test2 = $row2['login_attempt_time'][2];
echo $test1.$test2;
exit();
}
to
$data = $result2->fetch_all(MYSQLI_ASSOC);
$variable1 = reset($data); // first row
$variable2 = end($data); // last row
Remember, this mysqli_fetch_assoc($result2)) will fetch one row from the database. So the first time, $row2 will be the contents of the first row, the second time $row2 is the second row, etc. Perhaps add a counter to the loop to know when to populate $test1 and $test2, as with an if condition.