Listing categories with the latest comments/activities with MYSQL

Question

I am developping a site where users can post comments and each comment is categorized. I have a page where users can go and see a list of all the categories on the site with the latest 5 comments posted in them.

The information I need to retrieve from the database is:

• A list of categories
  • 5 comments in each categories

This is what I have right now (simplified to basic PHP):

echo "<ul>";
$query = mysql_query("SELECT * FROM categories");
while($result = mysql_fetch_assoc($query)){

    echo "<li><h2>{$result['category_name']}</h2>";

    $query_comments = mysql_query(
                                 "SELECT * FROM comments WHERE ".
                                 "category_id = '{$result['id']}' ".
                                 "ORDER BY created_at DESC LIMIT 5");
    while($result_comments = mysql_fetch_assoc($query_comments)){
        echo "{$result_comments['username']} wrote {$result_comments['text']} on {$result_comments['created_at']}<br>";
    }

    echo "</li>";

}
echo "</ul>";

It would look like this (assuming my categories are Fruits name)

Apple
    Jay wrote blah blah blah - August 5, 2009
    Bob wrote hello hello hello - August 5, 2009
    Tom wrote super super - August 5, 2009
    Edward wrote no no no - August 5, 2009
    Kaysie wrote super no! - August 5, 2009

Orange
    Cassie wrote ye ye ye ye - August 5, 2009
    Alfonce wrote whoohoo - August 5, 2009
    Arthur wrote love oranges - August 5, 2009
    Alice wrote yes yes yes - August 5, 2009
    Xavier wrote Lorem ipsum dolor sit amet - August 5, 2009

Strawberry
    Chris wrote Lorem ipsum dolor sit amet - August 5, 2009
    Hubert wrote Lorem ipsum dolor sit amet - August 5, 2009
    Martin wrote Lorem ipsum dolor sit amet - August 5, 2009
    Lyon wrote Lorem ipsum dolor sit amet - August 5, 2009
    Paris wrote Lorem ipsum dolor sit amet - August 5, 2009

Blueberry
    etc...

The problem is, if there are a lot of categories, I will have performance issues, especially if there are a lot of users using the site.

I am struggling to find way to reduce the amount of queries needed. Does anyone has an idea of how I could do that ?

UPDATE I tried to LEFT JOIN the category table with the comments table however, I didn't find a way to limit the amount of comments per category because if I use LIMIT 5, it only limits the amoutn of comments returned.

Answer 1

I talked to a database guy and it turns out to be a bit of a pain in MySQL.

Something like this would work well in PostgreSQL:

SELECT * FROM categories
LEFT JOIN comments ON categories.id = comments.category_id
WHERE comments.id IS NULL OR
comments.id IN ( SELECT id FROM comments AS a2 WHERE categories.id = a2.category_id ORDER BY id DESC LIMIT 5 )

Unfortunately MySQL doesn't support LIMIT in subqueries. He scratched his head and said there's a workaround available but it didn't sound pretty. At that point I figured you may as well use multiple queries. If it's a performance issue it could be data that you cache temporarily.

Sorry, not much help :)

Old wrong answer: Try using a LEFT JOIN in your query, with categories on the left (so all categories are always returned, regardless of if they have comments) and the comments table on the right. This will reduce it to one query.

Answer 2

You could use a join:

SELECT categories.category_name, comments.*
FROM comments 
LEFT JOIN categories ON categories.category_id=comments.category_id

and change the way you iterate through the results.

Answer 3

Something like this, perhaps?

SELECT cat.*, com.*
FROM categories cat, comments com
WHERE com.categoryid=cat.id
ORDER BY cat.category_name ASC, com.created_at DESC

Note: It would be wiser to expand the * to select only what you need (and also remove any chance of ambiguity).

EDIT: after reading more closely I don't think you want this or any of the other answers, since it will grab every comment from the database, not just the latest ones.

Best solution I can think of right now is for you to choose how long ago is ignorable, and add com.created_at > [date] to limit to total number of comments selected. This may mean some categories don't show 5 comments even when the user has made 5 or more, but a long time ago.

Answer 4

I can't see all your code, but I really think that you should make a function

get_categories()
{
    //PSEUDO: $results_array;
    //PSEUDO: return $results_array = mysql_results;
}

If you have time, I would suggest Migrating to PDO

your HTML would then be:

// rough PSEUDO Code
<ui>
    <li>
         <?php 
             foreach ($results_array as $key => $value)
             {
                 echo(htmlentitites($value);
             }
         ?>

    </li>


</ui>

This way you move your code to a more organized structure.