[Mysql] Fetch all news and all comments

Question

Hi, I'm trying to code a query that gets all the news and all the comments for each news. My current query is :

SELECT n.*,
       c.* AS comments
  FROM news n
       JOIN comments c ON (c.news_id = n.id)

But when I fetch the query as an array it gives me a key by comment and I would like to have a key by news and all comments in a sub-array.

Something like:

Array
(
    [0] => Array
    (
        [id] => 1 // news' id
        ...       // rest of news' data
        [comments] = Array
        (
            [id] => 1 // comment's id
            ...       // rest of comments' data
        )
    ),
    ... // all other news
)

Thanks!

Answer 1

I'm assuming it's returning individual records and not data structures. You're going to need to group those records yourself into the datastructure. I would hold out for some example code if possible but this can't be done directly done through SQL. (as you didn't get back PHP directly from the DB either)

Are you using any sort of ORM? You may wish to consider looking at:

• Rocks, Open Source ORM for PHP 5.1 plus, free software (GNU LGPL)
• Doctrine, Open Source ORM for PHP 5.2.3, free software (GNU LGPL)
• Propel, ORM and Query-Toolkit for PHP 5, inspired by Apache Torque, free software (GNU LGPL)
• EZPDO, Open Source ORM for PHP 5.0.4 or newer, free software (BSD)
• DABL, Database class creator and Query builder, inspired by Propel but easier to install, free software
• Data Shuffler Data mapper implementation (New BSD)
• Outlet Open source ORM (beta) for PHP 5.1.6 or newer, uses an approach similar to Hibernate (New BSD)
• Coughphp Open source ORM for PHP5, uses code generation (It should be possible to hook up other database drivers, but out of the box only MySQL support is included.[1]) (FreeBSD)

http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software#PHP

Answer 2

You can't do that in one query - best to take the query you've got and post-process the resulting answer to get the data structure you need.

To elaborate further - any SQL query can only return a two dimensional array of data - one dimension for the columns, and one for the matching rows. In your case what you're actually after is more like a three dimensional table.

Note also that on your query as written will return all of the news data over and over again for each comment against each article. That's an inefficient use of bandwidth and resource from the database server.

It's probably more efficient to do it like this (in pseudo-code):

SELECT * FROM news
...
foreach ($rows as $row) {
  $row['comments] = array();
  $news[$row['id']] = $row;
}

SELECT * FROM comments
...
foreach ($rows as $row) {
  $news[$row['news_id']]['comments'][] = $row;
}

The first query gets all of the news articles and puts them in an array. The second query gets the comments, and accumulates a separate array within each news article's structure.

Answer 3

you can use inner join to get result in a single query.

Suppose your database structure is like :

tab_news : news_id, news

tab_cmt : cmt_id, news_id, cmt

now write query like:

select n.news, c.cmt from tab_news n inner join tab_cmt c on (c.news_id=n.news_id)