chrisrulez001 Posted July 17, 2018 Share Posted July 17, 2018 Hi, I've posted this here as I think it's more of a MySQL issue/question than PHP. What I'd like to happen is that if this is possible using one query, I'd like to select all the posts from the posts table, and all the relevant comments from the comments table, each post to many comments. Here's what I've tried so far, I've tried an inner join but that only selects the posts with comments only. The closest I've got is with a left join but it doesn't display correctly, it repeats the post (the one with comments) and then shows the rest of the posts. I'm doing a foreach in PHP, here is a print_r from PHP: Array ( [0] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test1 [comment_date_posted] => [posted_by] => ) [1] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test2 [comment_date_posted] => [posted_by] => ) [2] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test3 [comment_date_posted] => [posted_by] => ) [3] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test4 [comment_date_posted] => [posted_by] => ) [4] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test5 [comment_date_posted] => [posted_by] => ) [5] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test6 [comment_date_posted] => [posted_by] => ) [6] => Array ( [ID] => 13 [subject] => test [content] => test [post_date_posted] => 2018-07-08 21:20:24 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [7] => Array ( [ID] => 12 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:42:57 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [8] => Array ( [ID] => 11 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:30:34 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [9] => Array ( [ID] => 10 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:27:22 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) ) I'm not entirely sure how to proceed but I've attached my database schema below: MariaDB [blog]> show TABLES; +----------------+ | Tables_in_blog | +----------------+ | comments | | posts | +----------------+ MariaDB [blog]> DESCRIBE comments; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | post_id | int(11) | NO | MUL | NULL | | | comment | text | NO | | NULL | | | date_posted | varchar(25) | NO | | NULL | | | posted_by | varchar(150) | NO | | NULL | | | ip | varchar(100) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ MariaDB [blog]> DESCRIBE posts; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | subject | varchar(150) | NO | | NULL | | | content | text | NO | | NULL | | | date_posted | varchar(25) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ MariaDB [blog]> SELECT * FROM posts; +----+-------------------+---------------------+---------------------+ | ID | subject | content | date_posted | +----+-------------------+---------------------+---------------------+ | 1 | test | test | 2018-07-08 18:21:12 | | 2 | test | test | 2018-07-08 18:46:15 | | 3 | test | test | 2018-07-08 18:47:08 | | 4 | test | test | 2018-07-08 18:47:23 | | 5 | test | test | 2018-07-08 18:50:38 | | 6 | test | test | 2018-07-08 18:51:15 | | 7 | test | test | 2018-07-08 18:52:05 | | 8 | test | test | 2018-07-08 19:24:14 | | 9 | test | test | 2018-07-08 19:25:49 | | 10 | test | test | 2018-07-08 19:27:22 | | 11 | test | test | 2018-07-08 19:30:34 | | 12 | test | test | 2018-07-08 19:42:57 | | 13 | test | test | 2018-07-08 21:20:24 | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | +----+-------------------+---------------------+---------------------+ MariaDB [blog]> SELECT * FROM comments; +----+---------+---------+-------------+-----------+----+ | ID | post_id | comment | date_posted | posted_by | ip | +----+---------+---------+-------------+-----------+----+ | 1 | 14 | test1 | | | | | 2 | 14 | test2 | | | | | 3 | 14 | test3 | | | | | 4 | 14 | test4 | | | | | 5 | 14 | test5 | | | | | 6 | 14 | test6 | | | | +----+---------+---------+-------------+-----------+----+ MariaDB [blog]> SELECT posts.ID, posts.subject, posts.content, posts.date_posted, -> comments.post_id, comments.comment AS comments, comments.date_posted, comments.posted_by -> FROM posts -> INNER JOIN comments ON posts.ID = comments.post_id; +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | ID | subject | content | date_posted | post_id | comments | date_posted | posted_by | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test1 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test2 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test3 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test4 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test5 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test6 | | | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ MariaDB [blog]> SELECT posts.ID, posts.subject, posts.content, posts.date_posted, -> comments.post_id, comments.comment AS comments, comments.date_posted, comments.posted_by -> FROM posts -> LEFT JOIN comments ON posts.ID = comments.post_id; +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | ID | subject | content | date_posted | post_id | comments | date_posted | posted_by | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test1 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test2 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test3 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test4 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test5 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test6 | | | | 1 | test | test | 2018-07-08 18:21:12 | NULL | NULL | NULL | NULL | | 2 | test | test | 2018-07-08 18:46:15 | NULL | NULL | NULL | NULL | | 3 | test | test | 2018-07-08 18:47:08 | NULL | NULL | NULL | NULL | | 4 | test | test | 2018-07-08 18:47:23 | NULL | NULL | NULL | NULL | | 5 | test | test | 2018-07-08 18:50:38 | NULL | NULL | NULL | NULL | | 6 | test | test | 2018-07-08 18:51:15 | NULL | NULL | NULL | NULL | | 7 | test | test | 2018-07-08 18:52:05 | NULL | NULL | NULL | NULL | | 8 | test | test | 2018-07-08 19:24:14 | NULL | NULL | NULL | NULL | | 9 | test | test | 2018-07-08 19:25:49 | NULL | NULL | NULL | NULL | | 10 | test | test | 2018-07-08 19:27:22 | NULL | NULL | NULL | NULL | | 11 | test | test | 2018-07-08 19:30:34 | NULL | NULL | NULL | NULL | | 12 | test | test | 2018-07-08 19:42:57 | NULL | NULL | NULL | NULL | | 13 | test | test | 2018-07-08 21:20:24 | NULL | NULL | NULL | NULL | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ Link to comment Share on other sites More sharing options...
Barand Posted July 17, 2018 Share Posted July 17, 2018 That's the way JOINS work. You get the data from the post table joined to the data from the matching comments table. If you only want posts that have comments, use INNER JOIN. If you want all posts, and data from comments where they exist, use LEFT JOIN. How you display those results is controlled by your processing of the query results. Link to comment Share on other sites More sharing options...
chrisrulez001 Posted July 17, 2018 Author Share Posted July 17, 2018 Ok thanks for your reply, I'll re-jig the processing. ? Link to comment Share on other sites More sharing options...
Barand Posted July 17, 2018 Share Posted July 17, 2018 Example pseudocode currentPost = 0 while fetch next record if postID != currentPost output post data set currentPost = postID endif output comment data endwhile Link to comment Share on other sites More sharing options...
chrisrulez001 Posted July 17, 2018 Author Share Posted July 17, 2018 Thank you for your help ? Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.