What is the tried and true way of counting the number of rows returned from PHP `mysql_query` function?

Question

I know about mysql_num_rows ( resource $result ) but I have read somewhere that is it not 100% accurate. I have seen other alternatives that actually gets each row and run a counter but that sounds pretty inefficient. I am interested in seeing what others do to get an accurate and efficient count.

Answer 1

mysql_num_rows() is accurate, as long as you are not using mysql_unbuffered_query().

If you are using mysql_query(), mysql_num_rows() is accurate.

If you are using mysql_unbuffered_query(), mysql_num_rows() will return the wrong result until all rows are retrieved.

Answer 2

use mysql_num_rows() when you've done a SELECT or SHOW query and mysql_affected_rows() in case of INSERT, UPDATE, REPLACE or DELETE queries.
both are accurate enough!!

Answer 3

It would help to know exactly what you're doing with the row count, but I'll assume it's for the very standard application of paging result sets.

Issue two queries. The first is a COUNT query with all of the WHERE criteria of the second. The second is used to fetch the rows. It's either this or loading 1,000 rows into memory when you're only going to display 10 of them.

However, if you need to guarantee accuracy, you'll have to wrap the two queries in a read consistent transaction. Otherwise a query that happens to run between the count and data queries could invalidate the paging info.