Gathering user info from another table

Question

I currently have a query set to select comments which are associated to a particular post. I also need to select the users details who submitted that comment against that post. I am struggling a little here and need some advice.

Here is my current query:

    $q = "SELECT p.post_id, f.created_date, f.comment_id, f.comment_content, j.id 
             FROM forum_post AS p
             INNER JOIN comment_post_join AS j ON p.post_id = j.post_id
             INNER JOIN forum_comment AS f ON f.comment_id = j.comment_id
             WHERE p.post_id = '$id'
             ORDER BY created_date ASC
         ";   

    $r = mysqli_query ($dbc, $q); // Run the query.


    // FETCH AND PRINT ALL THE RECORDS
    while ($row = mysqli_fetch_array($r)) {

    echo '
    <div style="border-bottom: thin solid #ddd;">
        <p style="font-size: 13px;">'.$row["comment_content"] . '</p><p style="font-size: 12px;">' .date("F j, Y, g:i a", strtotime($row["created_date"])). '</p>
    </div>
    ';

}

As you can see I am taking from the forum_post table as this is where my posts live. I am using an inner_join to reference the post and the comments associated to it, but I also need to get details out from the users as I also have another table user_comment_join which upon submission of a comment also updates the table which creates a row and submits the users ID and the comment ID.

I do a similar thing with the actual post and reference the user by doing this:

    $q = "SELECT u.user_id, u.first_name, u.last_name, f.post_created, f.post_id, f.post_title, f.post_content, j.id 
             FROM users AS u
             INNER JOIN user_post_join AS j ON u.user_id = j.user_id
             INNER JOIN forum_post AS f ON f.post_id = j.post_id
             WHERE f.post_id = '$id'
         ";

I hope I have explained my problem here and any advise would be great.

I have attempted this myself like so...:

    $q = "SELECT p.post_id, f.created_date, f.comment_id, f.comment_content, u.user_id, u.first_name, u.last_name, j.id 
             FROM forum_post AS p
             INNER JOIN comment_post_join AS j ON p.post_id = j.post_id
             INNER JOIN forum_comment AS f ON f.comment_id = j.comment_id
             INNER JOIN user_comment_join AS cj ON u.user_id = cj.user_id
             WHERE p.post_id = '$id'
             ORDER BY created_date ASC
         ";

But I get the following error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\MAMP\htdocs\functions\functions.inc.php on line 963

Which in turn refers to this line...:

while ($row = mysqli_fetch_array($r)) {

If I run this query in phpmyadmin I get the following:

Unknown column 'u.user_id' in 'field list'

I'm not the downvoter, but the error is actually telling you the column doesn't exist, thus the query failing, thus `$r` being `FALSE`. Which you would have solved with a simple search: http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-or-mysqli-result-boole — ʰᵈˑ, Aug 04 '15 at 10:24
Hey, so I understand that $r is failing at this point because there is clearly something up with my query. I need some help with the query more than anything... — PhpDude, Aug 04 '15 at 10:27
The column `user_id` does not exist in table `u` (it's a table as you've not aliased anything `u`). What table holds the column `user_id`? You don't need `AS` when aliasing tables. — ʰᵈˑ, Aug 04 '15 at 10:28
both my `users` table and my `user_comment_join` table hold that column — PhpDude, Aug 04 '15 at 10:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85096/discussion-between-phpcoder-and-). — PhpDude, Aug 04 '15 at 10:32
Hey, so see my update in my question I get the another problem now... — PhpDude, Aug 04 '15 at 10:33

score 1 · Accepted Answer · answered Aug 04 '15 at 13:08

You forgot about joining users table. Try this:

$q = "SELECT p.post_id, f.created_date, f.comment_id, f.comment_content, u.user_id, u.first_name, u.last_name, j.id 
         FROM forum_post AS p
         INNER JOIN comment_post_join AS j ON p.post_id = j.post_id
         INNER JOIN users AS u ON u.user_id = j.user_id
         INNER JOIN forum_comment AS f ON f.comment_id = j.comment_id
         INNER JOIN user_comment_join AS cj ON u.user_id = cj.user_id
         WHERE p.post_id = '$id'
         ORDER BY created_date ASC
     ";

Gathering user info from another table

1 Answers1