note 95006 added to function.mysql-num-rows

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. ---- Server IP: 213.181.100.226 Probable Submitter: 81.171.145.217 ---- Manual Page -- http://www.php.net/manual/en/function.mysql-num-rows.php Edit -- https://master.php.net/note/edit/95006 Del: integrated -- https://master.php.net/note/delete/95006/integrated Del: useless -- https://master.php.net/note/delete/95006/useless Del: bad code -- https://master.php.net/note/delete/95006/bad+code Del: spam -- https://master.php.net/note/delete/95006/spam Del: non-english -- https://master.php.net/note/delete/95006/non-english Del: in docs -- https://master.php.net/note/delete/95006/in+docs Del: other reasons-- https://master.php.net/note/delete/95006 Reject -- https://master.php.net/note/reject/95006 Search -- https://master.php.net/manage/user-notes.php