This is a very important task to reorder the table rows. In this tutorial, I will create an example with bootstrap 4. Where you can reorder the rows and save into the DB.
To save reordered data you need to create a table into the database (MySQL). Use the below query to create a table into DB with some records.
CREATE TABLE `reorderusers` (
`id` int(11) NOT NULL,
`userorder` int(11) NOT NULL,
`username` varchar(64) NOT NULL,
`useremail` varchar(128) NOT NULL,
`userphone` varchar(24) NOT NULL,
`usercountry` int(11) NOT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `reorderusers`
--
INSERT INTO `reorderusers` (`id`, `userorder`, `username`, `useremail`, `userphone`, `usercountry`, `dt`) VALUES
(1, 1, 'Jhon', '[email protected]', '0808254552', 23, '2019-10-26 14:47:31'),
(2, 2, 'Aslam', '[email protected]', '5400897855', 20, '2019-10-26 14:47:31'),
(3, 3, 'Kartik', '[email protected]', '1555420004', 50, '2019-10-26 14:47:31'),
(4, 4, 'Sena', '[email protected]', '0808254552', 74, '2019-10-26 14:47:31'),
(5, 5, 'Umha', '[email protected]', '3200558002', 5, '2019-10-26 14:47:31'),
(6, 6, 'Kmal', '[email protected]', '4653288000', 65, '2019-10-26 14:47:31'),
(7, 7, 'Ali', '[email protected]', '6598520010', 100, '2019-10-26 14:47:31'),
(8, 8, 'Jordan', '[email protected]', '8975421200', 100, '2019-10-26 14:47:31');
ALTER TABLE `reorderusers`
ADD PRIMARY KEY (`id`);
ALTER TABLE `reorderusers`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;
The table REORDERUSER will store the data of users. If you want to change the order you just need to drag and drop the user.
In this example, I am considering you are able to create CRUD of uses. If you are not able to do that follow the above post where you can learn how to create a simple CRUD.
How to Fetch the user’s data and render it into the bootstrap 4 tables.
PHP used to fetch and render users data into the Bootstrap 4 table. Before that, you need to create a connection with the database. Create a file with the name of the config.php and past below code init.
<?php
error_reporting(0);
define('DB_NAME', 'YOUR_DB_NAME'); //Your DB Name
define('DB_USER', 'root'); //Your DB User Name
define('DB_PASSWORD', ''); //Your DB Password
define('DB_HOST', 'localhost'); //Your Host Name
// Create connection
$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
?>
Add this file into the index.php file or your main header file. In this example, I am adding this to the index.php at the top then past below snippet just after the body tag.
<table class="table table-bordered table-striped">
<thead>
<tr>
<th width="20">Action</th>
<th>DateTime</th>
<th>User Name</th>
<th>User Email</th>
<th>User Phone#</th>
</tr>
</thead>
<tbody id="tb">
<?php
$result = $db->query("SELECT * FROM reorderusers WHERE 1 ORDER BY userorder ASC ");
if($result->num_rows>0){
while($val = $result->fetch_assoc()){ ?>
<tr>
<td align="center"><i class="fa fa-fw fa-arrows-alt"></i></td>
<td><?php echo $val['dt']; ?></td>
<td><?php echo mb_strtoupper($val['username'],'UTF-8'); ?></td>
<td><?php echo $val['useremail']; ?></td>
<td><?php echo $val['userphone']; ?></td>
</tr>
<?php
}
}else{ ?>
<tr>
<td colspan="5" class="bg-light text-center"><strong>No Record(s) Found!</strong></td>
</tr>
<?php } ?>
</tbody>
</table>
This example depends on the jQuery and jQuery UI plugin. So add the following lines into your head tag.
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.0/themes/smoothness/jquery-ui.css" type="text/css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://code.jquery.com/ui/1.12.0/jquery-ui.min.js" integrity="sha256-eGE6blurk5sHj+rmkfsGYeKyZx3M4bG+ZlFyA7Kns7E=" crossorigin="anonymous"></script>
To reorder the table rows I use JQuery UI sortable function. After reordering I send an ajax request to update the new sorted order for that consider below code. I use data-sort-id in <TR> tag to set rows ides. Whenever I update the order of the rows a comma-separated ids string will return. Then I am sending the AJAX request to update the new sorted order.
$('#sortable tbody').sortable({
handle: 'i.fa-arrows-alt',
placeholder: "ui-state-highlight",
//opacity: 0.9,
update : function () {
var order = $('#sortable tbody').sortable('toArray', { attribute: 'data-sort-id'});
console.log(order.join(','));
sortOrder = order.join(',');
$.post(
'action-form.ajax.php',
{'action':'updateSortedRows','sortOrder':sortOrder},
function(data){
var a = data.split('|***|');
if(a[1]=="update"){
$('#msg').html(a[0]);
}
}
);
}
});
$( "#sortable" ).disableSelection();
The PHP action-form.ajax.php file where I update the users according to the new sorted order. The below code will help you to understand how to update the new order.
if(isset($_REQUEST['action']) and $_REQUEST['action']=="updateSortedRows"){
$newOrder = explode(",",$_REQUEST['sortOrder']);
$n = '0';
foreach($newOrder as $id){
$db->query('UPDATE reorderusers SET userorder="'.$n.'" WHERE id="'.$id.'" ');
$n++;
}
echo '<div class="alert alert-success"><i class="fa fa-fw fa-thumbs-up"></i> Record updated successfully!</div>|***|update';
}