I have the following query where I am trying to join my profile_img
and users
table to match the id's in the friends
table (friend_one or friend_two) in oder to get their profile image or user information.
As of now, I do not get any errors...just not the correct results I am looking for. There should be two results that show relation to :profile_user
... 5 and 2, which would also give ocean and lake for their profile_img.
The parameter :profile_user
is equal to 1. :total_status
= 2.
I am not sure if my ON
clauses are throwing this off or not. I am not sure how to make u.id =
to both the friend_one
or friend_two
.
Does anyone see why this isn't working?
SELECT f.*, u.*, p.*, IFNULL(p.img, 'profile_images/default.jpg') AS img
FROM friends f
JOIN
users u
ON u.id = (f.friend_one or f.friend_two)
LEFT JOIN
profile_img p
ON p.user_id = f.friend_one or f.friend_two and p.id = (select max(p2.id) from profile_img p2 where p2.user_id = p.user_id)
WHERE (friend_one = :profile_user or friend_two = :profile_user)
AND status = :total_status
Full code, which is showing 0 results.
$friend_status = 2;
$friend_sql = "
SELECT f.*, u.*, p.*, IFNULL(p.img, 'profile_images/default.jpg') AS img
FROM friends f
JOIN
users u
ON u.id = (f.friend_one or f.friend_two)
LEFT JOIN
profile_img p
ON p.user_id = f.friend_one or f.friend_two and p.id = (select max(p2.id) from profile_img p2 where p2.user_id = p.user_id)
WHERE (friend_one = :profile_user or friend_two = :profile_user)
AND status = :total_status
";
$friend_stmt = $con->prepare($friend_sql);
$friend_stmt->execute(array(':profile_user' => $profile_user, ':total_status' => $friend_status));
$friend_total_rows = $friend_stmt->fetchAll(PDO::FETCH_ASSOC);
$count_total_friend = $friend_stmt->rowCount();
?>
<div id="friend-list-container">
<div id="friend-list-count">Friends <span class="light-gray"><?php echo $count_total_friend; ?></span></div>
<div id="friend-list-image-container">
<?php
foreach ($friend_total_rows as $friend_total_row) {
$friend_1 = $friend_total_row['friend_one'];
$friend_2 = $friend_total_row['friend_two'];
$friend_img = $friend_total_row['img'];
$friend_username = $friend_total_row['username'];
if($friend_1 !== $profile_user) {
echo $friend_1;
echo $friend_img;
echo $friend_username;
}
if($friend_2 !== $profile_user) {
echo $friend_2;
echo $friend_img;
echo $friend_username;
}
}