CODEWITHSUNDEEP
phpmysqlsqldatabase
Ahmadz Issa
Ahmadz Issa

Reputation: 765

Getting data from multiple tables into single row while

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 ?

Upvotes: 0

Views: 73

Answers (2)

Jon Skinner
Jon Skinner

Reputation: 33

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.

Upvotes: 1

Ohgodwhy
Ohgodwhy

Reputation: 50787

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.

Upvotes: 2

Related Questions