Example: Pagination

[Terry]

I read in another post someone asking about pagination.

 

This is an example from a Thread of mine here.

 

 

This is ONE possible method to do pagination, it uses MySQL. I am only going to show the SQL as it is the main component that makes it work. From the understanding of how it works you can easily create the PHP required to make it work for any pagination purpose. I do NOT know if this is the method Larry uses but it is one that I use in my own projects.

 

 

Here is the SQL:

 

SELECT * FROM table ORDER BY record_id LIMIT $pageNum, $totalRows

 

 

I simplified the SQL from my other thread to better explain how this works.

 

This is a normal SELECT statement.

 

I am using the record_id to order the display of the rows returned from the database, you can further choose ASC or DESC depending if you want most recent first or oldest first.

 

I tend to use the ID of the table rows to filter most recent records instead of date because in general the highest record id is the most recent record. This is not 100% correct in all situations but I find in my own projects it is good rule of thumb but I double check via the programming logic before saying yes that is indeed correct because it might not be.

 

What makes the pagination work is the last part of the SELECT statement.

 

LIMIT $pageNum, $totalRows

 

I am not going to explain the purpose of LIMIT or how it works, you can go to php.net and look it up yourself or read about it in one of Larry's other books.

 

$pageNum is the current page you happen to be on, the one you want to retrieve. It starts at 0 and goes up. Page 5 would be $pageNum == 4;

This is what is used to move from page to page, it is NOT saying I want X pages displayed from the records.

 

$totalRows is what determines how many pages there will be.

At each call to the database get X records, depending on how many records there are in the database in total and how many you get out at one time will determine how many pages there are.

 

Example Only:

-----------------

50 total records/rows in the database.

 

$totalRows=5;

 

This would make 10 pages assuming only 50 records in database.

 

$totalRows=15;

 

Now there are 3 pages.. This is a dynamic thing, you do not need to do the math for it, MySQL does it all for you.

 

You do however have to create the PHP to properly manipulate the returned result from the database. At each database call the number of rows are returned from the particular page you tell MySQL to retrieve.

 

It IS very possible to tell MySQL to return records that do not exist from pages that do not exist OR records that do not exist that did exist on a page but don't any more. You will get weird results in your pages when calling for data and/or pages that don't actually exist.

The returned mysql result will be NULL or blank but depending on your code either nothing will show up or weird things will happen because you did not account for no results returned.

 

That is the very basics of how this particular method of pagination works. There are others but this one I find works well.

 

Another method I came across is to retrieve ALL records at once and then in code or javascript filter the results and make however many pages you want, but you need to think through the math and how many records per page etc... Also if you have a lot of records and a lot of visitors you can end up crashing your hosting server and your site could come to a crawl and not be speedy loading with this method.

 

There are other ways to do pagination but the one I show above works good for me, others prefer other ways though, it comes to personal preference I guess.

 

Hope this was useful.

 

Thanks.

[HartleySan]

Terry, thanks for posting your findings, but you know that Larry talks about this is his PHP 6 & MySQL 5 book, right? Also, there are plenty of sites out there that already explain the LIMIT statement in detail.

 

Didn't mean to rain on your parade there. Thanks, man.

[Terry]

I don't have that particular book so didn't know about that. Just thought I would give an example I use, it is not the only way though.

 

Thanks.

[HartleySan]

Oh, okay. Sorry if I sounded rather harsh in my post. I was just thinking why write about something that's already been written about well by so many people. Anyway, your examples are appreciated. Thank you, and sorry if I sounded like a prick.

[Terry]

Oh, okay. Sorry if I sounded rather harsh in my post. I was just thinking why write about something that's already been written about well by so many people. Anyway, your examples are appreciated. Thank you, and sorry if I sounded like a prick.

 

 

No worries. I didn't see it talked about here in THIS forum. Though I have not looked too deep into the other sections of the site. I am sure it is redundant info but when I get into posting mode I just like to share. Looking over this forum it seems I am sort of taking over with gabillions of posts so will probably take a break from posting for awhile.

 

Thanks.

[HartleySan]

Nah, don't take a break. You taking over a "gabillion" posts makes things exciting. Besides, if you have something to say, you should say it.