imgrooot Posted August 17, 2019 Share Posted August 17, 2019 It's late and I'm not thinking straight. I'm posting this question. Hopefully I get a reply in the morning. I have two tables. TABLE 1 - PRODUCTS TABLE 2 - LIKES I am a User who has posted these products. I want to find out ALL the Likes I have received for all my products. Here is my code. $find_products = $db->prepare("SELECT product_id FROM products WHERE user_id = :user_id"); $find_products->bindParam(':user_id', $my_user_id); $find_products->execute(); $result_products = $find_products->fetchAll(PDO::FETCH_ASSOC); if(count($result_products) > 0) { foreach($result_products as $row) { $product_id = $row['product_id']; $find_likes = $db->prepare("SELECT like_id FROM product_likes WHERE product_id = :product_id"); $find_likes->bindParam(':product_id', $product_id); $find_likes->execute(); $result_likes = $find_likes->fetchAll(PDO::FETCH_ASSOC); if(count($result_likes) > 0) { $get_likes = 0; foreach($result_likes as $row) { $get_likes++; } } } } The issue with the above code is that It only shows the Likes if I echo inside the foreach loop. And it'll show combined Likes from each of my products. But I want to actually combine ALL the Likes from ALL the products and be able show them as a single number, outside of the foreach loop. How do I do that? Link to comment Share on other sites More sharing options...
Barand Posted August 17, 2019 Share Posted August 17, 2019 A single query will do the job without any looping. SELECT COUNT(*) as likes FROM products p JOIN product_likes pl USING (product_id) WHERE p.user_id = ? Link to comment Share on other sites More sharing options...
imgrooot Posted August 17, 2019 Author Share Posted August 17, 2019 8 hours ago, Barand said: A single query will do the job without any looping. SELECT COUNT(*) as likes FROM products p JOIN product_likes pl USING (product_id) WHERE p.user_id = ? So based on what you said, here's the full query. $find_likes = $db->prepare("SELECT COUNT(*) as likes FROM products p JOIN product_likes pl USING (product_id) WHERE p.user_id = :user_id"); $find_likes->bindParam(':user_id', $my_user_id); $find_likes->execute(); $get_likes = $find_likes->fetchColumn(); echo $get_likes; Seems to work fine now. Thanks. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.