note 95006 deleted from function.mysql-num-rows by thiago
| From: | thiago@php.net | Date: | Tue, 08 Dec 2009 15:17:44 +0000 |
| Subject: | note 95006 deleted from function.mysql-num-rows by thiago | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-163714@lists.php.net to get a copy of this message | ||
Note Submitter: matt at kynx dot org
----
Tom Fejfar's note on using FOUND_ROWS() along with a UNION query won't produce ordered
results suitable for pagination, which is what most people want from FOUND_ROWS().
SELECT SQL_CALC_FOUND_ROWS
id, NULL as num_rows
FROM t
LIMIT 0, 3
UNION
SELECT NULL, FOUND_ROWS()
ORDER BY num_rows, id DESC
On a table with 10 rows, what we're looking for is a result like:
10, NULL
9, NULL
8, NULL
NULL, 10
What we actually get will be something like:
3, NULL
2, NULL
1, NULL
NULL, 10
The result of the first select is in 'database order' before the LIMIT is applied.
Unfortunately adding the ORDER BY to the first select and enclosing in parenthesis does not work
either.
(SELECT SQL_CALC_FOUND_ROWS
id, NULL as num_rows
FROM t
ORDER BY id DESC
LIMIT 0, 3)
UNION
(SELECT NULL, FOUND_ROWS()
ORDER BY num_rows, id DESC)
Produces:
10, NULL
9, NULL
8, NULL
NULL, 3
The final count is of the limited query, not the full result set.
Don't waste your time using UNION. If you want to use FOUND_ROWS(), use mysqli::mulit_query to
avoid the race condition.