Page 1 of 1

[Solved] Limiting SQL Results to 5 per page

Posted: Wed Feb 01, 2006 10:30 pm
by Fusioned
I currently have a posting site going where it calls a SQL database and sorts out forum-like posts by their unique id which in turn sorts them by most current to least current. I need to limit the results to 25 a page and then have a hyperlink where I can call the next 25 and so forth. You get the idea. Any ideas on how it's done? If it's not as simple as I think it is, I'll gladly post my code. Thanks!

Posted: Wed Feb 01, 2006 10:32 pm
by feyd
search term: pagination.

It's asked for at least once a week here.. sometimes far more.

Posted: Wed Feb 01, 2006 10:38 pm
by Fusioned
thank you feyd!

Posted: Wed Feb 01, 2006 10:46 pm
by John Cartwright
If your interested I've written a pagination class. Feedback is always appreciated :)

I know there are a couple other pagination snipplets in the Code Snipplet forum as well.

Posted: Wed Feb 01, 2006 11:30 pm
by Christopher
feyd wrote:It's asked for at least once a week here.. sometimes far more.
Maybe we should do a TDD session in the Unit Testing forum and build a simple one that people can be refered to. Is there a minimum set of requirements that people ususally want?

Posted: Wed Feb 01, 2006 11:47 pm
by feyd
arborint wrote:
feyd wrote:It's asked for at least once a week here.. sometimes far more.
Maybe we should do a TDD session in the Unit Testing forum and build a simple one that people can be refered to. Is there a minimum set of requirements that people ususally want?
Sounds like a good idea.

Other than being flexible enough to paginate any number of records per page, I can't really think of much else. Maybe a nice to have would be column spanning built in as well. Possibly groupings (headings) but that's getting more into visual and categorization which for the most part should not be included..

Posted: Thu Feb 02, 2006 12:48 am
by Christopher
feyd wrote:Other than being flexible enough to paginate any number of records per page, I can't really think of much else.
That's really the core of it -- limiting results. But there are enough parts (that really should be separate) that it is not that intuitive, so I can understand the questions.
feyd wrote:Maybe a nice to have would be column spanning built in as well.
Can you clarify "column spanning"?
feyd wrote:Possibly groupings (headings) but that's getting more into visual and categorization which for the most part should not be included..
I am guessing that you mean clickable heading that sort on that column. That would be probably be an addition to the basic code that would somewhere trickle down to an "ORDER BY" clause in the SQL. It would be good to separate that part out into the query code, so the code that handles the requests (page number, sort column, etc.) didn't need to know anything about SQL. The other feature beside sorting on columns might be a search feature to reduce the list to less results -- but again that's not for the basic version.

I'll just throw out some random thoughts out:

GENERAL
- OOP hopefully?
- How does it work with other code, such as DB or template libs?
- If HTML is generated how do you handle styles, etc.?
- I notice that some of the classes here generate HTML to make it simpler to use, but that limits customizablity.
- It seems like the solution should be a set of classes but the solutions usually seen here are the easier to use, all-in-one type classes.

QUERY
- Need to be able to add LIMIT clause to SQL
- Is also good do initial query to get COUNT(*) so you can calculate the total number of pages
- What is the query interface? Do we just accept a full SELECT statement. Or do we manage the parts (e.g. fields, tables, where clause) to make it easier to do things like sort order?
- Do we have query object and result set, or just generate SQL and let programmer use their own DB code?

REQUEST PROCESSOR
- Need to determine of a page number is being requested or first time in.
- Calculate related values from page number passed
- Error handling and messages?
- Accept sort order parameters?
- Use Session to keep calculated values or pass/recalculate everything?

PRESENTATION
- Generate full links or just URLs to go to a page number?
- Support next/prev page?
- Support first/last page?
- Generate "Page x of x" type text or just provide access to values?
- What values should be available? Current page, total pages, page start record, page end record, total records?
- Generate links/URLs to control sort order of list?

Posted: Thu Feb 02, 2006 1:28 am
by feyd
with grouping I was more referring to visual breaks in the output itself, but that is a visual design thing more.

spanning, I mistakenly phrased. I was talking about splitting of the entries not only into rows, but columns too.

I personally prefer my paginators to not care of the source of the information, so I simply build them such that they take an array containing that page's supposed records, and a count of how many records were found in total. Display characteristics, such as first page, last page, next page, previous page and so forth would be set previously as class level flags, although for standalone, I guess a third parameter for this would be okay. OOP? I'd hope so, but it doesn't have to be. Output would be handled either via a template system, or entirely separate, where this class/set of functions merely readies the information and calculates the page linking display.

Posted: Thu Feb 02, 2006 3:14 am
by jayshields
I recently wrote my own pagination. It's not as hard as I first thought it would be. One of the last things I realised I needed was a COUNT(*) query to find out how many rows there actually were in total. Then the other SELECT query can just LIMIT FROM, AMOUNT instead of selecting all the rows.

If you're going to go ahead and write your own I'd gladly help :)

Posted: Thu Feb 02, 2006 2:48 pm
by Christopher
feyd wrote:with grouping I was more referring to visual breaks in the output itself, but that is a visual design thing more.

spanning, I mistakenly phrased. I was talking about splitting of the entries not only into rows, but columns too.
Ok, understood. But those are probably display stuff that should be left to the app.

I personally prefer my paginators to not care of the source of the information, so I simply build them such that they take an array containing that page's supposed records, and a count of how many records were found in total. [/quote]I agree.
feyd wrote:Display characteristics, such as first page, last page, next page, previous page and so forth would be set previously as class level flags, although for standalone, I guess a third parameter for this would be okay.
Yes to class level flags.
feyd wrote:OOP? I'd hope so, but it doesn't have to be.
I hope so.
feyd wrote:Output would be handled either via a template system, or entirely separate, where this class/set of functions merely readies the information and calculates the page linking display.
I think separate as I don't want to get into template wars.

I found three from a quick search here in the forums (here may be more) and got them all to work:

jspro2's create_pagination() function - just does the links, but in a specific BB style. I like that it is standalone. I'm not wild about HTML in the class but I see how it simplifies things. If it was a class the HTML could be definable.

jcart's pagination class - Nice class, but has the same lack of output customization that the previous function has. I also don't like having the database calls in there. A composite DB class would be cleaner.

blacksnday's paginate() function - Pretty rough. I uses globals, it's all echos inside, and it also has the db calls inline as well.

Posted: Thu Feb 02, 2006 3:06 pm
by Christopher
jayshields wrote:I recently wrote my own pagination. It's not as hard as I first thought it would be. One of the last things I realised I needed was a COUNT(*) query to find out how many rows there actually were in total. Then the other SELECT query can just LIMIT FROM, AMOUNT instead of selecting all the rows.
Yes, adding an initial COUNT(*) query is a good enhancement. It is pretty simple, but creating a generalized class is more difficult. It would be good to have a solid example to point to that had some independence from the presentation and data layers that it is in contact with.
jayshields wrote:If you're going to go ahead and write your own I'd gladly help :)
I was proposing doing the development here as a team/pair programming TDD exercise. We could build it together. Do you use unit tests? If there aren't any TDDers around here we could just start coding. But we need a basic spec for it first.