|
|
|
 Pretty much every website you view, which displays a large number of images, links etc from a database, such as this website, will create pages in order to reduce server loads, and creating a better browsing experience for th...
View Tutorial
|
|
|
|
Pretty much every website you view, which displays a large number of images, links etc from a database, such as this website, will create pages in order to reduce server loads, and creating a better browsing experience for the visitor. After all, who likes having to scroll down to the bottom of a very large page?
This technique is called Pagination, and requires you to have some minor math skills!
Dont worry though, this tutorial will guide you through the in's and out's of creating this type of program.
Lets look at a basic MySQL database table containing the information we want sorted into pages.
tbl_clients
uid | client_website | client_company | client_added
----------------------------------------------------------------------
1 | www.cybanworld.com | CybanWorld | 1242760951
2 | www.google.com | Google | 1242770498
3 | www.ask.com | Ask | 1242777777
4 | www.yahoo.com | Yahoo | 1242770998
5 | www.w3schools.com | W3schools | 1242782334
6 | www.facebook.com | Facebook | 1242785509
7 | www.cybanmedia.com | CybanWorld | 1242790011
8 | www.amazon.com | Amazon | 1242799333
The uid column is our primary key, and an auto-increment column. The client_added column contains a unix timestamp of when the row was inserted.
This is obviously a very small table, so we'll say we'll display 2 records per page, therefore there will be 4 pages.
So, we have the table. Lets create an action plan of what we have to do programming wise in order to display the results as we want them:
1. Count all the records and save the number as a variable
2. Find out what page you want to start on, and how many records to get
3. Use SQL to grab the required records from the database
4. Create a list of pages to choose from
Lets start with the first point.
The reason we count all the records is because we will use this number to calculate how many pages we ultimately need.
The SQL statement used to count the records must be in relation to what you want displayed. So, dont forget to use the WHERE clause in the statement if applicable.
This is what my SQL and PHP program for counting the records will look like:
$recordSet = mysql_query("SELECT COUNT(uid) AS cid FROM tbl_clients");
while($row = mysql_fetch_array($recordSet))
{
$data['record_count'] = $row['cid'];
}
mysql_free_result($recordSet);
This script counts the total number of records with UID set. It then saves this record count to the variable $data['record_count']
Ok, to do this, we're going to need to retrieve a variable from the URL. This can be whatever you want (page=1,p=1 etc).
This variable will tell your script what page you want to view, and will be used to calculate the record number to start on. Lets start by defining this variable:
$data['page'] = isset($_GET['page']) ? $_GET['page'] : 1;
Ill quickly go over what this statement does, for those unfamilier with this type of programming.
This is much like an IF condition, and can be used to store values to databases.
There are 4 parts, seperated by 3 symbols.
The first part $data['page'] is a variable and will be used to store what page you will be accessing.
The next part isset($_GET['page']) is the IF part. It evaluates everything after the = symbol, and before the ? symbol.
The next part after the question mark (?) and before the colon symbol (:), is what the variable is set to, if the condition is true.
If the condition is false then the value after the colon is assigned to the variable.
In this case, the default page is 1, as we want to start on page 1.
In addition, we also want to find out how many records to display. This can be done by the same method as above.
$data['records_per_page'] = isset($_GET['rpp']) ? $_GET['rpp'] : 20;
So now we have our two required variables. These will help to tell your script what record to start on, and how many records to get.
However, these values need to be inputted into a formula. Lets take a look at it:
$data['start_on_record'] = ($data['records_per_page']*$data['page'])-$data['records_per_page'];
The variable $data['start_on_record'] holds the record number which we want to start on. It is calculated by multiplying the records_per_page variable by the requested page, then taking the records_per_page.
Lets take a look at this, after the server has assigned values to the variables:
Page One: 0 = (20*1)-20
Page Two: 20 = (20*2)-20
Page Three: 40 = (20*3)-20
Page Four: 60 = (20*4)-20
And so on. As you can see, this simple formula allows you to tell the server what record you want to start on.
Lets build the SQL to grab the records.
SQL is going to be used to communicate with the databae, and grab the records we want displayed. The LIMIT and ORDER BY clauses will be used, to tell the database what records we want, and in what order.
Lets take a look at the SQL and PHP program to retrieve the data:
// Grab requested page number, and how many records to display per page
$data['page'] = isset($_GET['page']) ? $_GET['page'] : 1;
$data['records_per_page'] = isset($_GET['rpp']) ? $_GET['rpp'] : 20;
// Find what record to start on
$data['start_on_record'] = ($data['records_per_page']*$data['page'])-$data['records_per_page'];
// MySQL to grab results and display data
$recordSet = mysql_query("SELECT * FROM tbl_clients ORDER BY client_added DESC
LIMIT {$data['start_on_record']},{$data['records_per_page']}");
while($row = mysql_fetch_array($recordSet))
{
// Format data into HTML here
}
mysql_free_result($recordSet);
The green text is the SQL statement which will grab the records. As you can see, I've added the $data['start_on_record'] and $data['records_per_page'] variables into the LIMIT clause.
I've also added the ORDER BY clause. I want the program to display records based on when the client was added, from newest to oldest.
And thats the MySQL! Not as hard as it seems!
So the final thing to do, is to create a list of pages which the user can choose from. This is where the $data['record_count'] variable comes in.
We first need to know, how many pages there are in total. Lets look at the Math and PHP involved with this:
$data['number_of_pages'] = ceil($data['record_count']/$data['records_per_page);
To find the number of pages needed, you need to divide the total records found, by the number of records per page, for example:
100 records / 20 records per page = 5 pages.
The ceil() function is used, because sometimes, this calulation may return a decimal. Page numbers need to be whole numbers and therefore you need to create a whole number. Ceil() is used to round fractions up. If you round down, you could loose a page at the end.
Lets say you have 9 pages, and 5 records per page. This would result in 1.8 pages. The next program we look at only displays pages based on whole numbers, therefore because 1 is the only whole number, it would only display 1 page, and miss out the last 4 records. So using ceil() will change 1.8, to 2.0, and produce two pages.
Ok, so now we have the number of pages, lets look at the script to display the page selection. There are a number of ways you can do this, however, for simplicity, ill just create a list of all pages to select from.
$data['page_selection'] = "";
$data['number_of_pages'] = ceil($data['record_count']/$data['records_per_page);
for($i=1;$i<=$data['number_of_pages'];$i++);
{
if($i==$data['page'])
{
$data['page_selection'] .= "{$i}";
} else {
$data['page_selection'] .= "<a href="?page={$i}" target="_self">{$i}</a>";
}
if($i!=$data['number_of_pages'])
{
$data['page_selection'] .= " / ";
}
}
The for loop will start at page 1, and keep going until it reaches the total number of pages.
For each page ($i), it will perform the code below the loop.
There are two if conditions. The first one asks if the current page being processed ($i) is the same as the page requested by the user ($data['page']). If it is, it'll only display the page number.
However, if it is different, then it will display a link to the page.
The second condition asks if the current page being processed ($i) is different from the last page available. If is isnt the last page, it will add a forward slash seperator. This is just for aesthectics.
You can then display the link selection html, by echoing $data['page_selection'].
Thats the entire pagination program. For those that are interested in viewing the program as a whole, i've included the source code below. I've assumed you've already got an open connection to MySQL, therefore wont open one up in the source code.
// Define variables used
$data['page'] = isset($_GET['page']) ? $_GET['page'] : 1;
$data['records_per_page'] = isset($_GET['rpp']) ? $_GET['rpp'] : 20;
// Calculate how many records are being used
$recordSet = mysql_query("SELECT COUNT(uid) AS cid FROM tbl_clients");
while($row = mysql_fetch_array($recordSet))
{
$data['record_count'] = $row['cid'];
}
mysql_free_result($recordSet);
// SQL to grab data, from database
$data['start_on_record'] = ($data['records_per_page']*$data['page'])-$data['records_per_page'];
$data['record_display'] = "";
$recordSet = mysql_query("SELECT * FROM tbl_clients ORDER BY client_added DESC
LIMIT {$data['start_on_record']},{$data['records_per_page']}");
while($row = mysql_fetch_array($recordSet))
{
$data['record_display'] .= <<
<tr>
<td><a href="{$row['client_website']}" target="new">{$row['client_company']}</a></td>
</tr>
DISPLAY_RECORDS;
}
mysql_free_result($recordSet);
// Create page selection
$data['page_selection'] = "<tr>\n<td>";
$data['number_of_pages'] = ceil($data['record_count']/$data['records_per_page);
for($i=1;$i<=$data['number_of_pages'];$i++);
{
if($i==$data['page'])
{
$data['page_selection'] .= "{$i}";
} else {
$data['page_selection'] .= "<a href="?page={$i}" target="_self">{$i}</a>";
}
if($i!=$data['number_of_pages'])
{
$data['page_selection'] .= " / ";
}
}
$data['page_selection'] .= "</td>\n</tr>";
// Display All
echo $data['page_selection'].$data['record_display'];
|
 |
|
No comments have been posted!
|
|
Before you can comment, please Register or login.
|
|
|
|
