So, just for fun, I'm making a forum software that hopefully will help me learn more about PHP. It's not going to be huge or anything, just something for me to mess around with when I'm bored. I'm having some troubles though.
First off, I'll start by telling you the URL of my site so you can better see the issue at hand.
http://kb-web.co.cc/cms/forum/vf.php?id=1
So let me describe my problem. I am attempting to order the threads by which was posted in last. As you can see, the thread "TEst" was posted in most recent (15/08/2010 21:01 - compared to 13/08/2010 09:49). So, what I'd like to do is have the "TEst" thread come first, then the "YO YO YO" thread come second, etc etc. I have been trying to think of what I can possibly do for this, and I know what needs to be done (so I think) I just don't know how to apply it. First, I'll show you my database.
http://img.tl/i/zxzgm.png
Basically, I need to select the highest "id" field for each "thr_id". So as you can see, the thr_id of "4" has multiple posts, I need to select the highest id (in this case 15) and compare it to the other ones. So let's say I am comparing thr_id 4 to thr_id 8. I need to select ids "15" and "17" then compare the dates. If the date field for 15 is greater than 17 (1281678666 compared to 1281891700) then show the highest date first (in this case 17).
I don't know if this is completely clear to you, but it's how I'm thinking would be the best possible way. Here is the code I am currently using to select the threads.
$select_normal = mysql_query("SELECT *, forum_posts.id FROM `forum_thread`, forum_posts WHERE forum_thread.thr_id = '$get_id' AND forum_thread.sticky = '0' ORDER BY forum_posts.id");
while ($fetch_normal = mysql_fetch_array($select_normal)) {
$check_replies = mysql_num_rows(mysql_query("SELECT * FROM `forum_posts` WHERE `thr_id` = '$fetch_normal[id]'"));
$last_poster = mysql_fetch_array(mysql_query("SELECT `by`, `date` FROM `forum_posts` WHERE `thr_id` = '$fetch_normal[id]' ORDER BY `id` DESC LIMIT 0, 1"));
$date_lp = date("d/m/Y H:i", $last_poster['date']);
echo "<tr><td style='border-right: 1px solid #797979; padding:2px;'>";
$url = getURL();
if ($fetch_normal['locked'] == 1) {
echo "<img src='$url/images/icons/lock.png' alt='Thread Locked' title='Thread Locked'> ";
}
echo "<a href='vt.php?page=1&id=$fetch_normal[id]'>$fetch_normal[name]</a></td>
<td style='border-right: 1px solid #797979;'><div align='center'>$fetch_normal[byuser]</div></td>
<td style='border-right: 1px solid #797979;'><div align='center'>$check_replies</div></td>
<td style='border-right: 1px solid #797979;'><div align='center'>$fetch_normal[views]</div></td>
<td bgcolor=''><div align='center'>$date_lp<br />Last Post: <a href='vt.php?view=topic&id=349&#C1916'>$last_poster[by]</a></div></td>
</tr>";
}
Please let me know if I am doing this wrong. Thanks!
