Forward - I understand that learning how the lines of code work is a little hard to do with it being commented inline, however I have provided a nice pdf file which has everything nice and organized in it, as well as an easier to read explanation of the code.

NOTE - The PDF has not at this time been updated.

 Pagination_tutorial_final.pdf ( 253.21k ) Number of downloads: 415


Pagination, what is it and why would I want to use it?
Pagination is simply a way to split content of web pages up into multiple pages. People generally want to use it because it is easy enough to make and customize as well as the nice fact that it saves on server strain and load time of pages. While you do have to run the query multiple times beofr ll the results have been seen, it speeds things up slightly (more noticibly with the more records you have to query from) and also makes the site more asthetically pleasing to people. Say for instance you had a databse with 10 records in it, that would be fine to have all shown in a single query, but if you had a database with over 20,000 records that would add a lot to the server strain and the page would seem to go on forever; making asthetics not count for as much and getting users annoyed as they trudge through all the records looking for the one they want. If that isn’t enough to make you want to use pagination on your pages, this should help. The whole pagination script (including our example) can be written in under 59 lines of code!

Have I seen pagination working around the web?
The simple answer to this question is ‘yes’, If you are reading this tutorial on a forum then you have seen pagination in almost every topic and page the forum has (the little numbers at the bottom of the screen that look something like this:
Page 1 2 3 4 5 … 190

Onto the tutorial

For the purpose of this tutorial we will say that we have a database called DATABASE that has a table called images in which there are a number of image urls. The whole database looks like this:

DATABASE

Images

url (varchar)
image_id (int)

Here is a look at what you will be creating:


Looking at the code
Well, I understand that everyone is not a php guru (especially when starting out) and that it is helpful to see the code along with what each line does, so here it is (outside of the previously used comments) in an easy to read manner. Here we will go through the code, by each important section and say what it does.


Above is where you tell the document that php content starts after this point. It is the way the server knows (assuming your document is a .php file) what to do when parsing the file.


In the above statement we are connectinng to the databases selecting a specifit database for future queries to be directed at.


In this code we get the variable page (set in the URL) and set it to our own variable $page. Then we check to make sure that the $page variable is something that the rest of the application can use, if it isn't we set it to 1 (page 1).


Here we go and limit the number of results we accept from the queries.


Next we create the class "paginate" and give it an function init) that takes 2 variables ($limit and $page). After that we go and query the database for the number of records that it holds and count them, then divide the total number of records by the number of records that we are displaying per page ($limit) to get the number of pages in total ($total_pages). After that we set the next and previous page links.


Then we make a check against the $page variable (again) to make sure that it is readable and avalid page number. If$page is less than or equal to 1 we set $page equal to 1 and set $prev to nothing (get rid of the previous page link). If the first check didn't return true (and $page has not been changed, and $page is greater than or equal to the total number of pages ($total_pages) we set $page to the total number of pages ($total_pages) adn set $next equal to nothing, thus getting rid of the next page link.


After the prior checks we makea few more to see it $page is greater than 2, if true we set $prev equal to a First Page link and then whatever it was previously set to (basically we add a first page link to the mix). Additionally we check if $page is less than 1 less than the total pages ($total_pages-1), if true we add a last page link to $next.


Once we have reached this point in the code we calculate where the query should be recieving records after ($start) and then query for the next ($limit) records in the database. Starting from $start and ending at $start+$limit.


No we are ready to start putting the page content ($content) together. The $times_done variable is a basic counter of how many times the while loop has been completed. In the while loop we set $row to whatever information was stored int eh records one record at a time, then make a few checks against $times_done to see where the content shoudl be lined up vertically (every 5 times through the loop it starts over at a new line). Then we add a link and thumbnail of the image (source given by $row['src'], the table field name in the database).


Above we add the paging links to the $content variable and return what has been put together to the calling function.


Finally we create a pointer to the class (paginate) called $class and tell php to print out (echo) whatever is returned from the paginate function init. After that is done we close the database connection (adds security and is good habit) and end the php section of the application.

The only 2 tags we didn't go over are the <div> and </div> tags which are actually HTML, but basically are used to create a container for the content that holds it in the same general place when in a larger HTML file.

Okay, now that I have finished understanding the code, how do I implement it?
Implementing the code can be one of the hardest, or one of the easiest things to do, depending on what you personally need to change for it to fit what you are trying to accomplish. If you are just going for a quick and ugly thing like what we created in this tutorial then all you really need to do is change some of the variables to suit your needs. If that was the case, just change the red, and green text to what fits your needs and everything will be fine.
However, if you want to do something more complex it will take more work. You will still have to change the red and green text, but you will probably also have to change some of the normal black text as well. For instance, this tutorial was built so that if $limit is greater than 5 there will be multiple rows of images per page. So those things would need to go.
The basics behind implementing pagination on a website are to have a database that you can connect to and then have data in it that can be queried. Then you need to query for the data and use it to add to content as what we did with the $row[‘src’] variable. The loop will assign all the variables to a part of $row, making it an array; you just call for the variables from the array. The array key names are easy enough to figure out as they are exactly the same as the column names in your database. Meaning that, in addition to the $row[‘src’] variable we could have called to $row[‘image_id’] and received a number. Realize though that if you change the SELECT * FROM to something like SELECT src FROM you will only get the src column from the database, so make sure that you are querying everything that you need from the database to avoid making multiple, meaningless queries.

This post has been edited by BetaWar: 6 Jun, 2008 - 12:51 PM

Great start and everything but a few comments...

1) This tutorial needs to have the code cleaned up a bit and organized. I am seeing it as a jumbled up mess that is hard to follow.

2) While you show an example, you seemed to skimp out on actually explaining much of it. Reiterate a bit of what you show in words so that people can actually follow along.

3) Maybe make it more clear as to why you need pagination. You make an attempt at the start, but don't go far enough. I could argue the point about making it easier on the server because pagination actually causes more server requests, but that is something I will leave to you to explain further.

But like I said, great start here. Expand on it more and you will strike real gold. Keep up the good work.

Okay, thanks fo rthe input I will update it as soon as I get a changce (which will probably be sometime tomorrow).

<edit>
Okay, I just got around to getting it updates (had to go help out with a garage sale all morning).
</edit>

This post has been edited by BetaWar: 6 Jun, 2008 - 01:50 PM

Thanks for pdf.

I would like to see how you would auto generate the page links???


I notice you used next and prev but I don't see page markts. If you can give a example of how would you generate the page links so if a clieck select page 2 then page 2 would show.

I hope you can squeeze that in.

Other than that. Good job with the tutorial.

Hrmm. Normally when I use the method described above, I just a select count(1) instead of just running the whole query and using mysql_num_rows to fetch the total rows.

There is a better option than this. You can use the mysql SQL_CALC_FOUND_ROWS keyword.

Basically you do something like:

$start = $pageSize * $pageNum; // where $pageNum is the page the user is trying to display.
$end = $start + $pageSize;

Then turn around and use that in a query:

$result = mysql_query( "select SQL_CALC_FOUND_ROWS from table limit $start, $end" );

This will cause mysql to save the total that would have come back if the limit had not been used. You can fetch this value with:

$result = mysql_query( "select found_rows()" );

In the tutorial code, you are running two queries. Where the database engine is used to figure out data to fetch in both. In my example here, you are running one query, then the second database call simply returns a value that mysql has saved in the connection session (so the second query does no work, just fetches a number).

Maybe it's only a small performance increase, but it's the only way I do this nowadays. Every little bit helps.

This post has been edited by Ridikule: 21 Jan, 2009 - 01:29 PM