Well below i have attached the table structure kindly go through it.
I would like to get serial no as my 1st column followed by id and name. serial no should continue for pagination rather then starting from 1st. Thanks in advance..
Table structure is
create table departments (
id INT(20) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL);
<html>
<head>
<?php
$db_connection = new mysqli("localhost","root","","emp_app");
if($db_connection->connect_error)
die("connection failed".$db_connection->connect_error);
?>
</head>
<body>
<table>
<tr>
<th>Serial no </th>
<th>id</th>
<th>name</th>
</tr>
<?php
$sql_query = "select * from departments";
$result = $db_connection->query($sql_query);
if($result->num_rows > 0){
while($rows = $result->fetch_assoc()){
echo "<tr>";
echo "<td>".$rows["id"]."<td>";
echo "<td>".$rows["name"]."<td>";
echo "<tr>";
}
}
?>
</table>
</body>
here is a good example how to add pagination in php pagination in php Refrence link is using depricated mysql query you need to replace with mysqli query
$num_rec_per_page=10;
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * $num_rec_per_page;
$sql = "SELECT * FROM departments LIMIT $start_from, $num_rec_per_page";
$rs_result = $db_connection->query ($sql); //run the query
$serial=1;
while ($row = $result->fetch_assoc) {
//code here
echo $serial;
$serial++;
};
$sql = "SELECT * FROM departments"; //select query for total records
$rs_result = $db_connection->query($sql); //run the query
$total_records =$rs_result->num_rows; //count number of records
$total_pages = ceil($total_records / $num_rec_per_page);
echo "<a href='pagination.php?page=1'>".'|<'."</a> "; // Goto 1st page
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='pagination.php?page=".$i."'>".$i."</a> ";
};
echo "<a href='pagination.php?page=$total_pages'>".'>|'."</a> "; // Goto last page
?>
You could append the URL to add a get variable that will serve as your pagination number i.e. youurl.com/?pagination=1 or 2 or 3
Then in your code add
$pagenumber = $_GET["pagination"];
$offset = $pagenumber * 5; //The number 5 is how many results per page if you wanted 10 results per page this would be 10
Where you have
$sql_query = "select * from departments";
You can change to
$sql_query = "select * from departments" LIMIT 5 OFFSET '$offset';
Which is basically saying; only give me 5 results starting from the pagination row (which is 5 results x page 2 or 3
<html>
<head>
<?php
$db_connection = new mysqli("localhost","root","","emp_app");
if($db_connection->connect_error)
die("connection failed".$db_connection->connect_error);
$num_rec_per_page=5;
if (isset($_GET["page"])) {
$page = $_GET["page"];
} else {
$page=1;
};
$start_from = ($page-1) * $num_rec_per_page;
?>
</head>
<body>
<table>
<tr>
<th>Serial no </th>
<th>id</th>
<th>name</th>
</tr>
<?php
$sql_query = "SELECT * FROM departments LIMIT $start_from, $num_rec_per_page";
$result = $db_connection->query($sql_query);
if($result->num_rows > 0){
while($rows = $result->fetch_assoc()){
echo "<tr>";
echo "<td>".$rows["id"]."<td>";
echo "<td>".$rows["name"]."<td>";
echo "<tr>";
}
}
$sql = "SELECT * FROM departments"; //select query for total records
$rs_result = $db_connection->query($sql); //run the query
$total_records =$rs_result->num_rows; //count number of records
$total_pages = ceil($total_records / $num_rec_per_page);
echo "<a href='index.php?page=1'>".'|<'."</a> "; // Goto 1st page
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='index.php?page=".$i."'>".$i."</a> ";
};
echo "<a href='index.php?page=$total_pages'>".'>|'."</a> ";
?>
</table>
</body>