Page 1 of 1
count and limit at one query
Posted: Fri Nov 27, 2009 7:55 pm
by mrblack
Hi,
I'd like to make something like page count eg.
<< prev [1|2|3|4|5] next >>
Now I have to use 2 queries:
1. count of rows "count()"
2. rows limited with "limit"
Is it possible to make a one query for this case?
Thanks for some advice.
Re: count and limit at one query
Posted: Sat Nov 28, 2009 4:41 am
by VladSun
In general - you can't do this.
Byt MySQL has a hack about it:
http://dev.mysql.com/doc/refman/5.0/en/select.html
SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS(). See Section 11.10.3, “Information Functions”.
Re: count and limit at one query
Posted: Mon Nov 30, 2009 6:52 am
by mrblack
doesn't work for me

I tried it in phpMyAdmin on a table with 10 records:
Code: Select all
SELECT * FROM `table` LIMIT 1;
SELECT FOUND_ROWS();
and the result was:
edit
I forgot one important thing:
Code: Select all
SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 1;
SELECT FOUND_ROWS();
This works, but I can't implement it in php. If I use:
Code: Select all
$stmt = $this->database_manager->prepare($query); //$query with LIMIT
$stmt->execute();
$select_found_rows->execute(); //SELECT FOUND_ROWS();
$select_found_rows->bind_result($count);
echo $count; //outputs limitation (eg. "... LIMIT 3" => outputs number "3")
I've checked php.ini and I have "mysql.trace_mode" set to "OFF"
