Zachary Vincze Zachary Vincze - 4 months ago 7
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
topics.topic_cat
should match with
categories.cat_id
.

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.

categories


  • cat_id

  • cat_name

  • cat_description



topics


  • topic_id

  • topic_name

  • topic_date

  • topic_cat

  • topic_by


Answer

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