How can I display database query results on one page and use next button to browse more results?

They have: 105 posts

Joined: Mar 2006

Hi,

How can I allows users to view the first few database query results on a page and have a next button displaying the number of results pages? (So not all the results are one page)

Thanks,

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

MySQL's LIMIT option can be used for this. Just like you can use it to limit results, you can use it to show rows that aren't at the beginning of the result set.

For example, just adding 'LIMIT 10' to the end of a query would show the first 10 results. Appending 'LIMIT 11,10' will show 10 results, starting from the 11th, or the next 10 results. Appending 'LIMIT 21,10' will show the third set of 10, and so on.

They have: 105 posts

Joined: Mar 2006

Would I have one MySQL query that has more than one LIMIT clause, or would I have a different query that is executed on different pages? I don't understand how to use one instance of a browse/search page and provide links for the different pages of search results.

For example,

Would I have pages called search_results1.php, search_results2.php etc with a query on each that returns records using LIMIT?

Or would I make use of something similar to this:

search_results.php?username=yuyuiy&nlocation=tyutuy to use in the query but only using one search results page?

JeevesBond's picture

He has: 3,955 posts

Joined: Jun 2002

Sorry this reply is a little late.

Would I have one MySQL query that has more than one LIMIT clause, or would I have a different query that is executed on different pages?

You would use the same query, but change the parameters. See the way our Recent page works, try clicking through several pages. See the page=4 parameter? That's how Drupal works out which records to display.

It's a case of knowing how many records to display per page, then using the SQL SELECT COUNT(*) FROM tablename;, LIMIT and some maths. Smiling

Note: with LIMIT you can specify a start and an end row, for example:

SELECT description FROM tablename LIMIT 10, 50;

will get rows 10 to 50 (returning a total of 40 rows). Get it? Smiling

a Padded Cell our articles site!

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.