+PC9kaXY+PGRpdiBjbGFzcz0ib21lZ2EgZ3JpZF83IiBpZD0iaGVhZGVyX3JpZ2h0Ij4KPHVsPgo8bGk+PGEgaHJlZj0iaHR0cHM6Ly92cHNkaW1lLmNvbS8iPlZQUyBXZWIgSG9zdGluZzwvYT48L2xpPgo8L3VsPg==

Paginating MySQL Results With PHP

Although it sounds like something that happens in dark alley ways pagination is actually a useful thing that is often used on web sites to split results up. Pagination is when a number of results are spread over a few pages, mainly to keep loading times to a reasonable level, but also to make the page look less cluttered.

We are going to assume you already know how to display the data on the screen, maybe after reading our other tutorial.

Ok, enough of the filler text, grab yourself a beer….errr i mean notepad. First things first, you need to grab your data from the database. In this case we are using a MySQL database but it really could be any data source.

Connect to your database

define ('DB_USER', 'me');
define ('DB_PASSWORD', 'mypass');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'my_db');
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME);

Fill in the gaps as necessary.

As a side note, it is worth saving this small piece of code to a separate file, let’s call it connect.php and just including it at the top of all your scripts using:

include('connect.php');

Query the database

Yay we are connected to our database, but we haven’t really done anything or achieved any results. After we have connected to the database we need to actually do something with it, we want to grab some data out of tables within the database.

Usually you would grab all the data from the database that you need, maybe using a ‘WHERE’ clause.

$query = mysql_query('select * from my_table WHERE id=1');
while($row = mysql_fetch_array($query))
{
//blah blah blah...
echo $row[0];
//blah blah blah..
}

This is all very well but the code above will return every relevant result and spit them all out onto our page…every last one of them, there could be thousands!

Limit the results

The key to pagination is to alter the query and add ‘LIMITS’ to it. We can set a start point and a limit within a MySQL query. The start point is the row number where MySQL starts to grab results from and the limit is the number of returned results. Everybody loves an example, so heres an example!

$query = mysql_query('select * from my_table WHERE id=1 LIMIT 100, 10');
while($row = mysql_fetch_array($query))
{
//blah blah blah...
echo $row[0];
//blah blah blah..
}

The code above will limit the number of results to 10 and will start to grab results at row 100. Simple.

Paginating the results

So we know how to limit the number of results and how to start retrieving results from any row in the database. It may be time for a tea/coffee/beer, This is where it gets a little more complicated.

Paginating your MySQL results

In order to split the results we need to know a few variables. We need to know the following:

  • The total number of results
  • The number of results per page
  • The current page number being viewed

Given these three variables we can calculate what numbers we need to put into our ‘LIMITS’ in order to grab the correct results for the current page and the correct number of results for the current page as well as the total number of pages to list. First we set the number of results we want on each page:

$totalperpage = 10;

Next we need to grab the total number of results:

$totalresults = mysql_num_rows(mysql_query(''select * from my_table WHERE id=1'));

How do you imagine we could find the total number of pages we need? Thats right, we divide the total number of results by the results per page:

$totalpages = round($totalresults / $totalperpage);

The ’round’ simply rounds the number up to the nearest whole number.

Now we have our total number of pages we can move on to creating our page numbers. The page numbers are essentially just a group of links, linking to each page of results. We create this group by implementing a loop and using our ‘totalpages’ as the limit. Each link sends the page number as a variable called ‘page’. Sounds simple, but there is a minor snag. MySQL results start at zero rather than one, so we need page one to start at row zero….How confusing that must sound, onto the code!

for($i = 0; $i <= $totalpages; $i++)
{
$j = $i + 1;
echo "
<a href=\"mypage.com/results.php?page=$i\"$j</a>";
}

This loop will list the required number of pages, a link to each page of results. Note that each page is actually been sent a number that is one lower than the page. As described above, this is because MySQL starts searching at row zero, not one .

Catch that page number!

So the list of page numbers link back to the current page and pass the required page number to the page. At the top of the pages code somewhere  we need to grab the page number and set up the MySQL query:

$page = $_GET['page'];
if($page >= 1){ $start = (10 * $page) - 1; }
else{ $start = 0; }
$fetch = mysql_query('select * FROM my_table WHERE id=1 LIMIT $start, 10');

And thats it! The MySQL query is used to get whatever it is you are getting. See the complete basic code below:

//get page number, you will probably want to grab other variables as well here but for the sake of this we have left them off
$page = $_GET['page'];

//this if clause is necessary because otherwise we would miss rows of the database out of our results
if($page >= 1){ $start = (10 * $page) - 1; }
else{ $start = 0; }
//you are likely to want extra variable here as well, and pass them to the query
$fetch = mysql_query('select * FROM my_table WHERE id=1 LIMIT $start, 10');
//get the results, relevant to this page
while($row = mysql_fetch_array($fetch))
{
//display your results here!!
}
//Show page numbers, you may wish to show them in a list or something, so you can style them
$totalperpage = 10;

$totalresults = mysql_num_rows(mysql_query(''select * from my_table WHERE id=1'));
$totalpages = round($totalresults / $totalperpage);
for($i = 0; $i <= $totalpages; $i++)
{
$j = $i + 1;
echo "<a href=\"mypage.com/results.php?page=$i\"$j</a> ";
}
//if you want to add 'Previous' and 'Next' links then you simply add or minus one off the current page you are on
//you would also need to add in some kind of clause to check if you are on the first or last page

February 10th, 2011

Definitions

Collocated Hosting

Collocation means “co-location”. Collocated hosting lets you place your own web server on the premises (locations) of a service provider.

This is pretty much the same as running your own server in your own office, only that it is located at a place better designed for it.

Most likely an ISP will have dedicated resources like high-security against fire and vandalism, regulated backup power, dedicated Internet connections and more.

Free Hosting

…Some ISPs offer free web hosting.

Free web hosting is best suited for small sites with low traffic, like personal sites. It is not recommended for high traffic or for real business. Technical support is often limited, and technical options are few.

Very often you cannot use your own domain name at a free site. You have to use a name provided by your host like http://www.freesite.com/users/~yoursite.htm. This is hard to type, hard to remember, and not very professional…

Directory of best web hosting resources!

Apache Help MySQL Tutorials and help pages PHP Tutorials And Help