How to display only 5 records per page from my mysql database table through pagination?
19,446
Solution 1
IF your are using mysqli the code is below
$conn=mysqli_connect("localhost","root","","ui");
$start=0;
$limit=5;
$t=mysqli_query($conn,"select * from form_table");
$total=mysqli_num_rows($t);
if(isset($_GET['id']))
{
$id=$_GET['id'] ;
$start=($id-1)*$limit;
}
else
{
$id=1;
}
$page=ceil($total/$limit);
$query=mysqli_query($conn,"select * from form_table limit $start, $limit");
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script s src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"> </script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"> </script>
</head>
<body>
<div class="container">
<h2>Table</h2>
<table class="table table-bordered">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Gender</th>
<th>Hobbies</th>
<th>Course</th>
</tr>
</thead>
<tbody>
<?php
while($ft=mysqli_fetch_array($query))
{?>
<tr>
<td><?= $ft['0']?></td>
<td><?= $ft['1']?></td>
<td><?= $ft['2']?></td>
<td><?= $ft['3']?></td>
<td><?= $ft['4']?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<ul class="pagination">
<?php if($id > 1) {?> <li><a href="?id=<?php echo ($id-1) ? >">Previous</a></li><?php }?>
<?php
for($i=1;$i <= $page;$i++){
?>
<li><a href="?id=<?php echo $i ?>"><?php echo $i;?></a></li>
<?php
}
?>
<?php if($id!=$page)
{?>
Solution 2
If you want just PHP MySQL code, I usually use something like the following.
$page=max(intval($_GET['page']),1); // assuming there is a parameter 'page'
$itemsperpage = 5;
$total=100; // total results if you know it already otherwise use another query
$totalpages = max(ceil($total/$itemsperpage),1);
$query = "SELECT * FROM emp_master LIMIT ".(($page-1)*$itemsperpage).",".$itemsperpage; // this will return 5 items based on the page
Author by
Admin
Updated on August 06, 2022Comments
-
Admin over 1 year
I want to display five record per page through pagination (mysql,php,html,css) until all the records are displayed, navigation to pages must be like, Page: 1 2 3 4 5 6 7 7 8... Last.
HERE IS MY CODE TO VIEW ALL THE RECORDS FROM emp_master table. I am new to PHP so please write an easily understandable code for pagination. I have seen few examples but they are not working.
<?php $con=mysqli_connect("localhost","user","password","dataplus"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $result = mysqli_query($con,"SELECT * FROM emp_master"); echo "<table border='1'>"; $i = 0; while($row = $result->fetch_assoc()) { if ($i == 0) { $i++; echo "<tr>"; foreach ($row as $key => $value) { echo "<th>" . $key . "</th>"; } echo "</tr>"; } echo "<tr>"; foreach ($row as $value) { echo "<td>" . $value . "</td>"; } echo "</tr>"; } echo "</table>"; mysqli_close($con);
I want to display five record per page through pagination untill all the records are displayed, navigation to pages must be like, Page: 1 2 3 4 5 6 7 7 8... Last.
This code below is not working:
$dbhost="localhost"; $dbuser="10053"; $dbpass="n6867242"; $database="0368"; $rec_limit = 10; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } mysql_select_db('1005368'); /* Get total number of records */ $sql = "SELECT count(emp_id) FROM emp_master "; $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, e_mail ". "FROM emp_master ". "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']} <br> ". "EMP NAME : {$row['emp_name']} <br> ". "EMP MAIL : {$row['e_mail']} <br> ". "--------------------------------<br>"; } if( $page > 0 ) { $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);