how to add pagination in database table ?

Hi Dip, Try below two example,

First one:

<html>
<head>
<title>Paging Using PHPtitle>
head>
<body>
php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$rec_limit = 10;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('test_db');
/* Get total number 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];
if( isset($_GET{'page'} ) ) {
$page = $_GET{'page'} + 1;
$offset = $rec_limit * $page ;
}else {
$page = 0;
$offset = 0;
}
$left_rec = $rec_count - ($page * $rec_limit);
$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']} ".
"-------------------------------- ";
}
if( $page > 0 ) {
$last = $page - 2;
echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Recordsa> |";
echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Recordsa>";
}else if( $page == 0 ) {
echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Recordsa>";
}else if( $left_rec < $rec_limit ) {
$last = $page - 2;
echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Recordsa>";
}
mysql_close($conn);
?>
body>
html>

Second one:

"bootstrap3.css" rel="stylesheet">
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = ";
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'test';
$conn = mysqli_connect($dbhost, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$limit = 2;
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * $limit;
$sql = "SELECT * FROM posts ORDER BY title ASC LIMIT $start_from, $limit";
$rs_result = mysqli_query($conn, $sql);
?>
class="table table-bordered table-striped">
title
body
while ($row = mysql_fetch_assoc($rs_result)) {
?>
echo $row["title"]; ?>
echo $row["body"]; ?>
};
?>
$sql = "SELECT COUNT(id) FROM posts";
$rs_result = mysqli_query($conn, $sql);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / $limit);
$pagLink = "";
for ($i=1; $i<=$total_pages; $i++) {
$pagLink .= ".$i."'>".$i."";
};
echo $pagLink . "

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar7y ago
Hi Dip, Try below two example,
First one:
<html>

 <head>
 <title>Paging Using PHPtitle>
 head>

 <body>
 php
 $dbhost = 'localhost:3036';
 $dbuser = 'root';
 $dbpass = 'rootpassword';

 $rec_limit = 10;
 $conn = mysql_connect($dbhost, $dbuser, $dbpass);

 if(! $conn ) {
 die('Could not connect: ' . mysql_error());
 }
 mysql_select_db('test_db');

 /* Get total number 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];

 if( isset($_GET{'page'} ) ) {
 $page = $_GET{'page'} + 1;
 $offset = $rec_limit * $page ;
 }else {
 $page = 0;
 $offset = 0;
 }

 $left_rec = $rec_count - ($page * $rec_limit);
 $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']} ".
 "-------------------------------- ";
 }

 if( $page > 0 ) {
 $last = $page - 2;
 echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Recordsa> |";
 echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Recordsa>";
 }else if( $page == 0 ) {
 echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Recordsa>";
 }else if( $left_rec < $rec_limit ) {
 $last = $page - 2;
 echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Recordsa>";
 }

 mysql_close($conn);
 ?>

 body>
html>
Second one:
"bootstrap3.css" rel="stylesheet">
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = ";
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'test';
$conn = mysqli_connect($dbhost, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
 /* check connection */
 if (mysqli_connect_errno()) {
 printf("Connect failed: %s\n", mysqli_connect_error());
 exit();
 }

$limit = 2;
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * $limit;

$sql = "SELECT * FROM posts ORDER BY title ASC LIMIT $start_from, $limit";
$rs_result = mysqli_query($conn, $sql);
?>
class="table table-bordered table-striped">


title
body



while ($row = mysql_fetch_assoc($rs_result)) {
?>

 echo $row["title"]; ?>
 echo $row["body"]; ?>

};
?>


$sql = "SELECT COUNT(id) FROM posts";
$rs_result = mysqli_query($conn, $sql);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / $limit);
$pagLink = "";
for ($i=1; $i<=$total_pages; $i++) {
 $pagLink .= ".$i."'>".$i."";
};
echo $pagLink . "
";
?>