Using an SQL result in a foreach loop

Using an SQL result in a foreach loop

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Iterating Over MySQL Result Sets Without fetch_array

A common stumbling block in legacy PHP development involves misunderstanding how mysql_query() handles result sets compared to modern PDO or MySQLi drivers. The code snippet provided demonstrates a classic scenario: fetching a list of messages from a database and attempting to iterate over them using a foreach loop. However, the logic fails immediately because mysql_query() returns a resource representing a dataset, not an array.

When you execute $results = mysql_query($query), PHP does not populate $results with the row data. Instead, it returns a cursor that points to the first record in the database result set. To convert this cursor into usable data inside a foreach loop, you must explicitly fetch each row using functions like mysql_fetch_array(). Without this step, the foreach ($results as $result) syntax iterates over the internal state of the query resource rather than the actual message records, leading to undefined index notices or empty variables.

The Role of mysql_fetch_array

In the provided example, the commented-out line $userData = mysql_fetch_array($results, MYSQL_ASSOC); highlights exactly where the data retrieval is missing. While that specific line fetches only one row (the first one), a foreach loop requires a mechanism to iterate through all rows until the cursor reaches the end of the dataset.

The correct approach involves wrapping your iteration logic in a while loop or using mysql_fetch_array() directly within the loop structure. Here is how you would refactor the inner loop to correctly process every message:

$sentToID = $_SESSION['userID'];

$query = "SELECT * FROM messages WHERE sentToID = ?"; // Use prepared statements to prevent SQL injection
$results = mysql_query($query, $params);

while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
    $messageID = $row['messageID'];
    $sentFromID = $row['sentFromID'];
    $subject = $row['subject'];
    $body = $row['body'];
    $dateTime = $row['dateTime'];

    // Fetch sender type
    $typeQuery = "SELECT usertype FROM user WHERE userID = ?";
    $typeResult = mysql_query($typeQuery);
    $typeData = mysql_fetch_array($typeResult, MYSQL_ASSOC);
    
    $usertype = $typeData['usertype'];

    // Dynamic table selection based on type
    if ($usertype == "jobseeker") {
        $table = "jobseeker";
    } elseif ($usertype == "employer") {
        $table = "employer";
    } else {
        continue; 
    }

    // Fetch specific user data from the dynamic table
    $userDataQuery = "SELECT forename, surname, companyName FROM {$table} WHERE userID = ?";
    $userDataResult = mysql_query($userDataQuery);
    $userData = mysql_fetch_array($userDataResult, MYSQL_ASSOC);

    if ($usertype == "jobseeker") {
        $forename = $userData['forename'];
        $surname = $userData['surname'];
        
        echo "<div><p>{$forename} {$surname}</p><p>Subject: {$subject}</p><p>Body: {$body}</p></div>";
    } elseif ($usertype == "employer") {
        $forename = $userData['forename'];
        $surname = $userData['surname'];
        $companyName = $userData['companyName'];

        echo "<div><p>{$forename} {$surname} - {$companyName}</p><p>Subject: {$subject}</p><p>Body: {$body}</p></div>";
    }
}

Critical Security and Modernization Issues

Beyond the immediate syntax error regarding result set iteration, the code relies on several deprecated practices that pose significant security risks. The use of mysql_query() has been deprecated in PHP 5.5 and removed entirely in PHP 7.0. Furthermore, constructing SQL queries by concatenating variables directly into the query string—such as WHERE userID = '$sentFromID'—is vulnerable to SQL injection attacks.

Developers should transition to MySQLi or PDO immediately. These extensions support prepared statements, which separate the SQL logic from the data. This prevents malicious users from injecting arbitrary code through input fields like $sentFromID. For instance, using PDO would look like this:

$stmt = $pdo->prepare("SELECT * FROM messages WHERE sentToID = :id");
$stmt->execute(['id' => $_SESSION['userID']]);
foreach ($stmt as $row) {
    // Process row directly without explicit fetch_array call needed for iteration
}

In this PDO example, the foreach loop works natively because the iterator interface handles the fetching automatically. This eliminates the need for manual mysql_fetch_array calls and reduces the potential for logic errors when iterating over large datasets.

If you are maintaining legacy codebases that cannot yet migrate to PDO, ensure that every mysql_query() call inside a loop is followed by a fetch function if you intend to iterate manually. Never attempt to iterate directly on the raw result resource returned by mysql_query.

Our network