Using an SQL result in a foreach loop
Solution 1
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...
Solution 2
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
}
Solution 3
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
Solution 4
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
}
Richie
Updated on July 05, 2022Comments
-
Richie almost 2 years
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