I am running into an issue I cannot figure out. I am doing a SELECT query to display users' friends. I am joining a users, friends, and profile_img table to get the user information, their profile image and the friend relationship.
In the friends table, if friend_one
and friend_two
have a status
of 2, that means they are friends. This is what my query is checking for and then joining together the other tables to retrieve the other data, such as username and the image.
The query seems to be working fine. My output on the other hand is not what it should be though.
In my friends table, if you are looking at row 2 (see INSERT below), friend_one is 5 and friend_two is 1. My query gets the data for both of these users, however, when I attempt to output the friend list, I am only wanting to display the friend list associated with the user profile I am viewing. So if I am on friend 1's profile page, the data I want to show is only for friend 5.
CREATE TABLE `friends` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`friend_one` int(11) NOT NULL DEFAULT '0',
`friend_two` int(11) NOT NULL DEFAULT '0',
`status` enum('0','1','2') COLLATE utf8_unicode_ci DEFAULT '0',
`date` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `friend_two` (`friend_two`)
);
INSERT INTO `friends`
VALUES
(1, 1, 2, 2, NOW()),
(2, 5, 1, 2, NOW())
;
I attempted an if statement to continue past the friend data in which matches the profile user's page I was on and it works for the friend_1
output...which would show 1, but it doesn't do it for the friend_img
and friend_username
.
Does anyone see why it is working for $friend_1
, but not the rest of their data? The image and username are outputting the profile user's data, which it should be the opposite and just their friends data.
$friend_status = 2;
$friend_sql = "
SELECT f.*,
u1.*,
u2.*,
p1.*,
p2.*,
IFNULL(p1.img, 'profile_images/default.jpg') AS img1,
IFNULL(p2.img, 'profile_images/default.jpg') AS img2
FROM friends f
LEFT JOIN users u1 ON u1.id = f.friend_one
LEFT JOIN users u2 ON u2.id = f.friend_two
LEFT JOIN (
SELECT user_id, max(id) as mid
FROM profile_img
GROUP BY user_id
) max1 ON u1.id = max1.user_id
LEFT JOIN (
SELECT user_id, max(id) as mid
FROM profile_img
GROUP BY user_id
) max2 ON u2.id = max2.user_id
LEFT JOIN profile_img p1 ON p1.user_id = f.friend_one and p1.id = max1.mid
LEFT JOIN profile_img p2 ON p2.user_id = f.friend_two and p2.id = max2.mid
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_src = $friend_total_row['img'];
$friend_img = '<img src="'.$friend_img_src.'" alt="Friend Image">';
$friend_username = $friend_total_row['username'];
if($friend_1 !== $profile_user) {
echo '<div class="friend-list-block">
<div class="friend-list-block-img">' .
$friend_img .
'<div class="friend-list-block-details">'. $friend_1 . " "
. $friend_username .
'</div></div></div>';
}
Aucun commentaire:
Enregistrer un commentaire