[join queries and get counts]

Hi! I'm trying to get working a photo list (library) that I made for my website. I need to query db for somethings and I have to join them all but I'm not very familiarize with this tecquine, neither COUNT/SUM.

 

First I need to get (all) the images from user_uploads table (imgID, user_id, filename, description, up_time, etc), second get the user info from members table (id, name and usr_img) where id = user_uploads.user_id, third and last make 2 counts to know if the logged in user already liked the img ($_SERVER['user_id']) and get the total likes the current "user_uploads.img_id" have.

 

I'm already tried many queries, joining, subquery and finally I'm going to try again joining (or inner join). This is what I have:

SELECT user_uploads.* AS uu, members.*, COUNT(img_id, user_id) AS usr_liked, COUNT(img_id) AS total_likes
FROM user_uploads
INNER JOIN members AS m ON m.id = uu.user_id -- get img owner info
INNER JOIN img_likes AS il ON il.img_id = uu.imgID AND il.user_id = ? -- not merge members.id (img owner) with members.id (connected userid) / check if already liked
INNER JOIN img_likes AS ilt ON ilt.img_id = uu.imgID -- get img total likes
-- maybe group by, but i've not idea 
ORDER BY up_time DESC -- for the user_uploads.* ???

And this is the php code:

if (login_check($mysqli) == true) {
    $user_id = $_SESSION['user_id'];
}
else {
    $user_id = ip2long(get_ip_address());
}

if ($stmt = $mysqli->prepare(" SELECT user_uploads.* AS uu, members.*, COUNT(img_id, user_id) AS usr_liked, COUNT(img_id) AS total_likes
                               FROM user_uploads
                               INNER JOIN members AS m ON m.id = uu.user_id
                               INNER JOIN img_likes AS il ON il.img_id = uu.imgID AND il.user_id = ?
                               INNER JOIN img_likes AS ilt ON ilt.img_id = uu.imgID
                               GROUP BY img_id, user_id
                               ORDER BY up_time DESC")) {
    $stmt->bind_param('i', $user_id);
    $stmt->execute(); // get photos

    //$Items->store_result();
    //$Items->bind_result();

    $Items = $stmt->get_result();
    
    foreach ($Items as $ItemInfo) {
    
        $liked = $ItemInfo['usr_liked'];      // will this work???
        $total_likes = $ItemInfo['total_likes'];
        
        $imgID = $aItemInfo['imgID']; // user_uploads
        
        if ($liked == 0) {
            $like = 'Like';
        }
        else if ($liked == 1) {
            $like = 'Unlike';
        }
        
        $photo_list .= '
        // img
        // button
        <span class="total_likes" id="">' . $totallikes . '</span>
        <a id="' . $total_likes . '" class="likes">' . $like . '</a>
        ';
    }
}

I'm not sure how to do this and I'd appreciate any help.

 

Thanks in advance!

[hello freaks!]

Hi everyone!

 

I just started my first php project and I think I'm going to need some help from the experts

 

I've never done something (this complex?) like this, a real website to share with friends and all the internet.

 

Also I'll try my best to help others as much as i can...

 

And ofc, I hope I improve my phpfreaks skills.