Jump to content
chrisrulez001

Struggling to select posts & comments

Recommended Posts

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      |
+----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Example pseudocode

currentPost = 0
while fetch next record
    if postID != currentPost
        output post data
        set currentPost = postID
    endif
    output comment data
endwhile

 

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.