Zachary Vincze Zachary Vincze - 1 year ago 64
PHP Question

Select latest topic from a category (SQL and PHP)

I want to be able to select the latest topic that was posted to a category by selecting it through an SQL query.

I have two tables that I'm going to use.

  • categories

  • topics

A topic row knows what category it belongs to because
should match with

I want to loop through all the categories and show the latest topic posted to them. It looks something like this.

while($row = $result->fetch_assoc()) {
echo $row['cat_name'] . ' Latest Post is ' . $row['topic_name'];

This would be an example of what it would look like if it was executed.

category1 Latest Post is "latest post"
category2 Latest Post is "latest post"
category3 Latest Post is "latest post"

and so on...

What SQL query could I use to select this?

Here are my table structures.


  • cat_id

  • cat_name

  • cat_description


  • topic_id

  • topic_name

  • topic_date

  • topic_cat

  • topic_by

Answer Source

This is what you need:

SELECT c.cat_name, t.topic_name, t.topic_date 
FROM categories c
LEFT JOIN topics t ON c.cat_id=t.topic_cat
WHERE topic_date = (
    SELECT MAX(t2.topic_date)
    FROM topics t2
    WHERE t2.topic_cat = t.topic_cat

This query will return only the latest topic from each category. Each result has fields:

cat_name | topic_name | topic_date

Once you've executed your query, you can run your loop as you wish:

while($row = $result->fetch_assoc()) {
    echo $row['cat_name'] . ' Latest Post is ' . $row['topic_name'];

Live demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download