Richie Richie - 6 months ago 9
SQL Question

Using an SQL result in a foreach loop

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

Answer

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
}