On Thu, 16 Oct 2003, Dejan Milenkovic wrote:
> I'm not fammiliar with internal MySQL architecture and exactly how things
> work but I was wondering what is the most effcient way of spliting reports
> over multiple pages. Is there a preformance difference between these two
> codes, specialy if there are some complex conditions and joins that should
> be done to get result.
>
> $page=1; // this is set via GET or POST
> $items_per_page=10;
> $sql="SELECT COUNT(*) FROM table";
> $result=mysql_query($sql)
> $number_of_items=mysql_numrows($result);
> $start=($page-1)*$items_per_page;
> $sql="SELECT * FROM table LIMIT $start, $items_per_page";
> $result=mysql_query($sql)
> while ($row=mysql_fetch_assoc($result)) {
> // here goes output
> }
The above is better. MySQL doesn't return 4,000 pages to PHP, just the 10
you ask for. Faster, cleaner, better. Select count(*) from table is
super-fast, so almost no overhead there.
> $page=1; // this is set via GET or POST
> $items_per_page=10;
> $sql="SELECT * FROM table";
> $result=mysql_query($sql)
> $number_of_items=mysql_numrows($result);
> $start=($page-1)*$items_per_page;
> mysql_data_seek($result, $start);
> for ($i=0; $i<$items_per_page; $i++) {
> $row=mysql_fetch_assoc($result)
> // here goes output
> }
---------------------------------------------------------------------------
Peter Beckman Internet Guy
beckman@purplecow.com http://www.purplecow.com/
---------------------------------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
