Reorder Bootstrap 4 table rows with PHP, JQuery – Parte 1

9 de março de 20239 de março de 2023 admin 0 comentários

Reorder Bootstrap 4 table rows with PHP, JQuery

Demo Online

3 years ago
Zaid Bin Khalid
14060 Views
12

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', 'jhon@gmail.com', '0808254552', 23, '2019-10-26 14:47:31'),
(2, 2, 'Aslam', 'aslam@yahoo.com', '5400897855', 20, '2019-10-26 14:47:31'),
(3, 3, 'Kartik', 'kartik@gmail.com', '1555420004', 50, '2019-10-26 14:47:31'),
(4, 4, 'Sena', 'sena@gmail.com', '0808254552', 74, '2019-10-26 14:47:31'),
(5, 5, 'Umha', 'umha@yahoo.com', '3200558002', 5, '2019-10-26 14:47:31'),
(6, 6, 'Kmal', 'kmal@gmail.com', '4653288000', 65, '2019-10-26 14:47:31'),
(7, 7, 'Ali', 'ali@hotmal.com', '6598520010', 100, '2019-10-26 14:47:31'),
(8, 8, 'Jordan', 'jordan@gmail.com', '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.

PHP CRUD with Search and Pagination in Bootstrap 4

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';
}