doddsey_65 Posted January 18, 2011 Share Posted January 18, 2011 im trying to join my likes table with my post table to display who likes the relevant post. Unfortunatly it is displaying the post several times for how ever many likes there is because of the way i have joined it: $post_query = mysqli_query($link, "SELECT p.*, u.user_id, u.user_username, u.user_avatar, u.user_sig, l.post_id, l.like_username FROM ".TBL_PREFIX."posts as p LEFT JOIN ".TBL_PREFIX."members as u ON p.post_poster = u.user_username INNER JOIN ".TBL_PREFIX."post_likes as l ON p.post_id = l.post_id WHERE p.topic_id = '$topic_id' ORDER BY post_time ASC ") or die(mysqli_error($link)); $likes = array(); while ($post_info = mysqli_fetch_array($post_query, MYSQLI_ASSOC)) { $likes[] .= $post_info['like_username']; what would be the best way to perform this join? like table is as follows: like_id post_id like_username 1 340 doddsey65 Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/ Share on other sites More sharing options...
Muddy_Funster Posted January 19, 2011 Share Posted January 19, 2011 I'm not sure I get exactly what it is that you want to do here. You are reffering to the same TABLE_PREFIX rather than giving an actual table name. could you post the table structure for each of the three tables you are using, with the fields you are looking for highlighted? Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1161656 Share on other sites More sharing options...
doddsey_65 Posted January 19, 2011 Author Share Posted January 19, 2011 forum_posts: post_id post_subject forum_post_likes: like_id post_id like_username basically show all like_usernames under the post they refer to. Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1161789 Share on other sites More sharing options...
jdavidbakr Posted January 19, 2011 Share Posted January 19, 2011 I think you're wanting to use "group by" select post_id, post_subject, group_concat(like_username separator ', ') likes from forum_posts left join forum_post_likes using (post_id) group by forum_posts.post_id should return something like: post_id post_subject likes 1 'likable post' 'joe, sam, fred' 2 'stupid post' NULL Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162070 Share on other sites More sharing options...
doddsey_65 Posted January 20, 2011 Author Share Posted January 20, 2011 thanks it works fine. One last thing though. I want to display the first 4 usernames and then show the rest as a numerical representation. eg: user1, user2, user3, user4 and 5 others like this any ideas? Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162369 Share on other sites More sharing options...
DarkKnight2011 Posted January 20, 2011 Share Posted January 20, 2011 Hi doddsey_65, you would deal with that side of things using php, not the query and there are a number of ways to do it, one possible way would be to load all the usernames into an array from the database for that post and then you could do something like... // example array $likesArray = ('jon', 'bob', 'fred', 'frank', 'james'); // Count the number of others (total minus the number your displaying by name $others = count($likesArray) - 2; // build the string $likeString = ucwords($likesArray[0]) . ", " . ucwords($likesArray[1]) . " and $others like this"; which should in theory give you - Jon, Bob and 3 others like this note: ucwords capitalises the first letter of the word Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162398 Share on other sites More sharing options...
doddsey_65 Posted January 20, 2011 Author Share Posted January 20, 2011 i used the table join as above and added the result in an array. however when there are no likes for a post it displays that there is 1 like for it. a count of the array shows that 1 person likes the post when there is no one in the table that likes it: $like_list = ""; $likes = explode(", ", $post_info['likes']); $amount_likes = count($likes); $slice = array_slice($likes, 0, 3, true); $remain = array_slice($likes, 4, $amount_likes, true); $remain_num = count($remain); if ($amount_likes > 4) { for($i=0; $i<=3; $i++) { $like_list .= $likes[$i].", "; } $like_list .= " and $remain_num others like this - $amount_likes"; } elseif ($amount_likes < 1) { $like_list .= "0 Users Like This - $amount_likes"; } elseif ($amount_likes <= 3) { $like_list .= $post_info['likes']." likes this - $amount_likes"; } Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162432 Share on other sites More sharing options...
DarkKnight2011 Posted January 20, 2011 Share Posted January 20, 2011 Oh yeah, well spotted Quote Link to comment https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162439 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.