Count how many times product is liked and all the products a particular user has liked in PHP and SQL

Question

I am new to PHP and SQL. I am trying to count how many times product is liked and all the products a particular user has liked.But couldn't get the desired results.

I have 3 tables.

product table: pro_id pro_info pro_price

user table: id username password

and pro_likes table: id user product

In pro_likes table "user" and "product" columns are foreign keys referring to user(id) and product(pro_id) respectively. How can I find out how many times a particular product has been liked and which product a particular user has liked?

I tried the following to find out the former but it return the counts for all the products, not a single one.

$q="SELECT
    COUNT(pro_likes.product) AS likes
    FROM pro_likes                    
    LEFT JOINT products                    
    ON pro_likes.product = products.pro_id";                    
    $r=mysqli_query($con,$q);                
    $r1=mysqli_fetch_assoc($r);                
    echo $r1['likes'];

Can anyone please help?

Possible duplicate of [How to use count and group by at the same select statement](https://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) — devlin carnate, Jan 31 '18 at 20:18
Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) Try create a sample in http://rextester.com — Juan Carlos Oropeza, Jan 31 '18 at 20:35
`couldn't get the desired results.` but you dont say what result you want. Right now seem like you need two separated queries instead of one. — Juan Carlos Oropeza, Jan 31 '18 at 20:36

score 0 · Answer 1 · answered Jan 31 '18 at 20:16

0

You need to group your query by pro_likes.product

answered Jan 31 '18 at 20:16

alanfcm

78
3

Tried that but no luck. Now its giving 1 like to every product – user9250572 Jan 31 '18 at 20:22
Try it like the other post suggests – alanfcm Jan 31 '18 at 20:27
Not a real answer. This should be a comment. – Juan Carlos Oropeza Jan 31 '18 at 20:32

score 0 · Answer 2 · answered Jan 31 '18 at 20:32

You also need to grab the product name if you're going to get unique rows, otherwise all you're doing is counting the counts of the join.

SELECT
COUNT(pro_likes.product) AS likes, pro_likes.product as product
FROM pro_likes                    
LEFT JOINT products                    
ON pro_likes.product = products.pro_id
GROUP BY pro_likes.product;

score 0 · Answer 3 · answered Jan 31 '18 at 21:00

0

SELECT DISTINCT
COUNT([pro_likes.prodcuts]) AS likes, 
pro_likes.product AS product
FROM pro_likes
LEFT JOIN products
ON pro_likes.product = products.pro_id
GROUP BY pro_likes.product

answered Jan 31 '18 at 21:00

CarrieP05

11
1

score 0 · Accepted Answer · answered Feb 02 '18 at 13:01

thank you all for your help. I was able to accomplish this with the following code:

select count(pro_likes.product) as likes from pro_likes join products on pro_likes.product=products.pro_id where pro_likes.product='$pro_id'"

where $pro_id is products.pro_id that has been passed through URL from the previous page.

Count how many times product is liked and all the products a particular user has liked in PHP and SQL

4 Answers4