Drag and Drop Sortable Table Using Jquery Ajax PHP Mysql -- ItErrorSolution.come

Drag and Drop Sortable Table Using Jquery Ajax PHP Mysql

May 06, 2024 | PHP jQuery MySql Bootstrap Ajax

Hello Dev,

Dynamic sorting, especially through drag and drop functionality, greatly enhances user experience and comprehension of data flow. Implementing drag and drop functionality for table rows or div elements can significantly improve the usability of your product.

In this post, I'll guide you through creating drag and drop functionality for table rows using jQuery UI and PHP to make it dynamic. We'll also utilize Bootstrap to enhance the layout. For convenience, we'll include jQuery UI via CDN, so there's no need to download and host it locally.

First, we'll set up a "sorting_items" table in our database, containing columns for id, title, description, and position_order. This will allow us to manage the sorting directly on one page using AJAX requests.

Following a few straightforward steps, we'll achieve a layout similar to the screenshot provided below. Additionally, I'll provide a demo and the full script for download for your convenience.

Let me know if you need further clarification or assistance with any part of the process!

Creating a drag-and-drop sortable table with jQuery UI and integrating it with PHP for dynamic data management is a powerful feature that enhances user interaction and data organization. Below is a step-by-step guide to achieve this, including setting up the database, creating the HTML structure, and implementing the JavaScript logic for drag-and-drop functionality.

Step 1: Set Up the Database

First, create a database and a table named sorting_items with the following columns: id, title, description, and position_order.

CREATE TABLE `sorting_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `position_order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Read Also: PHP JQuery Chosen Populate Ajax Autocomplete Example Tutorial Step 2: Create the HTML Structure

Create an HTML file (e.g., index.html) and include Bootstrap, jQuery, jQuery UI, and other necessary CSS files. Then, set up the table structure with draggable rows.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Drag and Drop Sortable Table Using Jquery Ajax PHP Mysql -- ItErrorSolution.come</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.11.6/dist/umd/popper.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container mt-5">
        <table id="sortableTable" class="table table-bordered">
            <thead>
                <tr>
                    <th>Title</th>
                    <th>Description</th>
                    <th>Position Order</th>
                </tr>
            </thead>
            <tbody>
           <?php

            require('db_config.php');

            $sql = "SELECT * FROM sorting_items ORDER BY position_order";
            $users = $mysqli->query($sql);
            while($user = $users->fetch_assoc()){

            ?>
                <tr  id="<?php echo $user['id'] ?>">
                    <td><?php echo $user['title'] ?></td>
                    <td><?php echo $user['description'] ?></td>
                    <td><?php echo $user['position_order'] ?></td>
                </tr>
            <?php } ?>
            </tbody>
        </table>
    </div>

    <script>
        $(function() {
            $("#sortableTable tbody").sortable({
                update: function(event, ui) {
                    var newPosition = $(ui.item).attr('data-position');
                    var itemId = $(ui.item).attr('data-id');

                    $.ajax({
                        url: 'save_position.php',
                        type: 'POST',
                        data: {id: itemId, position: newPosition},
                        success: function(response) {
                            console.log(response);
                        }
                    });
                }
            }).disableSelection();
        });
    </script>
</body>
</html>

Step 3: Implement the Drag-and-Drop Logic

The above HTML structure includes a script that initializes the table rows as sortable. When a row is moved, it sends an AJAX request to save_position.php to update the position_order in the database.

Read Also: Laravel 11 Create Database Seeder Command Example Tutorial Step 4: Create the PHP Script to Save Position

Create a PHP file named save_position.php to handle the AJAX request. This script updates the position_order in the database based on the new position of the row.

save_position.php

<?php
// Database connection details
$host = "localhost";
$user = "your_username";
$pass = "your_password";
$db = "your_database";

// Create connection
$conn = new mysqli($host, $user, $pass, $db);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: ". $conn->connect_error);
}

// Get the ID and new position from the AJAX request
$id = $_POST['id'];
$newPosition = $_POST['position'];

// Update the position_order in the database
$sql = "UPDATE sorting_items SET position_order = $newPosition WHERE id = $id";
if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: ". $conn->error;
}

$conn->close();
?>

Step 5: Populate the Table Dynamically

To populate the table dynamically, you can fetch data from the sorting_items table and insert it into the table body. This can be done either on page load or through another AJAX call based on your application's requirements.

This setup provides a basic implementation of a drag-and-drop sortable table with jQuery UI and PHP. You can further enhance this by adding features like editing, deleting, and more complex sorting logic.

Drag and Drop Sortable Table Using Jquery Ajax PHP Mysql

Thank you for your encouragement! If you have any questions or need further assistance, feel free to ask. I'm here to help!

Tags :

#PHP

#jQuery

#MySql

#Bootstrap

#Ajax

ItErrorSolution.com

"Hey there! I'm a full-stack developer and proud owner of ItErrorSolution.com, based right here in India. I love nothing more than sharing handy tips and tricks with my fellow developers through easy-to-follow tutorials. When it comes to coding, I'm all about PHP, Laravel, Angular, Vue, Node, JavaScript, jQuery, CodeIgniter, and Bootstrap – been hooked on them forever! I'm all about putting in the work and staying committed. Ready to join me on this journey to coding?"