Yes. I just use the same method which Curt mentioned below and seem well. But I met the problem a little bit complex that the sql used "group by". Any idea? Best regards, Yang Shiqi -----Original Message----- From: Curt Zirzow [mailto:php-general@xxxxxxxxxxxxxxxxx] Sent: Sunday, January 09, 2005 2:37 AM To: php-general@xxxxxxxxxxxxx Subject: Re: Re: Pagination Optimization * Thus wrote Bruno B B Magalhes: > Thanks for your help, in fact helped a lot... Now my function only > performs 2 queries, here it is: > > ======================================= > function fetch_paginated($query='',$page=1,$itens=20) > { > $this->query($query); This here is going to be your big bottle neck. You're requiring your database to fetch all the results. You're goal with pagination is to know basically how many rows there are from the result with minimal effort there are two ways i know that can accomplish this rather efficiently: 1. use the SQL_CALC_FOUND_ROWS option (i'm assuming this is mysql from your LIMIT statement). This way you can always use the LIMIT based on the page and items you want, and still know how many items would have been in the results. This will result with simply one query to the database. 2. Modify your select statement to issue a count(*) of the query, to accomplish this automatically you can do something like: /* find the coulumns to replace with count(*) */ $match = '/(select)\s+(.*?)\s+(from\s+.*)/i'; /* row to start on; calculating what page they are * on to the actual row number */ $start = (($page-1) * $items); /* replace fieldnames with count(*) */ $replace = '$1 count(*) as qty $3'; /* now replace the sqlqty and make the limit query */ $sql_qty = preg_replace($match, $replace, $query); $sql_qty = preg_replace('/(order|group) by.*/', '', $sql_qty); $sql_limit = $query . " limit $start, $items"; And now you have $sql_qty that returns the number of total rows are available and $sql_limit which give you the actual results. The first usage, is probably the fastest approach (i havn't done any benchmarks), but it does limit to you with *only* mysql >= 4.0 and and is not a common thing in other dbms (iirc). The second option seems like a lot of work but, i can guarantee you that it will be much faster than selecting all rows in a resultset and figururing out what to do, expecially on the later pages. Here is some code that uses method 2, keep in mind that the does several things, like generating the navigation (which makes it more complex). I should probably seperate the pagination method a bit more. http://zirzow.dyndns.org/html/php/code/paginate.php And an example usage of it: http://www.bigstockphoto.com/search.php HTH, Curt -- Quoth the Raven, "Nevermore." -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
