Jump to content
imgrooot

How do I count rows from multiple products?

Recommended Posts

Posted (edited)

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?

Edited by imgrooot

Share this post


Link to post
Share on other sites

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 = ?

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.