If you are reading this tutorial, I assume you already know how to make a MySQL database connection from PHP. In case you do not know how, here is the code?br />

1) MySQL Database Connection


<?php
//////////////////////////////////////////
//// MySQL Database Connection ///////////
//////////////////////////////////////////
$host = "yourhost";
$user = "yourusername";
$db_name= "yourdatabasename";
$pass= "yourpassword";

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db_name, $conn) or die(mysql_error());
?>



2) Get page number, if any

First, you need to obtain the page number you are in. If the user hasn抰 clicked in any of the page links then it will bring them to page one. Notice that the variable $pageno will only contain numeric characters assigned to it. In case someone is playing around with the URL or trying an injection attack, the code will exit and the code won抰 be executed.
<?php
/////////////////////////////////////////
////// Prevent Injection Attack /////////
////// Set $pageno variable /////////////
/////////////////////////////////////////
if(isset($_GET['pageno']))
{
    if(!is_numeric($_GET['pageno']))
    {
        echo 'Error.';
        exit();
    }
    $pageno = $_GET['pageno'];
}
else
{
    $pageno=1;
}
?>



3) Calculate how many rows or records in total the query will output

$numrows variable contains the total number of rows. If the query returns no results then you can output a customized notice.

If the query does return rows you now have the total number of rows at the $numrows variable. In case you want to display 揟here are 150 products in total? you can do it by echoing the $numrows variable. We will do this later in the exercise.

<?php
$queryCount = 'SELECT count(*) FROM products';
$resultCount = mysql_query($queryCount);
$fetch_row = mysql_fetch_row($resultCount);
$numrows = $fetch_row[0];

// if there is no results
if($numrows == 0)
{
    echo 'Sorry, we have no products yet.';
    exit();
}?>



4) Calculate number of pages

Now let抯 get the last page number. How many rows do you want per page? Just for the exercise purposes, I chose 4. Of course, you probably have dozens or hundreds of rows in your table, so you might want to chose 10, 15 or 20. Take your pick.


<?php
$perPage = 4;
$lastpage = ceil($numrows/$perPage);
$pageno = (int)$pageno;
if($pageno<1)
{
    $pageno=1;
}
elseif($pageno>$lastpage)
{
    $pageno=$lastpage;
}
?>



5) Generate page links

We are going to work on the links. There will be 4:
?FIRST
?PREVIOUS
?NEXT
?LAST

The links that do not apply will be grayed out. For example if you are in page one, the FIRST link will be grayed out. Or if you are in the last page, then the LAST link will be grayed out. Also, this script will let us know what page we are in.


<?php
// ----- PAGE LINKS -----
if($pageno==1)
{
    $pages .= 'FIRST | PREVIOUS ';
}
else
{
    $pages .= "<a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> | ";
    $prevpage=$pageno-1;
    $pages .= " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREVIOUS</a> ";
}
$pages .= ' ( Page '.$pageno.' of '.$lastpage.' ) ';
if($pageno==$lastpage)
{
    $pages .= ' NEXT | LAST ';
}
else
{
    $nextpage = $pageno+1;
    $pages .= " <a href='".$_SERVER['PHP_SELF']."?pageno=$nextpage'>NEXT</a> | ";
    $pages .= " <a href='".$_SERVER['PHP_SELF']."?pageno=$lastpage'>LAST</a>";
}
?>



6) Calculate LIMIT clause for the MySQL query

The range of query results are loaded into variable $limit so we add it to the query and populate its results. Then we display the page links and the total number of products.


<?php
$limit=' LIMIT '.($pageno-1)*$perPage.', '.$perPage;
?>



7) Run the query, echo the results and echo the page links


<?php
$query  = $query.$limit;
$result = mysql_query($query);
if(!$result)
{
    echo 'Query failed: '.mysql_error();
}
while($row = mysql_fetch_array($result))
{
    echo $row['id'].' '.$row['name'].'<br />';
}

echo '<div style="width:100%; text-align: center; font-size: smaller; color: #999;">'.$pages.'</div>'
echo 'Total number of products: '.$numrows;
?>



Hope you liked the code.

discuss this topic to forum