SELECT DISTINCT COUNT (*)?

[HDFilmMaker2112]

I'm trying to come up with the MySQL way of doing this:

$likeValues = "$tbl_name2.keyword LIKE '%" . implode("%' OR $tbl_name2.keyword LIKE '%", $keywords) . "%'";

$query5 = "SELECT DISTINCT product_id FROM $tbl_name2 WHERE $likeValues";
$total_pages = mysql_num_rows(mysql_query($query5));

 

That returns the results I want, allowing me to generate a "Showing 1 -4 out of 7 results".

 

If I use this:

$query5 = "SELECT COUNT(*) as num FROM $tbl_name JOIN $tbl_name2 USING(product_id) WHERE $likeValues";
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages['num'];

 

It returns "Showing 1 -4 out of 14 results" or "Showing 1 -4 out of 24 results". Depending upon what the search query is, there are only 7 products in the database. 

 

If I add "GROUP BY product_id" it throws an error saying Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

 

I'm looking to see if this can be done in a SQL query, as I know it would return results faster.

[btherl]

Try selecting count(distinct product_id).  Alternatively you can put your original query in a subquery and wrap "SELECT COUNT(*) FROM ( ... )" around it.

[HDFilmMaker2112]

$query5 = "SELECT COUNT(DISTINCT product_id) as num FROM $tbl_name JOIN $tbl_name2 USING(product_id) WHERE $likeValues";
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages['num'];

 

Works. Thanks.

 

EDIT:

Trimmed it down to this:

 

$query5 = "SELECT COUNT(DISTINCT product_id) as num FROM $tbl_name2 WHERE $likeValues";

I'm not actually pulling any product information, just counting how many results are returned based on keywords in the query. So it seems useless to query a second table when nothings pulled from it.