Paul Paul - 9 days ago 5
MySQL Question

Query output not consistent to if condition

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

}


Fiddle

Answer

If I understand your request correctly, I think you are looking for:

SELECT u.*, p.*
FROM users u
LEFT JOIN (
        SELECT user_id, max(id) as mid
        FROM profile_img
        GROUP BY user_id
    ) mid ON u.id = mid.user_id
LEFT JOIN profile_img p ON p.id = mid.mid
WHERE u.id in (
    SELECT friend_one FROM friends WHERE friend_two = 1 AND status = 2
    UNION
    SELECT friend_two FROM friends WHERE friend_one = 1 AND status = 2 
    );

OK, so here is an update for the PHP portion...

<?php
    foreach ($friend_total_rows as $friend_total_row) {
        $friend_id          = $friend_total_row['user_id'];
        $friend_img_src     = $friend_total_row['img'];
        $friend_img         = '<img src="'.$friend_img_src.'" alt="Friend Image">';
        $friend_username    = $friend_total_row['username'];
            echo '<div class="friend-list-block">
                    <div class="friend-list-block-img">' .
                    $friend_img .
                        '<div class="friend-list-block-details">'. $friend_id . " "
                    . $friend_username .
                '</div></div></div>';

    }