从MySQL数据库获取数据后从第n行获取列数据并分配给变量

Question

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?

Answer 1

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