fire single query to fetch records for grid pagination

Question

I am fetching records from database in PHP and MySQL and showing it into the jqgrid.

Now i request 1 page which encompass 10 rows data in first request.

to achieve that i fire one query to get total number of records and then calculate totalpages which contains all total records.

and second query to fire limited records i.e. 1 to 10 and next time 11 to 20 and so on.

so the problem is every time i have to fire two queries one to fetch the total number of records and other to fetch limited rows for a grid.

is it possible to fire only one query to achieve above thing ?

to fetch record in a normal fashion the above scenario is fine but if i preform serch then i have to fire two queries one to fetch total number of rows that matches the criteria and other to fetch only limited rows that meets the criteria

Update

Well my limits to access rows is depends upon the total number of records so i dont see any option to fire only one query. Please look how my PHP code is counting limit

1) Fire the query to access total records

$selectMemberData = "SELECT * FROM tbl_member";
    //some db code  ... 
    // store the total records value
    $count = $dbMemberData->numRows; 



if( $count >0 ) {
    $total_pages = ceil($count/$limit);
} else {
    $total_pages = 0;
}

if ($page > $total_pages) $page=$total_pages;


$start = ( $limit * $page ) - $limit; // do not put $limit*($page - 1)

   //Fire another query to fetch limited records 

    $selectMember = "SELECT * FROM tbl_member   LIMIT "  .$start.", ". $limit;

so my concern is when i use autocomplete search at that moment it would be pretty expensive to fire the two queries. and the issue is i have dependent on total records to get start and end limit

Answer 1

select * 
from yourtable 
inner join 
(
select count(*) as count 
from yourtable
) as t2
limit 0,10

which will add on an extra colum of count to each row which will be the total amount of rows from the whole table, in one query.

sqlfiddle for an example

If you'd prefer not to use the join you can use a union:

select id,count(*) as data 
from yourtable
union all
select * 
from yourtable
limit 0, 10

but then you have to declare all the columns in the first select. You can write it like null as column_name for each of them except for the column that you want to hold your count if you want them to be null otherwise they'll just hold random data.

sqlfiddle

I'm assuming you're using a while loop on your data so you could use:

mysql_fetch_assoc($row);
$all_records_count = $row['data'];
while(mysql_fetch_assoc($row)){
...do something with results...}

to get the first row which will hold your count and then start looping through the results after.

Answer 2

Check out SQL_CALC_FOUND_ROWS.

Answer 3

not neccessarily you have to fire two queries. You can fire a single stored proc and get two result sets from it. First one containing limited records and the other one containing total records.

For this you can use MySql's found_rows() function to find out how many actual records in a query that returns limited results using LIMIT clause. Read more about it here http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows.

E.g

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;

then fire this

SELECT found_rows();