I am developing a CMS with articles and categories. I am making a feature for the admin panel that shows all the categories. In the table, I would like to include each category's latest article. My traditional method is to select just the categories in one query, loop through the results, and then inside the category loop have a query that gets the information on the latest article... So something like...
$query = "
SELECT category_id, title, description
FROM categories
ORDER BY title ASC";
if($query = mysql_query($query)){
while($cat=mysql_fetch_assoc($query)){
$query_article = "
SELECT article_id, category_id, title
FROM articles
WHERE category_id = ".$cat['category_id']."
ORDER BY article_id DESC
LIMIT 1";
$last = mysql_fetch_assoc($query_article);
}
}
How could I use joins and/or subqueries to achieve my goal of combining these two queries into one? I have come up with a query which is below:
SELECT
c.category_id, c.title, c.description,
a.article_id, a.category_id, a.title, COUNT(a.article_id) AS total_articles
FROM article_categories AS c
LEFT JOIN articles AS l ON (
SELECT
article_id AS article_id, category_id, title AS article_title
FROM articles AS l
WHERE l.category_id = c.category_id
ORDER BY l.article_id
DESC LIMIT 1)
LEFT JOIN articles AS a ON (c.category_id = a.category_id)
GROUP BY c.category_id
ORDER BY c.title ASC
When I use the above query, I get an error saying "Operand should contain 1 column(s)"
I would also like to add something that counts the total number of articles in the category.
Thanks for your time!