How to Reorder Images and Save to MySQL with jQuery AJAX

jQuery UI sortable enable to reorder the HTML element by drag & drop.

This will use to allow the users to adjust the positions of images to display on the 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


Contents

  1. Table structure
  2. Configuration
  3. HTML & PHP
  4. PHP
  5. Script
  6. 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Create a new config.php file.

Completed Code

<?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

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 id attribute to get the record id while saving with jQuery AJAX.

Add a button to save image positions on click.

Completed Code

<?php
include "config.php";
?>
<!doctype html>
<html >
  <head>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/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.3.1/jquery.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  </head>
  <body>

    <div style='float: left; 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" id="image_'.$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. PHP

Create a new ajaxfile.php file.

Assign $_POST['imageids'] in $imageids_arr and loop on it.

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

Update sort field by $position value according to $id value.

Completed Code

<?php

include "config.php";

$imageids_arr = $_POST['imageids'];

// 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 "Update successfully";
exit;

5. Script

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

On submit button click loop on $('#sortable li') using $.each().

Split id value and initialize imageids_arr Array.

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

Completed Code

$(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).attr('id');
       var split_id = id.split("_");
       imageids_arr.push(split_id[1]);
    });

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

6. Conclusion

Loop on the <ul> <li> to save the reorder of the images. Use an Array to store image ids and pass it in the AJAX request.

Now loop on the passed Array and update sort order according to index.

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

Related Post

Spread the love
  • 3
  •  
  •  
  •  
  •  
  •  

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *