mardi 29 novembre 2016

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

Aucun commentaire:

Enregistrer un commentaire