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 ?
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.
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.