Reorder Bootstrap table rows with help of JQuery,Ajax,PHP and MySql
Reorder table Rows is a important task. We will create an example with bootstrap.Where we can reorder rows and save into Database Table.
First Create table In Database(MySql) and Save some records.Use Below query to create a table in Database with some records.
--
-- Table structure for table `sortable`
--
CREATE TABLE `sortable` (
`blog_id` int(11) NOT NULL,
`blog_title` varchar(100) NOT NULL,
`blog_desc` text NOT NULL,
`blog_order` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `sortable`
--
INSERT INTO `sortable` (`blog_id`, `blog_title`, `blog_desc`, `blog_order`) VALUES
(1, 'HTML', 'HTML is the standard markup language for Web pages.IN HTML you can create your own Website.', 1),
(2, 'CSS', 'CSS is the language we use to style an HTML document.CSS describes how HTML elements should be displayed.', 2),
(3, 'JavaScript', 'JavaScript is the world\'s most popular programming language. JavaScript is the programming language of the Web. JavaScript is easy to learn.This tutorial will teach you JavaScript from basic to advanced.', 3),
(4, 'SQL', 'SQL is a standard language for storing, manipulating and retrieving data in databases.', 4),
(5, 'PHP', 'PHP is an acronym for \"PHP: Hypertext Pre processor\". PHP is a widely-used, open source scripting language. PHP scripts are executed on the server. PHP is free to download and use', 5);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `sortable`
--
ALTER TABLE `sortable`
ADD PRIMARY KEY (`blog_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `sortable`
--
ALTER TABLE `sortable`
MODIFY `blog_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
COMMIT;
Now we create a connection with the database. Create a file with the name of the connection.php and Use below Code.
<?php
error_reporting(0);
$mysql_hostname = "localhost"; //Your Host Name
$mysql_user = "root"; //Your DB User Name
$mysql_password = ""; //Your DB Password
$mysql_database = "YOUR_DB_NAME"; # Here your Database name
$bd = ($GLOBALS["___mysqli_ston"] = mysqli_connect($mysql_hostname,$mysql_user, $mysql_password))
or
die("Opps some thing went wrong");
mysqli_select_db( $bd, $mysql_database) or die("wrong");
?>
Now Create a php file to render MySql Table Data into Bootstrap table for Reorder Rows. Create File name sortable.php .Use Below Code
<?php include_once('connection.php'); ?>
<!DOCTYPE html>
<html>
<head>
<title>Reorder Bootstrap table rows with help of JQuery,Ajax,PHP and MySql</title>
<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.0/themes/smoothness/jquery-ui.css" type="text/css">
<script src="https://code.jquery.com/ui/1.12.0/jquery-ui.min.js"></script>
</head>
<body>
<div class="container my-4">
<h3 align="center">Bootstrap 4 Table(DataTable) Rows Reorder with using PHP MySqli Ajax jQuery</h3>
<br />
<div class="card">
<div class="card-header">Bootstrap 4 Table(DataTable) Rows Reorder with using PHP MySqli Ajax jQuery</div>
<div class="card-body">
<div class="table-resposive">
<table class="table table-striped table-bordered">
<thead><tr><th>#</th><th>Blog Title</th><th>Blog Description</th><th>Order</th></tr></thead>
<tbody>
<?php
$i = 0;
$query = "SELECT * FROM sortable";
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);
$num_result = mysqli_num_rows($result);
if($num_result > 0)
{
while($row = mysqli_fetch_array($result))
{
?>
<tr id = "<?php echo $row['blog_id'];?>">
<td><?php echo $i + 1;?></td>
<td><?php echo $row['blog_title'];?></td>
<td><?php echo $row['blog_desc'];?></td>
<td><?php echo $row['blog_order'];?></td>
</tr>
<?php
$i++;
}
}
?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
<style>
tbody {
cursor: all-scroll;
}
To reorder the table rows we use JQuery UI sortable function. After reordering we send an ajax request to update the new sorted order for that consider below code. Use below code end of the sortable.php file
$(document).ready(function(){
$('tbody').sortable({
update : function(event, ui)
{
var blog_id = new Array();
$('tbody tr').each(function(){
blog_id.push($(this).attr('id'));
});
$.ajax({
url:"reorder.php",
method:"POST",
data:{blog_id:blog_id},
success:function()
{
location.reload();
}
})
}
});
});
The PHP reorder.php file where we update the records according to the new sorted order. The below code will help you to understand how to update the new order.
<?php
include_once('connection.php');
if (!empty($_POST["blog_id"]))
{
$blogs = $_POST["blog_id"];
for($i = 0; $i < count($blogs); $i++)
{
$order = $i + 1;
$query = "UPDATE sortable SET blog_order = '".$order."' WHERE blog_id = '".$blogs[$i]."'";
mysqli_query($GLOBALS["___mysqli_ston"], $query);
}
}
?>