count and limit at one query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mrblack
Forum Newbie
Posts: 6
Joined: Mon Oct 19, 2009 5:22 pm

count and limit at one query

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: count and limit at one query

Post 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”.
There are 10 types of people in this world, those who understand binary and those who don't
mrblack
Forum Newbie
Posts: 6
Joined: Mon Oct 19, 2009 5:22 pm

Re: count and limit at one query

Post 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:

Code: Select all

FOUND_ROWS()
1
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" :(
Post Reply