Reading Time: 8 minutes

1,343 Views

Inside this article we will see the concept i.e PHP & MySQLi Drag and Drop Reorder Items with jQuery Tutorial. Article contains the classified information about Sorting MySQL Row Order using jQuery.

If you are looking for a solution i.e Drag & Drop Reorder Items in PHP then this article will help you a lot for this. Tutorial is super easy to understand and implement it in your code as well.

We will see drag, drop and sort concept using PHP, MySQLi & jQuery UI. We will use sorting function of jquery ui.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Table we need – items

CREATE TABLE `items` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `item_name` varchar(50) NOT NULL,
 `priority` int(5) NOT NULL DEFAULT '0',
 `status` enum('1','0') NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This code will create items table.

Let’s insert some Test data into this table.

Execute this sql query into database for fake rows.


INSERT INTO `items` (`id`, `item_name`, `priority`, `status`) VALUES
(1, 'Product 1', 0, '1'),
(2, 'Product 2', 0, '1'),
(3, 'Product 3', 0, '1'),
(4, 'Product 4', 0, '1'),
(5, 'Product 5', 0, '1');

Application Folder Structure

You need to create a folder structure to develop this application in PHP and MySQLi. Have a look the files inside this application –

Create a folder with name php-mysqli-sorting and create these 3 files into it – dbconfig.php and index.php, update_order.php.

Database Configuration

Open dbconfig.php file from folder. Add these lines of code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi Drag and Drop Reorder Items with jQuery
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

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

Application Programming

Open index.php file and write this following code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi Drag and Drop Reorder Items with jQuery
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Include the database file
require 'dbconfig.php';

$query = "SELECT id, item_name FROM items ORDER BY priority";

$statement = $conn->prepare($query);
$statement->execute();
$result = $statement->get_result();

$items = array();

while ($item = $result->fetch_assoc()) {
    $items[] = $item;
}

?>

<!doctype html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>PHP & MySQLi Drag and Drop Reorder Items with jQuery</title>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">
    <style>
        #sortable {
            list-style-type: none;
            margin: 0 auto;
            padding: 0;
            width: 60%;
        }

        #sortable li {
            margin: 0 3px 3px 3px;
            padding: 0.4em;
            padding-left: 1.5em;
            font-size: 1.4em;
            height: 18px;
        }

        #sortable li span {
            position: absolute;
            margin-left: -1.3em;
        }
    </style>
</head>

<body>

    <h4 style="text-align: center;">PHP & MySQLi Drag and Drop Reorder Items with jQuery</h4>
    <ul id="sortable">
        <?php
        if (count($items) > 0) {
            foreach ($items as $row) {
        ?>
                <li class="ui-state-default" id="<?php echo $row["id"]; ?>"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span><?php echo $row["item_name"]; ?></li>
        <?php
            }
        }
        ?>
    </ul>

    <script src="https://code.jquery.com/jquery-3.6.0.js"></script>
    <script src="https://code.jquery.com/ui/1.13.1/jquery-ui.js"></script>
    <script>
        $(function() {
            $("#sortable").sortable({
                update: function(event, ui) {
                    updateOrder();
                }
            });
        });

        function updateOrder() {
            var item_order = new Array();
            $('#sortable li').each(function() {
                item_order.push($(this).attr("id"));
            });
            var order_string = 'order=' + item_order;
            $.ajax({
                type: "POST",
                url: "update_order.php",
                data: order_string,
                cache: false,
                success: function(data) {}
            });
        }
    </script>
</body>

</html>

Concept

jQuery UI sortable() method.

$(function() {
    $("#sortable").sortable({
        update: function(event, ui) {
            updateOrder();
        }
    });
});

Function to fire an ajax request which update items order:

function updateOrder() {
    var item_order = new Array();
    $('#sortable li').each(function() {
        item_order.push($(this).attr("id"));
    });
    var order_string = 'order=' + item_order;
    $.ajax({
        type: "POST",
        url: "update_order.php",
        data: order_string,
        cache: false,
        success: function(data) {}
    });
}

Open update_order.php file and write this following code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi Drag and Drop Reorder Items with jQuery
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Include the database file
require 'dbconfig.php';

if (isset($_POST["order"])) {

    $order  = explode(",", $_POST["order"]);

    for ($i = 0; $i < count($order); $i++) {

        $query = "UPDATE items SET priority='" . $i . "' WHERE id=" . $order[$i];

        $statement = $conn->prepare($query);
        $statement->execute();
    }
}

Application Testing

Now,

Open into browser.

URL: http://localhost/php-mysqli-sorting/index.php

List of Items before sorting:

Drag & Sorted Items:

We hope this article helped you to learn PHP & MySQLi Drag and Drop Reorder Items with jQuery Tutorial in a very detailed way.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.