Re: Best way to show multiple pages reports from database

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/ ---------------------------------------------------------------------------