Frank Lucas Frank Lucas - 3 months ago 6
MySQL Question

PHP counting records from other table using id

I have a table prices that looks like this:


id - price - amount


I have an other table winners that keeps what price the users have won. It looks like this:


id - userid - priceid


So the id of my table prices is the sale as the priceid in the winners table.

Now let's say I want the amount of winners that have won price X and price X has id 1 for example. I've tried writing a query but it doesn't seem to make much sense.

$price = "X";
$count = $conn->fetchAll("SELECT winners.id FROM winners INNER JOIN prices ON winners.priceid=prices.id WHERE prices.price='".$price."'");


If anyone can help me out I would appreciate it very much

Many thanks in advance!

Answer

If you want the number that have won price X :

$count = $conn->fetchAll("SELECT COUNT(winners.id) AS nb_winners FROM winners INNER JOIN prices ON winners.priceid=prices.id WHERE prices.price='".$price."'");
Comments