帮助计算mysql结果

Question

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 for 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.

Answer 1

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()"