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.
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
topics
This is a more efficient query that gets you the same result. It will perform better because the subquery is uncorrelated (doesn't use any data) from the outer query
SELECT cat_name, topic_name, latest as topic_date
FROM categories c
LEFT JOIN (
SELECT topic_name, topic_cat, MAX(topic_date) latest
FROM topics
GROUP BY topic_cat
) as t
ON c.cat_id = t.topic_cat
You asked about LEFT JOIN vs JOIN: The first means that if any category has no topic, you still want it included in results. The topic name and date will just be null. JOIN means that you want results to include only categories with a matching topic. So you can alter either of my queries to your liking.
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'];
}