MySQL查询后如何将指定行字段值赋值给PHP变量？

Let's break down what's going wrong first, then fix the code:

The Core Issue #

Your while ($row2 = mysqli_fetch_assoc($result2)) loop iterates one row at a time—each $row2 holds only one record from your query. So $row2['login_attempt_time'] is a single string (not an array of times), and using [0] just grabs the first character of that string. Also, since you exit() inside the loop, you're only ever processing the first row anyway, which means you're not capturing all 3 records at all.

The Solution #

First, collect all 3 records into an array, then pull the first (latest) and last (oldest of the 3) records' login_attempt_time values. Here's the revised code:

$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);
    
    // Collect all matching rows into an array
    $loginAttempts = [];
    while ($row2 = mysqli_fetch_assoc($result2)) {
        $loginAttempts[] = $row2;
    }
    
    // Check if we have at least one record (though your query uses LIMIT 3, could be fewer)
    if (!empty($loginAttempts)) {
        // Get the FIRST record (latest attempt, since we sorted DESC)
        $latestAttemptTime = $loginAttempts[0]['login_attempt_time'];
        
        // Get the LAST record (oldest of the 3 returned attempts)
        $oldestOfThreeTime = end($loginAttempts)['login_attempt_time'];
        
        // Test the output
        echo $latestAttemptTime . " " . $oldestOfThreeTime;
    } else {
        // No matching records
        // echo 4;
        exit();
    }
}

Key Changes Explained #

• Collect all rows: We initialize $loginAttempts as an empty array, then push each row into it during the loop. This lets us access any record by index later.
• Access first/last records:
  • $loginAttempts[0] gives the first (newest) record because we sorted by login_attempt_id DESC.
  • end($loginAttempts) moves the array pointer to the last element and returns it—this works even if we get fewer than 3 records (e.g., only 2 matches).
• Removed early exit: The original exit() inside the loop stopped processing after the first row, which prevented capturing all records.

Optional: Directly Query for the Two Times #

If you only need the first and last times (not all 3 records), you could optimize the query to avoid fetching extra data. For example:

SELECT 
    (SELECT login_attempt_time FROM login_attempts WHERE login_attempt_user_id=? AND login_attempt_result=? ORDER BY login_attempt_id DESC LIMIT 1) AS latest_time,
    (SELECT login_attempt_time FROM login_attempts WHERE login_attempt_user_id=? AND login_attempt_result=? ORDER BY login_attempt_id DESC LIMIT 1 OFFSET 2) AS oldest_of_three_time;

This would return a single row with both times directly, but you'd need to bind the parameters twice. The first approach is simpler if you might need the other record data later.

内容的提问来源于stack exchange，提问作者Roelof Coertze