While creating the pagination it is required to set how many records
to fetch at a time from the Database Table. It helps to paginate all
records.
You can allow the user to change the defined number of rows visible at a time on the page by providing them an extra option.
Loop on the result and create
Create
In the demonstration, I created a pagination layout where also create a dropdown element for modifying the value of the total number of rows to fetch.
If you found this tutorial helpful then don't forget to share.
1. Table structure
I am usingemployee table in the demonstration.CREATE TABLE `employee` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(100) NOT NULL, `salary` varchar(20) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Configuration
Create a newconfig.php file for a database connection.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. HTML and PHP
Create two variable$row and $rowperpage.- $row – Current row position. Set value to 0.
- $rowperpage – Number of rows to fetch from the MySQL table. Set value to 5.
employee table and store it in a $allcount variable. Fetch records from the employee using $row and $rowperpage value.Loop on the result and create
<table> rows.Create
<form> element which has pagination control –- Two hidden elements to store
$rowand$allcountvalues. - Previous and Next button for navigation.
- A
<select>element to change number of row fetch at a time by changing$rowperpagevalue on<form>submit.
Create an array to make <option> list.
Event Handling
- Next Button
Storing
$_POST['row'] and $_POST['allcount'] in the variables and creating another variable $val which store the sum of $row and $rowperpage.
When
$val value is less than $allcount then initializing $row with $val.- Previous Button
Subtract
$_POST['row'] with $rowperpage and check if it is less than 0 or not. If it is then initialized $row with 0.- Row selection From Dropdown
Change value of
$rowperpage with $_POST['num_rows'] when the form submitted.
Completed Code:-
<!doctype html>
<html>
<head>
<title>Change number of records displayed in Pagination using PHP</title>
<link href="style.css" type="text/css" rel="stylesheet">
<script src="jquery-3.3.1.min.js" type="text/javascript"></script>
<?php
include("config.php");
$row = 0;
// number of rows per page
$rowperpage = 5;
if(isset($_POST['num_rows'])){
$rowperpage = $_POST['num_rows'];
}
// Previous Button
if(isset($_POST['but_prev'])){
$row = $_POST['row'];
$row -= $rowperpage;
if( $row < 0 ){
$row = 0;
}
}
// Next Button
if(isset($_POST['but_next'])){
$row = $_POST['row'];
$allcount = $_POST['allcount'];
$val = $row + $rowperpage;
if( $val < $allcount ){
$row = $val;
}
}
?>
</head>
<body>
<div class="container">
<table width="100%" id="emp_table" border="0">
<tr class="tr_header">
<th>S.no</th>
<th>Name</th>
<th>Salary</th>
</tr>
<?php
// count total number of rows
$sql = "SELECT COUNT(*) AS cntrows FROM employee";
$result = mysqli_query($con,$sql);
$fetchresult = mysqli_fetch_array($result);
$allcount = $fetchresult['cntrows'];
// selecting rows
$sql = "SELECT * FROM employee ORDER BY ID ASC limit $row,".$rowperpage;
$result = mysqli_query($con,$sql);
$sno = $row + 1;
while($fetch = mysqli_fetch_array($result)){
$name = $fetch['emp_name'];
$salary = $fetch['salary'];
?>
<tr>
<td align='center'><?php echo $sno; ?></td>
<td align='center'><?php echo $name; ?></td>
<td align='center'><?php echo $salary; ?></td>
</tr>
<?php
$sno ++;
}
?>
</table>
<!-- Pagination control -->
<form method="post" action="" id="form">
<div id="div_pagination">
<input type="hidden" name="row" value="<?php echo $row; ?>">
<input type="hidden" name="allcount" value="<?php echo $allcount; ?>">
<input type="submit" class="button" name="but_prev" value="Previous">
<input type="submit" class="button" name="but_next" value="Next">
<!-- Number of rows -->
<div class="divnum_rows">
<span class="paginationtextfield">Number of rows:</span>
<select id="num_rows" name="num_rows">
<?php
$numrows_arr = array("5","10","25","50","100","250");
foreach($numrows_arr as $nrow){
if(isset($_POST['num_rows']) && $_POST['num_rows'] == $nrow){
echo '<option value="'.$nrow.'" selected="selected">'.$nrow.'</option>';
}else{
echo '<option value="'.$nrow.'">'.$nrow.'</option>';
}
}
?>
</select>
</div>
</div>
</form>
</div>
</body>
</html>
4. jQuery
Submit the<form> with submit() function when value changes in the <select id='num_rows'> element.
Completed Code
$(document).ready(function(){
// Number of rows selection
$("#num_rows").change(function(){
// Submitting form
$("#form").submit();
});
});
5. Conclusion
It gives flexibility to the users to change the number of rows visible at a time on the page.In the demonstration, I created a pagination layout where also create a dropdown element for modifying the value of the total number of rows to fetch.
If you found this tutorial helpful then don't forget to share.
0 Comments:
Post a Comment