help with counting mysql results

I have to modify an existing PHP script so that it displays the total number of matches in the database before it shows the results f开发者_如何学运维or the current page. The query is:

SELECT products.features, products.make_id, products.model, products.price,
       products.id, brands.name AS brandname, brands.id AS brandid
FROM products
LEFT JOIN brands ON products.make_id=brands.id
WHERE products.active=1 AND brands.active=1 AND (products.category=22)
ORDER BY id DESC LIMIT 0,12

How do I get the number of results? mysql_num_rows() obviously returns 12 but I don't know how to use count in this query, everything I try returns some error.

I could just execute the query once without the limit (clause?) but that seems a bit inefficient.

Thanks.

Use the MySQL function FOUND_ROWS():

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

so run the two following queries in PHP, the first statement gives the first 12 rows, the second one gives the total of rows:

$sql1 = "SELECT SQL_CALC_FOUND_ROWS 
  products.features
, products.make_id
, products.model
, products.price
, products.id
, brands.name AS brandname
, brands.id AS brandid
FROM products
LEFT JOIN brands ON products.make_id=brands.id
WHERE (products.active=1) AND (brands.active=1) AND (products.category=22)
ORDER BY id DESC LIMIT 0,12"

$sql2 = "SELECT FOUND_ROWS()"

This would return you the total number of rows the current select matches. The result will be one row, with one field and it will hold the total

SELECT COUNT(*) AS total
FROM products
LEFT JOIN brands ON products.make_id=brands.id
WHERE products.active=1 AND brands.active=1 AND products.category=22

You will have to run this as a separate query.

<?php
$q=mysql_query('above query');
$result=mysql_fetch_array($q);
echo $result['total']; // will print out the desired number of rows

You were probably trying to get the total and the limit in the same select and while that might be possible with a union, but since you were worrying about inefficiency before, this is possible worse.

Since in your OP you hint that this is for pagination. Let me tell you, LIMIT m,n may not be as fast as it sounds. Learn how to improve it and read more about Efficient Pagination Using MySQL