Paged Output Queries

What is it? How is it useful?

[neophyte]

Hello All -

I've been researching books on PHP/MYSQL on line and I came across one that had a phrase regarding "paged output queries". I googled that phrase but came up with a short result set that mostly pointed back to this book.

Can someone tell me what a "paged output query" is... why (and if) it's useful (and under what circumstances) and perhaps a pointer (sticky) to some more net-based information on the construction of such a query?

Thanks to all in advance!

Neophyte

[Demaestro]

Interesting that you were on Google looking for page results and you were in fact using a "paged" results system.

Paged results means rather then dumping all the results onto one page that you break it up into groups of 20 or how ever many you want per page.

Do achieve this you pass limit and offset values to the select statement.

Lets say you have a 100 records in a table called the_table.. and you select them and place them on a page.

Select * from the_table

Now lets say you want to only show 20 at a time...

select * from the_table offset 0 limit 20

This would get the first 20 results. To get the next 20 you would say

select * from the_table offset 20 limit 20

This would get the second set of 20 results because you are offsetting the results set by 20.

Now to do this in code you need to pass the offset value....(You could also pass the limit as a variable so that people can define how many per page they get.)

select * from from the_table offset /'%offset'/ limit 20

Then have your page set up with links.

Page 1 (link:/get_query?offset=0) .. 2(link:/get_query?offset=20) .. 3(link:/get_query?offset=40) .. 4(link:/get_query?offset=60)

You can dynamically set up how many page links there are by checking the count of your query with no limits or offsets passed. Then divide by how many results per page to get how many page links to make...

If you need any help let me know. Hope this helps.

[edited by: Demaestro at 8:12 pm (utc) on Nov. 2, 2007]

[Demaestro]

Oh and to answer your second question.. it is useful so you don't load 1000 rows into one page which can take a long time depending on what you show.

[neophyte]

Demaestro -

Thanks so much for your very detailed answer! Now I understand the functionality of this type of query (and how to implement same due to your code examples!)

Thanks again!

Neophyte