Join 4 tables (gather status and image updates)

[jacko310592]

hey guys,

 

my following code allows we me to get a friend id in a bi-directional table (by finding the user_id of the logged in user ($loggedInUserId)) and retrieve user details (such as username) for that user:

 

(SELECT SQL_CALC_FOUND_ROWS *
FROM user_details AS ud
CROSS JOIN user_friends AS uf
ON ud.user_id = uf.user_id_link1
WHERE uf.user_id_link2 = '$loggedInUserId')

UNION ALL
(SELECT *
FROM user_details AS ud
CROSS JOIN user_friends AS uf
ON ud.user_id = uf.user_id_link2
WHERE uf.user_id_link1 = '$loggedInUserId')

LIMIT 0,10

 

.. but now i need my code to gather the friend ids (from user_friends), get the friends username (from user_details), for each friend i then need to gather any status updates and image uploads relating to that user (from user_statuses and user_images)

 

so i need to find a way of combining the following kind of codes with the above:

 

SELECT post_body, date_posted
FROM user_statuses
WHERE user_id = '[FRIEND_ID]'

 

SELECT name, date_uploaded
FROM user_images
WHERE user_id = '[FRIEND_ID]'

..while sorting the whole query using the dates from "date_posted" and "date_uploaded"

 

 

general layout for tables:

user_details:

user_id   |   username
------------------------------
001       |   bob
002       |   fred
003       |   bill



user_friends:

user_id_link1   |   user_id_link2
-----------------------------------------
001             |   003                                                  // bob is a friend of bill, vice-versa



user_statuses:

user_id   |   post_body   |   date_posted
----------------------------------------------------
001       |   testing     | [TIMESTAMP]
001       |   1234        | [TIMESTAMP]
002       |   blahblah    | [TIMESTAMP]



user_images:

user_id   |   name           |   date_uploaded
----------------------------------------------------
002       |   blah.jpg       | [TIMESTAMP]
002       |   test.png       | [TIMESTAMP]
001       |   img.jpe        | [TIMESTAMP]

so if the logged in user was bill, the query would return the first 2 rows from user_statuses, and the 3rd row from user_images, as well as any data from the user_details table

 

can anyone suggest how my problem can be solved?

 

sorry if i haven't explained well, please ask if you need anymore info

thanks for any help (:

[kickstart]

Hi

 

Something like this I think if I have understood what you need

 

(SELECT SQL_CALC_FOUND_ROWS *
FROM user_details ud
CROSS JOIN user_friends uf ON ud.user_id = uf.user_id_link1
INNER JOIN user_statuses us ON uf.user_id_link1 = us.user_id
INNER JOIN user_images ui ON uf.user_id_link1 = ui.user_id
WHERE uf.user_id_link2 = '$loggedInUserId')
UNION ALL
(SELECT *
FROM user_details AS ud
CROSS JOIN user_friends AS uf ON ud.user_id = uf.user_id_link2
INNER JOIN user_statuses us ON uf.user_id_link2 = us.user_id
INNER JOIN user_images ui ON uf.user_id_link2 = ui.user_id
WHERE uf.user_id_link1 = '$loggedInUserId')
LIMIT 0,10

 

All the best

 

Keith

[jacko310592]

hi Keith,

thanks for the help, but at the moment the code doesnt seem to be working as it returns no data, but without throwing errors,

 

is this what the code is set out to do:

find all friend's ids  => get info for each friend from user_details  =>  gather all status updates posted by each friend

                                                                                                                  =>  gather all image uploads uploaded by each friend?

 

thank you

[kickstart]

Hi

 

It is getting a row for each combination of user, all their friends, all their statuses and all their images.

 

If they have no friends, no statuses or no images then no row will be returned for them (change the inner joins to left outer joins to get rows even when there is no match on status or image). If they have multiple statuses or images then things will probably get confusing (probably need to group some of the records together)

 

Not quite sure on the details you need to give you more help.

 

All the best

 

Keith

[jacko310592]

thanks Keith, i'll keep tweaking the code i have then, and look into grouping etc

 

thanks again

[jacko310592]

in the end i went with:

 

using two queries, one to get friend's status messages, and one to get friend's image uploads:

$friendStatuses = mysql_query("
(SELECT ud.user_id, ud.username, us.post_body, us.date_posted
FROM user_friends AS uf
CROSS JOIN user_details AS ud ON ud.user_id = uf.user_id_link2
INNER JOIN user_statuses AS us ON us.user_id = uf.user_id_link2				
WHERE uf.user_id_link1 = '$loggedInUserId')
UNION ALL
(SELECT ud.user_id, ud.username, us.post_body, us.date_posted
FROM user_friends AS uf
CROSS JOIN user_details AS ud ON ud.user_id = uf.user_id_link1
INNER JOIN user_statuses AS us ON us.user_id = uf.user_id_link1
WHERE uf.user_id_link2 = '$loggedInUserId')
ORDER BY date_posted DESC
LIMIT 0,10
") or die(mysql_error());


$friendImages = mysql_query("
(SELECT ud.user_id, ud.username, ui.name, ui.date_uploaded
FROM user_friends AS uf
CROSS JOIN user_details AS ud ON ud.user_id = uf.user_id_link2
INNER JOIN user_images AS ui ON ui.user_id = uf.user_id_link2				
WHERE uf.user_id_link1 = '$loggedInUserId')
UNION ALL
(SELECT ud.user_id, ud.username, ui.name, ui.date_uploaded
FROM user_friends AS uf
CROSS JOIN user_details AS ud ON ud.user_id = uf.user_id_link1
INNER JOIN user_images AS ui ON ui.user_id = uf.user_id_link1
WHERE uf.user_id_link2 = '$loggedInUserId')
ORDER BY date_uploaded DESC
LIMIT 0,10
") or die(mysql_error());

mysql_close();

 

then combined the results from both queries into one array:

$arr = array();

while($userInfoParts = mysql_fetch_array($friendStatuses)){
$arr[] = array(
	"user_id" => $userInfoParts['user_id'],
	"username" => $userInfoParts['username'],
	"value" => $userInfoParts['post_body'],
	"date" => $userInfoParts['date_posted']
);
}

while($userInfoParts = mysql_fetch_array($friendImages)){
$arr[] = array(
	"user_id" => $userInfoParts['user_id'],
	"username" => $userInfoParts['username'],
	"value" => $userInfoParts['name'],
	"date" => $userInfoParts['date_uploaded']
);
}

 

then set a function to sort the array by "date", and crop the array after the first 10 sub-arrays:

function orderBy($array, $field, $order=NULL) {
$code = "return strnatcmp(\$a['$field'], \$b['$field']);";
if ($order = "DESC") usort($array, create_function('$b,$a', $code));
else usort($array, create_function('$a,$b', $code));
return $array;
}

$arr = orderBy($arr, 'date', 'DESC');
array_splice($arr, 10);