2

I'm currently creating pagination on my site, and was wondering, is it possible to return the total number of results within this query ...

SELECT * FROM images 
LEFT JOIN category 
  ON category.id = image.category_id 
WHERE category = :category 
ORDER BY timestamp LIMIT 0, 10

If I can return the total number of results I can use that to create the pagination links on the results page rather than creating a sperate query.

xQbert
  • 34,733
  • 2
  • 41
  • 62
GV1
  • 475
  • 1
  • 10
  • 17
  • Record count is a function of the record set object. you have it in the meta data. – xQbert Dec 18 '11 at 13:00
  • But wouldn't that be 10, because of the LIMIT? – Nanne Dec 18 '11 at 13:03
  • 2
    @xQbert this person wants to know what the number of rows would have been, if there had been no LIMIT clause. – toon81 Dec 18 '11 at 13:05
  • Unless I'm misunderstanding the intent of your query, the query should have a JOIN, not a LEFT JOIN. (category is coming from the category table, right?) – Corbin Dec 18 '11 at 13:17

3 Answers3

2

There is a built-in MySQL way of doing this: SQL_CALC_FOUND_ROWS. It costs you an extra query but the performance impact of that is very slight. It's certainly better than getting ALL results and then counting those in PHP.

According to the accepted answer to this question, it is often more efficient to count the total afterwards (or beforehand, if that suits you better):

SELECT COUNT(id) FROM images WHERE image.category_id = {{insert category id here}}

This is a good way of doing this. What you do NOT want to do, is write a query that SELECTs all rows, and then filter out the ones you want in PHP. See other answers to this question for more on that.

Community
  • 1
  • 1
toon81
  • 868
  • 1
  • 5
  • 13
  • 1
    Can't vouch for the accuracy of it, but the accepted answer on http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count seems to suggest that a second COUNT(id) query should be performed in most situations. – Corbin Dec 18 '11 at 13:11
  • Might be worth editing the answer with an example count query. I would post an answer myself, but feeling astoundingly lazy :). – Corbin Dec 18 '11 at 13:15
0

You can't get the total number of results from the actual MySQL query (you would have to do a separate query), but you can easily do a count in PHP of the results array. Effectively, mysql_num_rows does just that.

SBerg413
  • 14,515
  • 6
  • 62
  • 88
  • To do this, unless I'm much mistaken, PHP has to actually get all those rows. That's very inefficient. If I *am* much mistaken, then MySQL has to get all of them. Less inefficient but it can be a real burden on the server if the number of rows is very great. MySQL knows how to efficiently calculate row counts without fetching the rows, it's best to leverage that ability. – toon81 Dec 18 '11 at 13:08
  • It would transfer all of the rows from MySQL to PHP. Horribly inefficient. – Corbin Dec 18 '11 at 13:10
  • I happen to know that that's how the MySQL C api works, which I believe is what the PHP implementation uses. The PHP manual doesn't seem to stress enough that when you do a query, PHP actually gets all of the rows, whether you fetch them with PHP or not. It's a bit of a pitfall IMO. – toon81 Dec 18 '11 at 13:16
  • Well, they have unbuffered queries, which do not fetch the entire result set. 'Coincidentally' enough, you cannot call mysql_num_rows on unbuffered queries. – Corbin Dec 18 '11 at 13:18
  • 1
    I'm using PDO. But, are you saying when you set a limit to return 10 results PHP's MySQL implementation will actually get all the rows? – GV1 Dec 18 '11 at 13:19
  • It will get all 10 rows if it's a buffered query. The mysql result set will not contain all of the tables rows, so it will not fetch them all. Buffered queries fetch the entire result set for a query. So with a LIMIT 10, at most, 10 rows would be fetched. (Even if only 5 are explicitly retrieved with mysql_fetch_assoc or whatever.) There's a reason why mysql_num_rows(mysql_query("SELECT * FROM table")) absolutely murders performance. As for specific to PDO, I'm not sure if PDO uses buffered or unbuffered queries by default. – Corbin Dec 18 '11 at 13:21
  • @GV1 what we're saying is with a LIMIT 10, it will get 10 rows, even if you `mysql_fetch_(whatever)` only one. – toon81 Dec 18 '11 at 13:27
0

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned.

Original link: PHP PDO - Num Rows

Community
  • 1
  • 1
Mohammad Saberi
  • 12,864
  • 27
  • 75
  • 127