I have a simple article and comments system with the following tables:

Articles table:

ID | Writer | Text
 1 | Bob    | good article
 2 | Marley | read this

Comments table:

ID | Article_ID | Comment
 1 |      1     |  i love this article
 2 |      1     |  good one
 3 |      2     |  waiting for more

I want to select each article with its comments underneath it. I use the following query:

SELECT * FROM articles LEFT JOIN comments ON articles.ID = comments.Article_id

The expected results:

Article 1: good article
Comments: 1 - i love this article
          2 - good one

Article 2: read this
Comments: 3 - waiting for more

What I get:

Article 1: good article
Comments: 2 - good one

Article 2: read this
Comments: 3 - waiting for more

So how do I select each article with its comments and order the articles by id descending and the comments by their ids descending also?

Thanks

Well,

Actually both your expected result and the result are wrong. How do you handle your data?

The expected result is wrong because any SQL query returns a table as a result. It cannot contain another table in a row.

Your query returns the following result:

Article 1: good article

Comments: 1 - i love this article

Article 1: good article

Comments: 2 - good one

Article 2: read this

Comments: 3 - waiting for more

Witch should be enough for you get the relevant data for your page.

But I would suggest breaking it to separate queries, article usually contains a lot of data you don't want to duplicate in your result.

Like that (untested code):

$articles_sql="SELECT * FROM articles";
$comments_sql="SELECT * FROM comments";

$articles=$db->query($sql).getResults();

$comments=$db->query($sql).getResults();

foreach($articles as &$article){
    $article['comments']=array();
    foreach ($comments as $comment){
        if($article['id']==$commnet['article_id']){
            $article['comments'][]=$comment;
        }
    }
}

Hope it helps!

more