help with counting mysql results

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.

Because it returns 12 rows because limit is set to 12 (or whatever the desired number of results per page is) — A-OK
– A-OK, Commented Apr 13, 2011 at 23:21
Possible duplicate of How to get the total found rows without considering the limit in PDO? — Pekka
– Pekka, Commented Apr 13, 2011 at 23:26
considering "per page" sounds a lot like pagination, I added some reading material if you're truly worried about performance. — Khez
– Khez, Commented Apr 13, 2011 at 23:31

Skrol29 · Accepted Answer · 2011-04-13 23:49:58Z

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

answered Apr 13, 2011 at 23:49

Skrol29

5,6031 gold badge23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A-OK Over a year ago

Cool but how does the MySQL server know for which query to return the number of rows if there are hundreds of queries per second on the server of the similar query from the same script?

Skrol29 Over a year ago

It must be the very next query of the same connection. Same as for RowCount.

Khez · Accepted Answer · 2011-04-13 23:30:46Z

1

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

edited Apr 13, 2011 at 23:30

answered Apr 13, 2011 at 23:23

Khez

10.3k2 gold badges35 silver badges53 bronze badges

3 Comments

A-OK Over a year ago

But the query can get a big bigger, likeselect products.features, products.make_id, products.model, products.price, products.contractprice, 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 (make_id=2) and (formfactor_id=17) and (products.category=22) and (features like "%,32,%") and (features like "%,20,%") order by id desc limit 0,12

Khez Over a year ago

doesn't matter what you select, you ignore every item in the select and ignore the ORDER BY and the LIMIT, just to get the total number of rows, it will be faster than you expect

A-OK Over a year ago

ok thanks I'll give it a try, see how many hundreds of queries per second it does now :)

Collectives™ on Stack Overflow

help with counting mysql results

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related