Getting multiple select instead of single (on JOIN table)

[eevan79]

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.

[mikosiko]

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

[eevan79]

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)

[mikosiko]

the select that I gave to you is correct.... show us the rest of the code and example of the tables data

[eevan79]

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