Getting data from multiple tables into single row while

Question

I'm trying to retrieve data from 2 tables and combine multiple rows into a single while loop

posts

post_id content          
------  -------      
   1    content1                     
   2    content2           
   3    content3  
   4    content4


comments

id      post_id   content   
------  ------    ------              
   1      1        Wharton university           
   1      2        Yale University

sql code I write

    mysqli_query( $connect, "SELECT * FROM `posts`
 INNER JOIN  comments ON posts.post_id =  comments.post_id  ORDER BY 1 DESC");

The problem I'm only getting post id 1 and 2. while there are over 30 posts

I want to get all posts and comments for each post in a single while loop.

How can I do it ?

is to get all columns data from both tables! , do I have to name each column ? — Ahmadz Issa
– Ahmadz Issa, Commented May 24, 2016 at 3:59
there are over 30 posts, and how many comments? For your case, IMO you should use left outer join instead of inner join — Pham X. Bach
– Pham X. Bach, Commented May 24, 2016 at 4:04

Ohgodwhy · Accepted Answer · 2016-05-24 04:02:30Z

2

Change your join to a LEFT join instead of an INNER join.

The difference being that a LEFT join will use null when there's no comments, where as INNER will only give you the rows that have comments.

answered May 24, 2016 at 4:02

Ohgodwhy

50.9k11 gold badges86 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jon Skinner · Accepted Answer · 2016-05-24 04:06:12Z

1

Your INNER join is taking the union of both tables on the post_id field. You need a LEFT join instead of an INNER join. The LEFT join will give you all the results from the first table.

answered May 24, 2016 at 4:06

Jon Skinner

337 bronze badges

1 Comment

Jon Skinner Over a year ago

Actually, when I started my post, there were no posts. I don't really think I need to copy an extremely elementary SQL concept. Don't get so defensive.

Collectives™ on Stack Overflow

Getting data from multiple tables into single row while

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related