slj90 Posted January 7, 2015 Share Posted January 7, 2015 (edited) I am using the following sql code to display a stream of statuses, I have the username/profile picture/status/likes working but I am now stuck on comments. SELECT s.*, u.*, c.*, COUNT(l.likes_location_id) AS likeCount FROM stream AS s LEFT JOIN users as u ON (u.users_username = s.stream_username) LEFT JOIN comments AS c ON ( c.comments_location_id = s.stream_id ) LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id ) GROUP BY s.stream_id ORDER BY s.stream_id DESC LIMIT 50 If a status has multiple comments the results only shows the first comment that was made. How can I get it to include all of them? Also, how would I include this in the PHP loop, without having another loop?Thanks, Edited January 7, 2015 by slj90 Quote Link to comment Share on other sites More sharing options...
requinix Posted January 7, 2015 Share Posted January 7, 2015 Include how? How do you expect it to combine multiple rows of data into one single string? Quote Link to comment Share on other sites More sharing options...
slj90 Posted January 7, 2015 Author Share Posted January 7, 2015 Include how? How do you expect it to combine multiple rows of data into one single string? I don't know. I guess it's not possible? How is this usually done? Thanks Quote Link to comment Share on other sites More sharing options...
slj90 Posted January 7, 2015 Author Share Posted January 7, 2015 (edited) Include how? How do you expect it to combine multiple rows of data into one single string? I was going to use an if statement to display them. Edited January 7, 2015 by slj90 Quote Link to comment Share on other sites More sharing options...
kicken Posted January 7, 2015 Share Posted January 7, 2015 For a strictly mysql answer, you could use GROUP_CONCAT to join all the comment texts together into a single string. You probably don't want to display them all as a single string and likely want other details such as who made the comment so that's not a great solution. A better solution is to use a separate query to fetch the comment details and then reference them as you display the statuses. You'd have one query that pulls the status information and like count, then a second query that pulls the comments for each status. As you read the status information you'd store it into an array indexed by the status ID. Then as you read the comments you can locate the status by the ID and append the comments to that array. After you've put together the comments and status information you'd loop the statuses and display them. $sql = ' SELECT s.stream_id , u.users_username , COUNT(l.likes_location_id) AS likeCount FROM stream AS s LEFT JOIN users as u ON (u.users_username = s.stream_username) LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id ) GROUP BY s.stream_id ORDER BY s.stream_id DESC LIMIT 50 '; $result = $db->query($sql); $statuses = array(); foreach ($result as $row){ $statuses[$row['stream_id']] = $row + array('comments' => array()); } $idList = implode(',', array_keys($statuses)); $sql = ' SELECT c.comments_location_id , c.comment FROM comments c ORDER BY c.comments_location_id DESC WHERE c.comments_location_id IN ('.$idList.') '; $result = $db->query($sql); foreach ($result as $row){ $id = $row['comments_location_id']; if (isset($statuses[$id])){ $statuses[$id]['comments'][] = $row; } } foreach ($statuses as $status){ //display status. } Side note: Don't use * in your select. Specify which fields you need (even if it's all of them). Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.