Query within while query

[slj90]

I have a query which displays statuses from users:

$result = mysql_query("SELECT * FROM stream ORDER BY stream_id DESC");



while($row = mysql_fetch_array($result)) {
$tha = $row['stream_time'];
$time = strtotime($tha);


  echo "<tr><td><b>" . $row['stream_username'] . "</b></tr></td>" . " <tr><td>" . $stream_pp . "</td><td>" . $row['stream_status'] . " " . humanTiming($time) . $msg;
  echo "<br></tr></td>";



}
echo "</table>";

I want to get the profile picture of each user, how would I add this code?

 

$query = "SELECT `profile_picture` FROM `users` WHERE `user_username` = '$status_username'";
$result = mysql_query($query) or die($query."<br/><br/>".mysql_error());
$stream_pp = mysql_result($result, 0, 0);

By simply just adding it within it only displays one result.

 

Any ideas?

Thanks

[Ch0cu3r]

You wouldn't have two separate queries. Instead you'd use a JOIN to get the user profile picture. An Example query

SELECT s.stream_username, s.stream_time, s.stream_status,   # get the username, time and status from the streams table
    u.profile_picture                                       # get the profile_picture from the users table
FROM stream s
LEFT JOIN users u ON u.user_username = s.stream_username    # join the users table, where the username matches in the user and stream tables
ORDER BY s.stream_id DESC

And you'd loop over the results as you normally would

$sql = 'SELECT s.stream_username, s.stream_time, s.stream_status,   
    u.profile_picture                                       
FROM stream s
LEFT JOIN users u ON u.user_username = s.stream_username 
ORDER BY s.stream_id DESC';

$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
    $tha = $row['stream_time'];
    $time = strtotime($tha);

    echo "<tr><td><img src=\"{$row['profile_picture']}\" /><b>" . $row['stream_username'] . "</b></tr></td>" . " <tr><td>" . $stream_pp . "</td><td>" . $row['stream_status'] . " " . humanTiming($time) . $msg;
    echo "<br></tr></td>";
}

echo "</table>";

[Jacques1]

A query in a query loop is almost always the wrong solution. You want a join:

SELECT
	-- something useful, not just "*"
FROM
	stream
	JOIN users ON stream.stream_username = users.user_username	-- you should get rid of those redundant prefixes
ORDER BY
	stream.stream_id DESC
;

[slj90]

Thanks for the replies.

 

Ch0cu3r, I have tried using your suggestion

 

<?php

include './include/connect.php';

error_reporting(E_ALL);
$sql = "SELECT s.stream_username, s.stream_time, s.stream_status, u.profile_picture FROM stream s LEFT JOIN users u ON u.user_username = s.stream_username ORDER BY s.stream_id DESC";


$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
 echo $row['s.stream_status'];
echo $row['u.profile_picture'];

}
?>

But get the error:

 

Notice: Undefined index: s.stream_status in /home/partyfm/public_html/play.php on line 14

Notice: Undefined index: u.profile_picture in /home/partyfm/public_html/play.php on line 15

What could be wrong?

Many thanks,

Edited May 26, 2014 by slj90

[Jacques1]

When you're not sure about the content of a variable, check it. This will tell you exactly what it does and does not contain:

var_dump($row);

You'll see that there is no key named “s.stream_status”. It's stream_status, because that's the name of the column. The “s” is just an internal qualifier which tells MySQL which table it should get the column from (because multiple tables may have the same columns).