How to Reorder Images and Save to MySQL with jQuery AJAX

jQuery UI sortable enables users to reorder HTML elements effortlessly through simple drag and drop actions.

With jQuery UI Sortable, users gets the ability to adjust the positions of images, this enhanc the visual layout and customization options on your webpage.

In this tutorial, I show how you can save the sorted image list order in the MySQL database and read it.

How to Reorder Images and Save to MySQL with jQuery AJAX

Demo Download

Table of Content

Table structure
Database Configuration
HTML & PHP – Create Layout
Updating Image Positions Dynamically with PHP and AJAX
jQuery – Initialize sortable() and Save reordered images using AJAX
Demo
Conclusion

1. Table structure

I am using images_list table in the example.

CREATE TABLE `images_list` (
   `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
   `name` varchar(150) NOT NULL,
   `location` varchar(150) NOT NULL,
   `sort` int(2) NOT NULL DEFAULT '0',
   `timemodified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. Database Configuration

Create a new config.php file.

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

3. HTML & PHP – Create Layout

Use <ul id="sortable"> to list images.

Select record from images_list table order by sort field in ascending order.

Loop on the record to add images.

In the <li > add data-id attribute to get the record id while saving with jQuery AJAX.

Add a button to save image positions on click.

NOTE – Included jquery.ui.touch-punch.min.js script to enable touch event on the mobile devices.

<?php
include "config.php";
?>
<!doctype html>
<html >
   <head>
       <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.13.2/themes/smoothness/jquery-ui.css">
       <style>
       #sortable { 
           list-style-type: none; 
           margin: 0; 
           padding: 0; 
           width: 90%; 
       }
       #sortable li { 
           margin: 3px 3px 3px 0; 
           padding: 1px; 
           float: left; 
           border: 0;
           background: none;
       }
       #sortable li img{
           width: 180px;
           height: 140px;
       }
       </style>

       <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
       <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.13.2/jquery-ui.min.js"></script>

       <script src="//cdnjs.cloudflare.com/ajax/libs/jqueryui-touch-punch/0.2.3/jquery.ui.touch-punch.min.js"></script>
   </head>
  <body>

       <div style='width: 100%;'>
            <!-- List Images -->
            <ul id="sortable" >
            <?php
            // Fetch images
            $fetch_images = mysqli_query($con,"SELECT * FROM images_list ORDER BY sort ASC");

            while($row = mysqli_fetch_assoc($fetch_images)){
                $id = $row['id']; 
                $name = $row['name'];
                $location = $row['location'];

                echo '<li class="ui-state-default" data-id="'.$id.'" >
                    <img src="'.$location.'" title="'.$name.'" >
                </li>';
            }

            ?>
            </ul>
       </div>
       <div style="clear: both; margin-top: 20px;">
            <input type='button' value='Submit' id='submit'>
       </div>

  </body>
</html>

4. Updating Image Positions Dynamically with PHP and AJAX

Create a ajaxfile.php file.

Capture the imageids from the $_POST request and store them in the $imageids_arr array. Proceed to iterate through this array.

Set $position=1 which is incremented inside the loop.

Within the loop, update the sort field with the $position value based on the $id and return a success indicator of 1.

<?php

include "config.php";

$imageids_arr = array();

if(isset($_POST['imageids'])){
    $imageids_arr = $_POST['imageids'];
}

if(count($imageids_arr) > 0){
    // Update sort position of images
    $position = 1;
    foreach($imageids_arr as $id){
         mysqli_query($con,"UPDATE images_list SET sort=".$position." WHERE id=".$id);
         $position ++;
    }
    echo 1;
    exit;
}else{
    echo 0;
    exit;
}

5. jQuery – Initialize sortable() and Save reordered images using AJAX

Initialize sortable on $('#sortable') selector.

When the submit button is clicked, execute a loop on the $('#sortable li') elements using the $.each() method.

Split the id values and establish an imageids_arr array to store the results.

Send an AJAX request to save positions for this pass imageids: imageids_arr as data.

$(document).ready(function(){

    // Initialize sortable
    $( "#sortable" ).sortable();

    // Save order
    $('#submit').click(function(){
         var imageids_arr = [];
         // get image ids order
         $('#sortable li').each(function(){
              var id = $(this).data('id');
              imageids_arr.push(id);
         });

         // AJAX request
         $.ajax({
              url: 'ajaxfile.php',
              type: 'post',
              data: {imageids: imageids_arr},
              success: function(response){
                   if(response == 1)
                        alert('Save successfully.');
              }
         });
    });
});

6. Demo

View Demo

7. Conclusion

Iterating through the <ul> and <li> elements to capture and save the desired image order. This order is stored efficiently in an array, which is then seamlessly pass with an AJAX request.

Furthermore, once the array is transmitted, the next step is to iterate through it again. During this iteration, the sort order is adjusted based on the index values. This ensures that the displayed images align perfectly with the user’s preferences and actions.

If you found this tutorial helpful then don't forget to share.