Query output not consistent to if condition

Question

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

I'm not sure what are you asking exactly? On my page (friend_1) display all data from my friends (friend_2, 3 etc) ? — boroboris
– boroboris, Commented Nov 29, 2016 at 19:08
If I am on a user's page with an id of 1 (see my example above).. the only output should be from user 5. My output on my page is showing user 5 as $friend_one, but the username and img is from user 1. — Paul
– Paul, Commented Nov 29, 2016 at 19:10

BA_Webimax · Accepted Answer · 2016-11-29 20:47:05Z

1

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

SELECT u.*, p.img
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['id'];
        $friend_img_src     = $friend_total_row['img'];
        $friend_img         = (!empty($friend_img_src)) ? '<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>';

    }

edited Nov 29, 2016 at 20:47

answered Nov 29, 2016 at 19:12

BA_Webimax

2,6871 gold badge16 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Paul Over a year ago

My query is fine, the output is wrong... I think the problem stems from my if condition.

BA_Webimax Over a year ago

No, your query tries to grab too much information because of the way it is structured. Go back to your Fiddle and look at the duplicate columns being returned. PHP is going to pick the last one it sees with the "img" label on it. All the U1.*, U2.*, P1.*, P2.* grabs duplicates of the fields. Simplify the query to retrieve only what you need. Also, reducing all those JOINs will speed up the query.

Paul Over a year ago

Hmm. Well your query helps in a certain way, but hurts it too. It now gets the right user info, but I needed the max id in it to get only the latest profile_img.

BA_Webimax Over a year ago

@Paul I updated the query above. That should match the image with the highest ID.

BA_Webimax Over a year ago

I made changes to the query and the PHP in the answer.

|

Collectives™ on Stack Overflow

Query output not consistent to if condition

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related