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.
count and limit at one query
Moderator: General Moderators
Re: count and limit at one query
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
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”.
There are 10 types of people in this world, those who understand binary and those who don't
Re: count and limit at one query
doesn't work for me 
I tried it in phpMyAdmin on a table with 10 records:
and the result was:
edit
I forgot one important thing:
This works, but I can't implement it in php. If I use:
I've checked php.ini and I have "mysql.trace_mode" set to "OFF" 
I tried it in phpMyAdmin on a table with 10 records:
Code: Select all
SELECT * FROM `table` LIMIT 1;
SELECT FOUND_ROWS();Code: Select all
FOUND_ROWS()
1I forgot one important thing:
Code: Select all
SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 1;
SELECT FOUND_ROWS();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")