eevan79 Posted September 7, 2010 Share Posted September 7, 2010 Hello, I am trying to get data from 2 tables: friend_list and activities. Table friend_list contain list of user friends, and table activities contain activities (obvious ). But when fetch data from mysql, I am getting multiple activities from activities table (as I have friends in friend_list, so I get data for each particular activities.) Here is my code: $myid = mysql_real_escape_string($_SESSION['user_id']); //GET 30 RECENT FRIENDS ACTIVITIES $sql = "SELECT " . $table_prefix . "activities.activities_id, " . $table_prefix . "activities.user_id, " . $table_prefix . "activities.other_id, " . $table_prefix . "activities.activities_date, " . $table_prefix . "activities.activities, " . $table_prefix . "friend_list.id, " . $table_prefix . "friend_list.user_id, " . $table_prefix . "friend_list.friend_id FROM " . $table_prefix . "friend_list LEFT JOIN " . $table_prefix . "activities ON " . $table_prefix . "activities.user_id != $myid WHERE " . $table_prefix . "friend_list.user_id = $myid ORDER BY " . $table_prefix . "activities.activities_date DESC LIMIT 0,30 "; and here is how it looks like: in this case I have 8 friend in friend_list, and get multiple ( 8 ) activities. I also tried a variety of other ways to get data, but without success...always get multiple select from activities table. Quote Link to comment https://forums.phpfreaks.com/topic/212725-getting-multiple-select-instead-of-single-on-join-table/ Share on other sites More sharing options...
mikosiko Posted September 7, 2010 Share Posted September 7, 2010 your JOIN is incorrect... it is not joining table friend_list with activities properly... it should be $sql = "SELECT " . $table_prefix . "activities.activities_id, " . $table_prefix . "activities.user_id, " . $table_prefix . "activities.other_id, " . $table_prefix . "activities.activities_date, " . $table_prefix . "activities.activities, " . $table_prefix . "friend_list.id, " . $table_prefix . "friend_list.user_id, " . $table_prefix . "friend_list.friend_id FROM " . $table_prefix . "friend_list LEFT JOIN " . $table_prefix . "activities ON " . $table_prefix . "friend_list.user_id = " . $table_prefix . "activities.user_id WHERE " . $table_prefix . "friend_list.user_id = $myid ORDER BY " . $table_prefix . "activities.activities_date DESC LIMIT 0,30 "; Quote Link to comment https://forums.phpfreaks.com/topic/212725-getting-multiple-select-instead-of-single-on-join-table/#findComment-1108110 Share on other sites More sharing options...
eevan79 Posted September 7, 2010 Author Share Posted September 7, 2010 Already tried: "... LEFT JOIN " . $table_prefix . "activities ON " . $table_prefix . "friend_list.user_id = " . $table_prefix . "activities.user_id But still getting multiple SELECT (same result)... EDIT: With this code I get my own activities instead of friends from friend_list (and still multiple) Quote Link to comment https://forums.phpfreaks.com/topic/212725-getting-multiple-select-instead-of-single-on-join-table/#findComment-1108113 Share on other sites More sharing options...
mikosiko Posted September 7, 2010 Share Posted September 7, 2010 the select that I gave to you is correct.... show us the rest of the code and example of the tables data Quote Link to comment https://forums.phpfreaks.com/topic/212725-getting-multiple-select-instead-of-single-on-join-table/#findComment-1108115 Share on other sites More sharing options...
eevan79 Posted September 7, 2010 Author Share Posted September 7, 2010 All code (hardcoded for now ): $myid = mysql_real_escape_string($_SESSION['user_id']); //GET 30 RECENT FRIENDS ACTIVITIES $sql = "SELECT " . $table_prefix . "activities.activities_id, " . $table_prefix . "activities.user_id, " . $table_prefix . "activities.other_id, " . $table_prefix . "activities.activities_date, " . $table_prefix . "activities.activities, " . $table_prefix . "friend_list.id, " . $table_prefix . "friend_list.user_id, " . $table_prefix . "friend_list.friend_id FROM " . $table_prefix . "friend_list LEFT JOIN " . $table_prefix . "activities ON " . $table_prefix . "activities.user_id != $myid WHERE " . $table_prefix . "friend_list.user_id = $myid ORDER BY " . $table_prefix . "activities.activities_date DESC LIMIT 0,50 "; $result = $db->query($sql); echo "<table style='margin:8px;width:98%'>"; while ($list = $db->fetch_array($result,'assoc')) { //Placeholders $activities = $list['activities']; $activities = str_replace("[AND]","$l_and ",$activities); $activities = str_replace("[FRIENDS]","$l_are_friends ",$activities); $activities = str_replace("[postED]","$l_friend_reply",$activities); $activities = str_replace("[NEW_TOPIC]","$l_friend_new_topic",$activities); $a_date = date($date_format,strtotime($list['activities_date'])); echo "<tr><td height='24px'>ID:$list[activities_id] $activities , $a_date</td></tr>"; } With 1 friend in friend_list I get all activities (for all users) with this code above. With your code and 1 user in friend_list I get only my own activities. Basicaly, I need to select all user_id's (my id) from friend_list to get all friends, and then get all activities from activities table where is friend.user_id = activities.user_id and not my id ($myid). EDIT: You are right. Your code is correct...but instead of: LEFT JOIN " . $table_prefix . "activities ON " . $table_prefix . "friend_list.user_id = " . $table_prefix . "activities.user_id it needs to be: LEFT JOIN " . $table_prefix . "activities ON " . $table_prefix . "friend_list.friend_id = " . $table_prefix . "activities.user_id Finally its working. Thanks for help Quote Link to comment https://forums.phpfreaks.com/topic/212725-getting-multiple-select-instead-of-single-on-join-table/#findComment-1108121 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.