How do I count rows from multiple products?

[imgrooot]

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 August 17, 2019 by imgrooot

[Barand]

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

 

[imgrooot]

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.