PHP MySQL Order By problem

I have been working on building a blog by hand to gain more experience with PHP. I have completed most of the main webpage so far, but I am experiencing a problem with the Order By statement in the comments section.

Here is the code that I believe is causing the problem:


<ul>
<?php
//This will be a stacked query that will first grab the current blogid, then grab the username from the user table and use that username to replace the userid in the comment table, and finally post both the user name and the user's comment (5 most recent)
//Start by grabbing the most recent blog
$query1 = "SELECT * FROM CurrentBlog ORDER BY BlogDate DESC LIMIT 1";
$data1 = mysqli_query($dbc, $query1)
or die("MySQL error: " . mysqli_error($dbc) . "<hr>\nQuery: $query1");
while ($row1 = mysqli_fetch_array($data1)) {
$blogid = $row1['BlogId'];
//Now make a query to grab the userid and the username from the Users table
$query2 = "SELECT * FROM Users";
$data2 = mysqli_query($dbc, $query2)
or die("MySQL error: " . mysqli_error($dbc) . "<hr>\nQuery: $query2");
while ($row2 = mysqli_fetch_array($data2)) {
$userid = $row2['UserId'];
$username = $row2['UserName'];
//Now make a query that grabs the 5 most recent comments, sets the username to the associated user id, and sorts the comments by date desc
$query3 = "SELECT * FROM Comments WHERE BlogID='$blogid' ORDER BY CommentDate DESC LIMIT 5";
$data3 = mysqli_query($dbc, $query3)
or die("MySQL error: " . mysqli_error($dbc) . "<hr>\nQuery: $query3");
while ($row3 = mysqli_fetch_array($data3)) {
$commentuserid = $row3['UserID'];
$commenttext = $row3['CommentText'];
if ($userid == $row3['UserID']) {
$commentuserid = $username;
echo '<li>';
echo '<h3>';
echo $commentuserid;
echo '</h3>';
echo '<p>';
echo $commenttext;
echo '</p>';
echo '<p>';
$Ctimestamp = $row3['CommentDate'];
$Cdate = date("d-m-Y", $Ctimestamp);
echo $Cdate;
echo '</p>';
echo '</li>';
}
}
}
}

?>
</ul>



And here are my results:
User 1
User 1's comment
12-10-2011
User 1
User 1's second comment
12-09-2011
User 2
User 2's comment
12-10-2011
User 3
This might appear as it it user 3's second comment
14-09-2011
User 3
User 3's comment
12-09-2011

Any help would be appreciated.

You've not really gone about this correctly. Did you plan your logic out before attempting the task? Currently what you're doing, is looking through the first 5 comments for EVERY user in your database, meaning if you have 20 users, you're looping through the same 5 comments 100 times. Obviously, very inefficient.

I do see what you were trying to do though - you're trying to populate a list of userid's and usernames so that you can fill that information into your comments. Firstly, you could use a mysql join OR simply run the user query and while loop on its own, and populate an array like - $users['userid'] = 'username'. That way, when you get your 5 comments, all you need to do is:

// Loop to populate users
$users = array();
while ($row2 = mysqli_fetch_array($data2)) {
$users[$row2['UserID']] = $row2['UserName'];
}

// Loop to get comments
while ($row3 = mysqli_fetch_array($data3)) {
echo '<li>';
echo '<h3>';
echo $users[$row3['UserID']]; // Echo's username for this comment
echo '</h3>';
echo '<p>';
echo $row3['CommentText'];
echo '</p>';
echo '<p>';
$Cdate = date("d-m-Y", $row3['CommentDate']);
echo $Cdate;
echo '</p>';
echo '</li>';
}

Of course, that's not the most efficient way of doing things. The most efficient way would be to use a mysql join to select everything you need from the Comments table, collecting the user ids and then using that in a SELECT query WHERE userid=id OR userid=id2 etc. etc. to get a list of users that only commented in that blog :). That's something for you to play with ;)

Thanks for the quick reply BluePanther. I have limited experience with PHP, so I was just applying what I already knew. I had not yet learned to use an array like you showed me, so your code helped me out alot.

Do you have any suggestions on books I can read to gain additional PHP knowledge?

I never really used any books - I just used online resources and the PHP Manual to build on my previous programming experience in different languages. There's a good set of learning resources in a sticky at the top of this forum though, I'm sure there's plenty of gems in there :).