Custom sort – jQuery UI, AJAX, PHP & MySQL

We are going to implement a custom sort using jQuery UI, which helps you to customize the order of data being displayed on your website. First, add CDN for jquery and jquery UI. Goto cdnjs.com to get CDN link for jQuery and visit this link to get CDN link for jQuery UI. Copy the JS link for jQuery, and CSS and JS links for jQuery UI. Create 2 script tags for jQuery and jQuery UI JS and one link tag for jQuery UI CSS.

index.php

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js">
</script>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.css">

<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js">
</script>

First you need to show all data from database. Here we are connecting with our sample database and displaying all products based on their product code.

<?php

    $conn = mysqli_connect("localhost", "root", "", "classicmodels");
    $result = mysqli_query($conn, "SELECT * FROM products ORDER BY productCode DESC");

?>

1. Create a div container which will enclose all data.
2. Create a while loop to run through all records in database.
3. Inside this loop, create a div tag which will represent each product separately.
4. Give it a class so it will group each product under same class.
5. and give it a unique ID, now ID will be the value which will tell the order of all products in an array. In our database, unique ID of product is productCode, so we will use that. But you must use as per your database table.

<div id="myData">
    <?php while ($row = mysqli_fetch_object($result)) { ?>

        <div 
            class="item"
            id="<?php echo $row->productCode; ?>"
            style="margin-top: 0px; margin-bottom: 0px;">
                
            <img
                src="<?php echo $row->image; ?>"
                style="width: 200px;">

            <h3><?php echo $row->productName; ?></h3>

        </div>
    <?php } ?>
</div>

Now we need to find a way to able to drag them so we can move them. So,

1. Create a script tag.
2. Attach a listener which will be called when the page is fully loaded.
3. Select the div container using ID.
4. Call sortable function, it exists in jquery UI. Its constructor will receive an object where you can specify options.

<script>

    window.products = [];
    
    $(function () {
        $("#myData").sortable({
            "items": ".item",
            "opacity": 0.6,
            "cursor": "move",
            "update": function (event, ui) {
                var data = $("#myData").sortable("toArray");
                
                // if using table
                // data = data.filter( n => n );

                window.products = data;
            }
        });
    });
</script>

jQuery UI sortable options

1. “items”: Selector of each individual item. So we will use the div inside loop having class class=”item”.
2. “opacity”: Give it an opacity, so when we select some product it will be highlighted among others.
3. “cursor”: A cursor which will be displayed on div only during dragging an item.
4. “update”: A function which will be called when you finish dragging some item from one place to another.

The update function has 2 parameters, events and UI (although we are not going to use any of them but you might need them in your case). Inside this function, we will get the new order of items in a variable. Select the main div container tag using its ID selector and call sortable function same as above. And inside it pass the argument “toArray”. It will return all items unique ID in a sorted array.

If you are using tables instead of divs, you might receive first element of array as empty string. You can prevent this by calling filter function to this array. Passing n means to remove null or empty strings.

Save custom sequence in database

Now we will create a button which when clicked will save this data in selected sequence.

<button type="button" onclick="saveData();">Save</button>

Create a function inside script tag:

function saveData() {
    var ajax = new XMLHttpRequest();
    ajax.open("POST", "save.php", true);
    ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    ajax.send("data=" + JSON.stringify(window.products));

    ajax.onreadystatechange = function () {
        if (this.readyState == 4 && this.status == 200) {
            console.log(this.responseText);
        }
    };
}

1. First create an AJAX object.
2. Call open method and set the request method to “POST”.
3. Second parameter will be the name of file where data needs to be sent.
4. Since this is a post request, so we also need to attach a header for content type.
5. And send the data will be in name value pair separated by ampersand sign.
6. Since we want to send an array so we need to convert that into JSON string.
7. onreadystatechange event will be fired when the response has been received from server.

save.php

Create a new file named “save.php” which will handle the request. First make a connection with database. Create a new table in database which will hold the sequence of products, its structure will be as below:

Table name: product_order, columns:

id integer auto increment
product_id integer
order_number integer
Then in “save.php” paste the following code:

<?php

    $conn = mysqli_connect("localhost", "root", "", "classicmodels");

    $data = json_decode($_POST["data"]);

    mysqli_query($conn, "DELETE FROM product_order");

    $sql = "INSERT INTO product_order(product_id, order_number) VALUES";

    for ($a = 0; $a < count($data); $a++)
    {
        $sql .= " ('" . $data[$a] . "', '" . ($a + 1) . "')";

        if ($a < count($data) - 1)
        {
            $sql .= ",";
        }
    }

    mysqli_query($conn, $sql);
    echo "Done";

?>

First get all data that we are receiving in a separate variable, and since we are sending JSON string so we need to decode that JSON back in PHP array. First of all, whenever the sequence of products changes, we have to remove all the previous sequence from database. Then we will prepare an SQL query to insert the new sequence in product order table. So loop through each product ID. Product ID will be what we are receiving from AJAX. Order number must be starting from 1, to 2 3 and so on, that is why we are incrementing in variable $a.

Display data by custom sort

Now go back to index.php and change the SQL query to following:

<?php

$conn = mysqli_connect("localhost", "root", "", "classicmodels");

$result = mysqli_query($conn, "SELECT * FROM products INNER JOIN product_order ON products.productCode = product_order.product_id ORDER BY product_order.order_number ASC");

?>

So we just need to change this query to display products based on order number from product_order table. Inner join with product_order, based on productID. And order by order_number from product_order table. Ascending order will make it start from 1 to 2 and 3 and so on.

That’s how you can implement a custom sort in jQuery UI using AJAX, PHP and MySQL.

[wpdm_package id=’219′]