Select articles and join its comments underneath it

Dev Shed Forums Sponsor:

January 25th, 2013, 04:07 PM

butlimous

Registered User

Join Date: Jan 2013

Posts: 5

Time spent in forums: 1 h 26 m 49 sec
Reputation Power: 0

Select articles and join its comments underneath it

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

Articles table:

Code:

     id | writer  | text
     1  | Bob    | first article
     2  | Marley | second article

Comments table:

Code:

 
    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:

Code:

 SELECT * FROM articles LEFT JOIN comments ON articles.id = comments.article_id

The results I get:

Code:

articles.id   | articles.writer | articles.text  | comments.id | comments.article_id | comments.comment
     1             | Bob             | first article  |    1        |         1           | i love this article
     1             | Bob             | first article  |    2        |         1           | good one  
     2             | Marley          | second article |    3        |         2           | waiting for more

What I want:

Code:

articles.id   | articles.writer | articles.text  | comments.id | comments.article_id | comments.comment
     1            | Bob             | first article  |    1        |         1           | i love this article
    NULL          | NULL            |    NULL        |    2        |         1           | good one  
     2            | Marley          | second article |    3        |         2           | waiting for more

How do I select each article once and then the comments? Like in the first article, it has 2 comments, how to avoid the article text, writer, and id from being outputted 2 times for each comment?

Thanks

January 25th, 2013, 04:43 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,870

Time spent in forums: 1 Month 2 Weeks 2 Days 22 m 32 sec
Reputation Power: 813

Hi,

do the formatting (in your case leaving out repeated values) in your application, not with SQL.

I think this is common misunderstanding caused by spreadsheet programs like Excel. SQL tables and result sets are supposed to hold data, not be pretty. In your case, (mis-)using NULL in the sense of "see above" doesn't even make sense, because your set isn't ordered. So there is no "above".

Use your JOIN to get the raw data. And then use your application to format the data in any way you want. By the way, rather than putting everything in a big table (with a lot of repetitions), I'd have the article data on top and a table of all comments below that. But that's up to you, of course.

January 25th, 2013, 09:43 PM

butlimous

Registered User

Join Date: Jan 2013

Posts: 5

Time spent in forums: 1 h 26 m 49 sec
Reputation Power: 0

Hey

I sort results but didn't add it to the query as it's out of scope of the question. Regarding data output I display each article then the comments below it not in a table like that...this is only for illustration. I can easily display the article only once but it doesn't make sense retrieving data from sql and then not displaying it...also this redundant data (repeated values) add to the memory and slow down loading the page...thats why i'm searching for a query to get only the data i need without repeated values.

Thanks

January 25th, 2013, 11:47 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,870

Time spent in forums: 1 Month 2 Weeks 2 Days 22 m 32 sec
Reputation Power: 813

No offense, but I think you're really on the wrong track with that "optimization".

Have you actually done benchmarks? How many comments will you even have? I mean, more than 1,000 on a single page would be pretty much unusable.

Unless you fetch thousands of rows all into one big array, this is a complete waste of time, because the used memory doesn't even add up (PHP has a garbage collector to take care of that). And if you do collect all rows, that would be your problem.

I understand that it might seem strange to you that the values are repeated. But this is how joins in the relational database model work. If you don't like it, you might have to look for a different model.