CODEWITHSUNDEEP
phpmysql
Gary
Gary

Reputation: 1011

MySQL query within another query's while loop in PHP

I have the following code:

$query = mysql_query("SELECT * FROM activity ORDER BY activity_time DESC LIMIT 50");
while($result = mysql_fetch_array($query)) {
    extract($result);
    if ($activity_type == "discussion") {

        $query = mysql_query("SELECT * FROM discussions WHERE discussion_uuid = '$activity_ref'");
        while($result = mysql_fetch_array($query)) {
            extract($result);
            echo $discussion_user . " said:<br>" . $discussion_text . "<br>";
        }

    } elseif ($activity_type == "file") {

    }
}

But it just returns the last row. My goal is to have a chronological list of "activities" each displayed slightly differently depending on their type.

Upvotes: 1

Views: 16597

Answers (2)

Wizzard
Wizzard

Reputation: 12712

Your using $query and $result twice so the second loop is overwriting the result of the first and stopping...

$query = mysql_query("SELECT * FROM activity ORDER BY activity_time DESC LIMIT 50");

and

$query = mysql_query("SELECT * FROM discussions WHERE discussion_uuid = '$activity_ref'");

same with $results var...

I would suggest you change to $query and $query2 but best to use something like

$activies = mysql_query("SELECT * FROM activity ORDER BY activity_time DESC LIMIT 50");
while($activity = mysql_fetch_array($activies)) {

and 

$discussions = mysql_query("SELECT * FROM discussions WHERE discussion_uuid = '$activity_ref'");
while($discussion = mysql_fetch_array($discussions)) {

I would also avoid using extract - as you might be overwriting vars your not expecting to...

Upvotes: 4

Mitch Dempsey
Mitch Dempsey

Reputation: 39939

You have to create another connection to the database so that you can run them at the same time.

OR

You can load the results of the first one into an array, and then just loop through the array.

Upvotes: 1

Related Questions