Paging with php mysql
Paging means showing your sql query result in multiple pages instead of just put them all in one long page. Imagine waiting for five minutes just to load a search page that shows 10000 result.
MySQL helps to generate paging by using LIMIT clause along with LIMIT you can use two variables one for OFFSET and another no of records you have to display in one page.
Paging can use in following steps.
Step 1: Make Database connection
$dbhost = 'localhost:3036'; your host name eg: localhost
$dbuser = 'root'; your database user id
$dbpass = 'rootpassword'; your database password
$rec_limit = 10; how many records per page
$conn = mysql_connect($dbhost, $dbuser, $dbpass); make MYSQL connection
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('test_db'); select database
Step 2: Find total no of records
$sql = "SELECT count(emp_id) FROM employee ";
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
$row = mysql_fetch_array($retval, MYSQL_NUM );
$rec_count = $row[0];
Step 3: arrange your page number
if( isset($_GET{'page'} ) )
{
$page = $_GET{'page'} + 1;
$offset = $rec_limit * $page ;
}
else
{
$page = 0;
$offset = 0;
}
$left_rec = $rec_count - ($page * $rec_limit);
Step 4: Fetch Records from Table
$sql = "SELECT emp_id, emp_name, emp_salary ".
"FROM employee ".
"LIMIT $offset, $rec_limit";
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "EMP ID :{$row['emp_id']}
".
"EMP NAME : {$row['emp_name']}
".
"EMP SALARY : {$row['emp_salary']}
".
"--------------------------------
";
}
Step 5: Use Paging
{
$last = $page - 2;
echo "<a href=\"$_PHP_SELF?page=$last\">Last 10 Records</a> |";
echo "<a href=\"$_PHP_SELF?page=$page\">Next 10 Records</a>";
}
else if( $page == 0 )
{
echo "<a href=\"$_PHP_SELF?page=$page\">Next 10 Records</a>";
}
else if( $left_rec < $rec_limit )
{
$last = $page - 2;
echo "<a href=\"$_PHP_SELF?page=$last\">Last 10 Records</a>";
}
mysql_close($conn);.