Reading Time: 8 minutes

1,317 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.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

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.