Using an SQL result in a foreach loop

Question

I feel like I am missing something stupidly obvious here, I am trying to get the results of an SQL query and then using them in a loop. I feel like I am missing something stupidly obvious, I have tried it with and without the commented out line.

<?php
$sentToID = $_SESSION['userID'];

$query = "SELECT *
          FROM messages
          WHERE sentToID = '$sentToID'";

$results = mysql_query($query);
//$userData = mysql_fetch_array($results, MYSQL_ASSOC);

foreach ($results as $result){
    $messageID = $result['messageID'];
    $sentFromID = $result['sentFromID'];
    $subject = $result['subject'];
    $body = $result['body'];
    $dateTime = $result['dateTime'];

    $query = "SELECT usertype 
              FROM user
              WHERE userID = '$sentFromID'";
    $messageResult = mysql_query($query);
    $messageData = mysql_fetch_array($messageResult, MYSQL_ASSOC);

    $usertype = $messageData['usertype'];

    $query = "SELECT * 
              FROM $usertype
              WHERE userID = '$sentFromID'";

    $messageResult = mysql_query($query);
    $messageData = mysql_fetch_array($messageResult, MYSQL_ASSOC);

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

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

Any help would be greatly appreciated

When uncommented, shouldn't the foreach read `foreach($userData as $result)`? — Kenaniah, Dec 16 '11 at 18:29
We can't help you if you won't tell us what the problem is. Anyway, to work with the array you need to use the array, not the query! Meaning, you have `$result` in your `foreach` instead of `$userData` — Anonymous, Dec 16 '11 at 18:31

Michael Berkowski · Accepted Answer · 2016-05-06T16:43:49.527

5

You must first fetch your results into an array. Looks like you started to do this but commented it out.

$results = mysql_query($query);
//$userData = mysql_fetch_array($results, MYSQL_ASSOC);

$resultset = array();
while ($row = mysql_fetch_array($results)) {
  $resultset[] = $row;
}

// $resultset now holds all rows from the first query.
foreach ($resultset as $result){
 //... etc...

edited May 06 '16 at 16:43

answered Dec 16 '11 at 18:29

Michael Berkowski

267,341
46
444
390

score 5 · Answer 2 · answered Dec 16 '11 at 18:30

5

Instead of your foreach(), you should do something like this (see the mysql_query() manual page for more):

while($result = mysql_fetch_assoc($results)) {
    // your code
}

answered Dec 16 '11 at 18:30

WWW

9,734
1
29
33

score 2 · Answer 3 · answered May 17 '16 at 10:05

I don't have the reputation to comment, and the above answers are correct, but the php mysql_query() manual page says that

this extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used

So now a correct way would be:

while($result = mysqli_fetch_assoc($results)) {
    // your code
}

score 2 · Answer 4 · edited Dec 16 '11 at 19:18

another option

$num_rows = mysql_num_rows($result)
for ($i=0;$i<$num_rows;$i++) {
$row = mysql_fetch_assoc($result)
$messageID = $row['messageID'];
}

You can do anything from here.

Remember, 1- query into object like $result 2- fetch row at a time from the object into an array which reflects an entire row in the table given your query definition with associative keys or numeric 3- do something with the array

You will loop through the object row by row and put in $row as an array.

Cheers

score 0 · Answer 5 · edited Aug 28 '20 at 10:00

The return type of the $results = mysql_query($query); is a resource of type object. For example:

object(mysqli_result)#3 (5) { 
  ["current_field"] => int(0) 
  ["field_count"] => int(3) 
  ["lengths"] => NULL 
  ["num_rows"] => int(2) 
  ["type"] => int(0) 
}

Since, we are only interested with the table data. We have the pass the resource to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data according to PHP Docs

We cannot directly use it.

However, If you are having problem understanding the inner working of while loop i will give you an example.

<?php

    $s = 0;

    class deepe {
        function dis(){
            $a = array(2,3,4,5,6);
            $b = $GLOBALS['s'];
            if ( $b < count($a) )
                return $a[$b];
        }
    }

    $chk = new deepe();

    while($fac = $chk->dis())
    {
        echo $fac."<br />";
        $GLOBALS['s']++;
    }

?>

Using an SQL result in a foreach loop

5 Answers5

Linked