List Categories with Recent Comments / Actions with MYSQL

Question

I am developing a site where users can post comments, and each comment is classified. I have a page where users can view a list of all categories on the site with the last 5 comments posted to them.

Information I need to get from the database:

• Category List
  • 5 comments in each category

This is what I have right now (simplified 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 will look like this (if 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...

, , , .

. - , ?

. , , , LIMIT 5, amoutn .

Answer 1

, MySQL.

- 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 )

, MySQL LIMIT . , , . , . , , .

, :)

: LEFT JOIN , ( , , ) , .

Answer 2

:

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

.

Answer 3

- , ?

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

. *, , ( ).

EDIT: , , , .

, , - , , com.created_at > [date], . , 5 , 5 , -.

Answer 4

, ,

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

, PDO

HTML :

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

    </li>


</ui>

.