R M R M - 6 months ago 7
MySQL Question

PHP SQL how the get the exact count from a table

I have two tables.

FAMILY
- id
- ecc.

PRODUCTS
- id
- idfamily
- type
- ecc

FAMILY & PRODUCTS are connected with family.id = products.idfamily

I'm doing a families pagination with filters based on products types, so I'm trying to get the exact count of FAMILY containg almost one product with a specific type.

First query is ok, I get all the family:

if (!isset($_GET['type']) || $_GET['type'] == 'all') {
$query_family=mysql_query("
SELECT * FROM family
");
}
$count=mysql_num_rows($query_family);
**result = 166**


Unfortunately, the following second query is wrong:

} else {
$query_family=mysql_query("
SELECT * FROM family f LEFT JOIN products p ON f.id = p.idfamily
WHERE p.type = '$_GET[type]'
");
}
$count=mysql_num_rows($query_family);
**result = 500+**


it's wrong because I get ALL THE PRODUCTS WITH A TYPE, but I'm trying to get THE NUMBER OF FAMILIES CONTAING PRODUCTS WITH THE SELECTED TYPE ($_GET[type]).

Thank you

Answer

You should have SELECT distinct f.id from family f LEFT JOIN products p on f.id = p.idfamily WHERE p.type = '$_GET[type]' in the second query I think.