Jump to content

Getting multiple select instead of single (on JOIN table)


Recommended Posts

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:

activities.gif

 

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.

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
                          ";

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)

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).  :shrug:

 

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 :)

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.