Query using 4 tables and while loop

[slj90]

I am in the process of making a stream page where users can enter a status, like and comment others statuses, like on facebook.

 

I have 4 tables, stream, users, likes and comments as shown below, with the rows I will be using:

 

stream
stream_status

stream_username

 

users

profile_picture

 

likes

likes_item_id

likes_username

 

comments

comments_item_id

comments_username

 

My first question is how do I write the query to get data from all 4 tables, I know I would use 'JOIN' but not entirely sure on how to do this correctly with several tables? 

 

Secondly, how would I display multiple rows from the likes and comments table in the while loop?

 

Thanks,

[adam_bray]

Your table structure doesn't look that great from what you've posted. I would use something along the lines of -

 

Stream

status_id (auto increment)

status

uid

status_time

 

Users

uid (auto increment)

username

avatar

 

Likes

status_id

uid

like_time

 

Comments

comment_id (auto increment)

status_id

comment

uid

comment_time

 

Then as you mentioned, you need to use JOIN's to get the relevant data.

 

If you're not sure on the type of JOIN to use then think along the lines of "can this value = null, but the query should still work?"

- yes = left outer join

- no = inner join

 

With that said, here's an example query for the table structure I suggested above (untested) -

SELECT stream.status 
, users.username AS author_username
, users.avatar AS author_avatar
, c.comment
, c.comment_time
, uc.username AS comment_username
, uc.avatar AS comment_avatar
, COALESCE(l.likes,0) AS likes
FROM stream
INNER
  JOIN users
  	ON users.uid = stream.uid
LEFT OUTER
  JOIN comments AS c
  	ON c.status_id = stream.status_id
LEFT OUTER
  JOIN users AS uc
  	ON uc.uid = c.uid
LEFT OUTER
  JOIN ( SELECT like_time,   
		COUNT(*) AS likes
		   FROM likes 
		 GROUP
		   BY status_id ) AS l
	ON l.status_id = stream.status_id
ORDER BY status_time DESC
LIMIT 50;

To answer your second question I'd suggest setting up the MySQL, entering some dummy data, then running the query in phpmyadmin to see how it gets returned. Once you understand that, it'll become much easier to work out how to loop through the rows.

[slj90]

Hi,

 

Thanks for the reply.

 

I didn't list all the columns that were in my tables as I didn't want to complicate things, so just listed the ones I wanted to echo out.

 

I have tried to get the code you kindly wrote for me to work using my column names, I had several errors which I fixed but now all I am getting is a blank page.

<?php
include './include/connect.php';
$sql = "SELECT stream.stream_status
, users.user_username AS author_username
, users.profile_picture AS author_avatar
, c.comments_comment
, c.comments_time
, uc.user_username AS comment_username
, uc.profile_picture AS comment_avatar
, COALESCE(l.likes,0) AS likes
FROM stream
INNER
  JOIN users
  	ON users.user_username = stream.stream_username
LEFT OUTER
  JOIN comments AS c
  	ON c.comments_item_id = stream.stream_id
LEFT OUTER
  JOIN users AS uc
  	ON uc.user_username = c.comments_username
LEFT OUTER
  JOIN ( SELECT likes_time,   
		COUNT(*) AS likes
		   FROM likes 
		 GROUP
		   BY stream_id ) AS l
	ON l.likes_item_id = stream.stream_id
ORDER BY stream_time DESC
LIMIT 50;

$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{


echo $row['stream_username'] . $row['stream_status'] . $row['profile_picture'];
}

echo mysql_error();


?>

Thanks again!

[slj90]

So there was a missing ".

 

I now have the error "Unknown column 'stream_id' in 'group statement'" Should it be likes_item_id? 

 

I then get the error 'Unknown column 'l.likes_item_id' in 'on clause''

Edited May 28, 2014 by slj90

[Psycho]

Start by testing your query in your database management utility (e.g. PHPMyAdmin). Once you've verified it is getting the results you expect - THEN use it in your code to create output. Trying to start with a new query and new code to create output only complicates things.

 

However, the code you posted is invalid. There isn't a closing quote mark for the query.

 

Some comments on what I am seeing: Your tables should have an ID column as the primary key. You should not be JOINing tables on data such as username. Also, use better names in your database. "item_id" says nothing about what the id is for. You don't have any table called "items". It makes it hard to read/understand the code with names that are ambiguous.

 

I'm not understanding why slj90 is suggesting the complicated logic around the likes. Should be able to do a JOIN with a GROUP BY. The current query would not work for that subquery because you are trying to JOIN on that subquery using 'likes_item_id', but that value isn't returned in the subquery.

 

Try this query to start with. As written it will only pull data from the streams table. If that works, then remove the comments '-- 1 ---' and run again. It should then display the user information. If that works, then remove the comments with '-- 2 --' and verify the output. Repeat with the comments for 3 and 4

 

       SELECT s.stream_status
-- 1 --        , u.user_username AS author_username, u.profile_picture AS author_avatar
-- 2 --        , c.comments_comment, c.comments_time
-- 3 --        , uc.user_username AS comment_username, uc.profile_picture AS comment_avatar
-- 4 --        , COUNT (stream.stream_id) AS likes
        FROM stream s
-- 1 -- INNER JOIN users u          ON users.user_username = stream.stream_username
-- 2 -- LEFT OUTER JOIN comments c  ON c.comments_item_id = stream.stream_id
-- 3 -- LEFT OUTER JOIN users uc    ON uc.user_username = c.comments_username
-- 4 -- LEFT OUTER JOIN likes l     ON l.likes_item_id = stream.stream_id
        ORDER BY stream_time DESC
-- 4 -- GROUP BY (stream.stream_id)
        LIMIT 50

[slj90]

Hello Psycho,

 

Thanks for your detailed reply, I appreciate it.

I understand where you are coming from with the names I have used for columns and will plan ahead better next time and perhaps change the current ones when I have time.

 

I have played around with the query you kindly wrote for me, I did get some errors but managed to solve them by changing the query slightly.

 

It now succesfully gets all the data I require as shown in the attached picture. 

 

Here is the query I used:

  SELECT s.stream_status, u.user_username AS author_username, u.profile_picture AS author_avatar, c.comments_comment, c.comments_time, uc.user_username AS comment_username, uc.profile_picture AS comment_avatar, likes_username
-- 4 --        , COUNT (stream.stream_id) AS likes
        FROM stream s
INNER JOIN users u          ON user_username = stream_username
LEFT OUTER JOIN comments c  ON c.comments_item_id = stream_id
LEFT OUTER JOIN users uc    ON uc.user_username = c.comments_username
LEFT OUTER JOIN likes l     ON l.likes_item_id = stream_id
        ORDER BY stream_time DESC
-- 4 -- GROUP BY (stream.stream_id)
        LIMIT 50

I will talk about comments 4 in a little while.

 

As you can see in the picture it displays a row for each comment and each like. So, "Example Status" is shown 4 times as it has 2 comments and 2 likes. Will I be able to manipulate the results shown with PHP to only display the status once with the matching comments and likes, or will the comments in the query I left out resolve this? Please could you explain what those 2 lines do.

 

I couldn't get either of them to work, firstly:

, COUNT (stream.stream_id) AS likes

Gave the error:
 

 #1630 - FUNCTION mydatabase.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

And:

GROUP BY (stream.stream_id)

Gave the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY (stream.stream_id) LIMIT 50' at line 9

Thanks again for your help.

Edited May 30, 2014 by slj90

[Psycho]

. . . will the comments in the query I left out resolve this? Please could you explain what those 2 lines do.

 

The parts under #4 do two things, but first let me explain something else first. You didn't supply a field for the stream table for the primary ID. I assume you have one, if not you need to add one. So, I put stream_id as a placeholder. You should typically have a primary ID on all your tables as it is needed for some operations. In this case it is needed to GROUP the records to prevent duplicates.

 

But, I see you added likes_username to the query (again this is the absolute wrong way to do this, you should have the ID for the user record). Are you needing data for all of the likes associated with the stream or are you only needing a count of the likes? If you need the former, it becomes more complicated. If you need the latter, the other two lines in the query are supposed to provide that, but I think I had an error. But, as stated above, I assume your tables have a unique primary key. If not, this almost becomes impossible. If your comments table has a unique key, then this should work to add the COUNT() of likes to the returned data without the duplication of comments (replace the with correct comment_id field name).

 

SELECT s.stream_status,
       u.user_username AS author_username, u.profile_picture AS author_avatar,
       c.comments_comment, c.comments_time, uc.user_username AS comment_username,
       uc.profile_picture AS comment_avatar,
       likes_username
-- 4 --        , COUNT (c.comment_id) AS likes
FROM stream s
INNER JOIN users u          ON u.user_username    = s.stream_username
LEFT OUTER JOIN comments c  ON c.comments_item_id = s.stream_id
LEFT OUTER JOIN users uc    ON uc.user_username   = c.comments_username
LEFT OUTER JOIN likes l     ON l.likes_item_id    = s.stream_id
ORDER BY stream_time DESC
-- 4 -- GROUP BY (c.comment_id)
LIMIT 50

It may seem counter-intuitive to use the comment_id to determine the number of likes. But, as you see from the current results, JOINing the likes onto the rest of the query creates a duplication of the comments to likes. Note: if you had more many-to-one JOIN conditions, I'd probably result to a subquery as adam_bray was suggesting above.

 

If you do need data about the individual records for the likes, then you will have to get the data with the duplication in the result set and you will have to handle it in the processing code.