Paging

[BNPMike]

What is your preferred method for presenting data over a number of pages?

Eg A start page presents a search criterion to MySQL. PHP creates an SQL query which presumably returns - lets say - 200 records into a PHP variable. PHP reads the first -let's say - 50 records and generates a page. The user views that page and the presses a button 'Next'. What happens now? Does PHP run the query again only this time ignore the first 50 records, and display records 51-100? That seeems inefficient but if somehow you tried to store the results-set somewhere you'd have problem cleaning up when the 'session' finished. Any guidlines you can offer?

 

[DRJ478]

Have you ever used PHPMyAdmin? If you have it inspect the SQL statements made when paging. There is such a construct in SQL as LIMIT offset, numberOfRows.

SELECT * FROM myTable LIMIT 0,10

returns the first records. You can set the offset (row where it begins) and the number of rows to be returned. Don't fetch more rows than you want to display.

 

[sleipnir214]

Part of it is making use of MySQL's LIMIT clause in SELECT statments.

Something like:

SELECT * FROM foo ORDER BY <some order> LIMIT 50,50

would return records 51 through 100 from a result set. This is handles efficiently by MySQL.


The other part is making links available on your page which hand the script which page of data to display. Assuming that the script will use a standard page size of 25 records, when the script displays the 3rd page (records 51 through 75) would have links like:

<a href=&quot;thescript.php?page=2>Previous</a>
and
<a href=&quot;thescript.php?page=4>Next</a>

PHP can then calculate which records to tell MySQL to provide.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[BNPMike]

Wow. That's impressive.

Many thanks, chaps.