Mysql Join, need help formatting a query

Question

I'm sorry if this is a repeat, but I can't find anything that really answers my question as it's rather specific.

I am making a forum using php and mysql, and so far so good, but I want to sort the threads in each category based on the date of their most recent post.

My database structure is this:

Table: threads
Fields: name, f_id, t_id

Table: posts
Fields: author, date, post, t_id, p_id

threads has the name of the thread, the f_id of the forum it is in, and it's own auto-increment thread id. posts has the author, date, post, t_id of the thread it is in, and the auto-increment post id. To this end, the second post will always be a higher ID than the first post, even if a post is made in another thread in between.

My issue comes when I want to display all the threads in a particular forum. I currently use this code:

$t_query = mysqli_query($link, "SELECT name, t_id FROM threads WHERE f_id = ".$f_id."");

echo "<table><tr><td>Name</td><td>Author</td><td>Date</td></tr>";
while($rowt = mysqli_fetch_array($t_query)) {
    $p_query = mysqli_query($link, "SELECT author, DATE_FORMAT(date, '%r on %c/%e/%Y') as date_f, p_id FROM posts WHERE t_id = ".$rowt['t_id']." LIMIT 1");
    echo "<tr><td><a href=\"../pages/post.php?t_id=",htmlentities($rowt['t_id']),"\">",htmlentities($rowt['name']),"</a></td>";
        while($rowp = mysqli_fetch_array($p_query)){
            echo "<td>",htmlentities($rowp['author']),"</td><td>",htmlentities($rowp['date_f']),"</td></tr>";
}
echo "</table>";

That works fine and all, except I can't order by date because the date is in posts and my first loop grabs from threads. What I need to accomplish is a join that will grab the date of the highest p_id from posts where t_id equals the t_id from threads.

After re-reading that it seems a bit confusing but I hope someone can help me with the join query and tweaking my php.

Thanks, sharf.

Answer 1

I assume you want to sort the threads listing by the date of the most recent post in it, right? Join onto posts twice, once for the most recent date, and a second time to make sure no newer post exists in it. For example...

SELECT t.name, t.t_id
FROM threads t
JOIN posts p1 ON (p1.t_id = t.t_id)
LEFT JOIN posts p2 ON (p2.t_id = t.t_id AND p2.p_id > p1.p_id)
WHERE p2.p_id IS NULL
ORDER BY p1.date;