[Solved] Limiting SQL Results to 5 per page

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Fusioned
Forum Commoner
Posts: 32
Joined: Tue Jan 18, 2005 10:43 pm
Location: Philadelphia, PA

[Solved] Limiting SQL Results to 5 per page

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

search term: pagination.

It's asked for at least once a week here.. sometimes far more.
User avatar
Fusioned
Forum Commoner
Posts: 32
Joined: Tue Jan 18, 2005 10:43 pm
Location: Philadelphia, PA

Post by Fusioned »

thank you feyd!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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?
(#10850)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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?
(#10850)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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 :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
Post Reply