So, just for fun, Im making a forum software that hopefully will help me learn more about PHP. Its not going to be huge or anything, just something for me to mess around with when Im bored. Im having some troubles though.

First off, Ill 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 Id 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 dont know how to apply it. First, Ill 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 lets 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 dont know if this is completely clear to you, but its how Im thinking would be the best possible way. Here is the code I am currently using to select the threads.

PHP Code: $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! So, just for fun, Im making a forum software that hopefully will help me learn more about PHP. Its not going to be huge or anything, just something for me to mess around with when Im bored. Im having some troubles though.

First off, Ill 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 Id 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 dont know how to apply it. First, Ill 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 lets 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 dont know if this is completely clear to you, but its how Im thinking would be the best possible way. Here is the code I am currently using to select the threads.

PHP Code: $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!

View Complete Thread with Replies