multiple php pages of mysql results q

lunacyfoundme

Ive recently taken up learning php and mysql and have started a website using it to learn it as i go. I was just about how would i display sets of records from a mysql query seeing that mysql 4 doesnt support the fetures needed to make this easy ie triggers and whatnot.

For example if i had a product database and did a search in that that returned 50 results. how do I program the search page to display the first 10 on one page the next 20 on the next page and so on.

seamus

What I've used in the past is a two-query process. First off, you do a "SELECT COUNT(*) FROM myTable WHERE..." to get the number of entries which match the criteria.
Then to actually get a certain chunk of results, I did
"SELECT * FROM myTable WHERE...... LIMIT $lowerbound, $upperbound"
Where the boundary values depend on which page the visitor is looking at.

I'm sure there's a less intensive way of doing this, but my MySQL tends to be along the lines of "whatever works in the smallest number of queries" - I was using it on a relatively small and quiet database. Imagine boards.ie having to perform every search twice.

finnpark

seamus wrote:

What I've used in the past is a two-query process. First off, you do a "SELECT COUNT(*) FROM myTable WHERE..." to get the number of entries which match the criteria.
Then to actually get a certain chunk of results, I did
"SELECT * FROM myTable WHERE...... LIMIT $lowerbound, $upperbound"
Where the boundary values depend on which page the visitor is looking at.

I'm sure there's a less intensive way of doing this, but my MySQL tends to be along the lines of "whatever works in the smallest number of queries" - I was using it on a relatively small and quiet database. Imagine boards.ie having to perform every search twice.

Ineed to do the same. Im using PERL and MYSQL. One way I can think of doing it is to read all the values into an array as usual. Then print out first ten. If there is more than 10 then add link named "2" as in 2nd page of results and pass some variables by means of the URL ie page.php?lowerbound=11 . Keep doing the same until you have all pages listed - its basically doing it using HTML formatting or printing out an extra link for each group of 10 using a for loop. When you click "2" link you basically call the same page with different query details, the first time you go into the page the lowerbound variable should be set to 0 by default.

akari no ryu

If you're using the ADOdb abstraction libraries, then the select limit method works quite well. You set your lowerbound*queries per page to your base value and your results per page can come from either a config variable or a user set session one.