[PHP] While Loops in MySQL

[imdead]

hey guys, in my script i displays status' the users have posted. However when the users status' got over like 20, i noticed it was affected load times greatly as lots of SQL is looping.

 

<?php
  	$sql =  sqlcount(mysql_query("SELECT * FROM statuses LEFT JOIN users ON statuses.userid = users.id ORDER BY statusid DESC"));
while ($row = mysql_fetch_assoc($sql)) {
$status_id = htmlspecialchars($row['statusid']);
$status = htmlspecialchars($row['status']);
$time_posted = htmlspecialchars($row['time_posted']);
$status_userid = htmlspecialchars($row['userid']);
		$sql2 = sqlcount(mysql_query("SELECT username,avatar_url FROM users WHERE id=$status_userid LIMIT 1"));
while ($row2=mysql_fetch_array($sql2)){
$usernamestatus = $row2['username'];
$avatar_url = $row2['avatar_url'];
if (empty($avatar_url)) {
$avatar = "<img src='$directory_self/images/default_avatar.gif' height='50px' width='50px' />";
}else{
$avatar = "<img src='$directory_self/photos/$usernamestatus/$avatar_url' height='50px' width='50px' />";
}
echo '<div id="content">';
echo "<br />";
echo $avatar;
echo "<a href=\"profile.php?id=$status_userid\">". $usernamestatus ."</a>";
echo "<br />";
  $status = str_replace(array_keys($bbcode), array_values($bbcode), $status);
echo $status;
	if($userid == $status_userid){
	echo " <span class=x><a href=delete_status.php?id=$status_id>x</a></span>";
	}
	echo "<br />";
echo format_date($time_posted);
echo "<br />";
echo "<br />";
?>
<div id="like<?php echo"$status_id"; ?>"><a href="#" style="text-decoration: none" class="like" id="<?php echo"$status_id"; ?>"><span class="like_b"> <?php echo LANG_LIKE; ?> </span></a></div>
  <div id="unlike<?php echo"$status_id"; ?>" style="display:none"><span class="youlike_b"> <?php echo"$user"; echo  LANG_LIKES_THIS; ?> </span><a href="#" class="unlike" id="<?php echo"$status_id"; ?>"><span class="unlike_b"> <?php echo LANG_UNLIKE; ?> </span></a></div>
<?php
echo "</div>";
	}
}
?>

 

Is there a way to shorten this code and make it run faster, without as many queries?

Cheers

[jcbones]

The 2 queries can be made into 1, and is basically there anyway.

"SELECT s.statusid, s.status, s.time_posted, s.userid, u.username, u.avatar_url FROM statuses AS s LEFT JOIN users AS u ON s.userid = u.id ORDER BY s.statusid DESC"

 

Get rid of the second while loop, and run everything in the first.  Don't forget to point your $row2 array back to $row.

[imdead]

Thankyou! that second query was the one which was causing the extra query on each status, so now i'm constantly loading lesser queries. Cheers for that!

if anybody wants to see the faster code it's,

$sql =  sqlcount(mysql_query("SELECT s.statusid, s.status, s.time_posted, s.userid, u.username, u.avatar_url FROM statuses AS s LEFT JOIN users AS u ON s.userid = u.id ORDER BY s.statusid DESC"));
while ($row = mysql_fetch_assoc($sql)) {
$status_id = htmlspecialchars($row['statusid']);
$status = htmlspecialchars($row['status']);
$time_posted = htmlspecialchars($row['time_posted']);
$status_userid = htmlspecialchars($row['userid']);
		$usernamestatus = $row['username'];
$avatar_url = $row['avatar_url'];
if (empty($avatar_url)) {
$avatar = "<img src='$directory_self/images/default_avatar.gif' height='50px' width='50px' />";
}else{
$avatar = "<img src='$directory_self/photos/$usernamestatus/$avatar_url' height='50px' width='50px' />";
}
echo '<div id="content">';
echo "<br />";
echo $avatar;
echo "<a href=\"profile.php?id=$status_userid\">". $usernamestatus ."</a>";
echo "<br />";
  $status = str_replace(array_keys($bbcode), array_values($bbcode), $status);
echo $status;
	if($userid == $status_userid){
	echo " <span class=x><a href=delete_status.php?id=$status_id>x</a></span>";
	}
	echo "<br />";
echo format_date($time_posted);
echo "<br />";
echo "<br />";
?>
<div id="like<?php echo"$status_id"; ?>"><a href="#" style="text-decoration: none" class="like" id="<?php echo"$status_id"; ?>"><span class="like_b"> <?php echo LANG_LIKE; ?> </span></a></div>
  <div id="unlike<?php echo"$status_id"; ?>" style="display:none"><span class="youlike_b"> <?php echo"$user"; echo  LANG_LIKES_THIS; ?> </span><a href="#" class="unlike" id="<?php echo"$status_id"; ?>"><span class="unlike_b"> <?php echo LANG_UNLIKE; ?> </span></a></div>
<?php
echo "</div>";
	}
?>