Knowledge Base

• DB.class.php
• orderUpdate.php
• index.php
• style.css
• images/

Create Database Table

To store images and their display order, a table needs to be created in the database. The following SQL creates a images table with some basic required fields in the MySQL database.

CREATE TABLE `images` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `file_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `img_order` int(5) NOT NULL DEFAULT '0',
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DB Class (DB.class.php)

The DB class controls all the database related works, like get records, update record, etc. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

The DB class contains two methods to fetch and update images data.

• getRows() function fetch the images data from the database.
• updateOrder() function updates list order of the images.

<?php
class DB{
    // Database configuration
    private $dbHost     = "localhost";
    private $dbUsername = "root";
    private $dbPassword = "root";
    private $dbName     = "gallerymanager";
    private $imgTbl     = 'images';
    
    function __construct(){
        if(!isset($this->db)){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
    
    /*
     * Get rows from images table
     */
    function getRows(){
        $query = $this->db->query("SELECT * FROM ".$this->imgTbl." ORDER BY img_order ASC");
        if($query->num_rows > 0){
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }else{
            $result = FALSE;
        }
        return $result;
    }
    
    /*
     * Update image order
     */
    function updateOrder($id_array){
        $count = 1;
        foreach ($id_array as $id){
            $update = $this->db->query("UPDATE ".$this->imgTbl." SET img_order = $count WHERE id = $id");
            $count ++;    
        }
        return TRUE;
    }
}
?>

Reorder Images with Drag and Drop (index.php)

The index.php file display the images and allow the user to reorder images by drag and drop.

jQuery & Ajax Code:
At first, include the jQuery and jQuery UI library to use Ajax and Draggable functionality

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

The following jQuery code is used to enable the jQuery UI Draggable & Sortable features and implement the drag & drop functionality. When order save request is submitted, current images order send to the orderUpdate.php file via Ajax to update the images order in the database.

<script type="text/javascript">
$(document).ready(function(){
    $('.reorder_link').on('click',function(){
        $("ul.reorder-photos-list").sortable({ tolerance: 'pointer' });
        $('.reorder_link').html('save reordering');
        $('.reorder_link').attr("id","saveReorder");
        $('#reorderHelper').slideDown('slow');
        $('.image_link').attr("href","javascript:void(0);");
        $('.image_link').css("cursor","move");
        $("#saveReorder").click(function( e ){
            if( !$("#saveReorder i").length ){
                $(this).html('').prepend('<img src="/images/refresh-animated.gif"/>');
                $("ul.reorder-photos-list").sortable('destroy');
                $("#reorderHelper").html( "Reordering Photos - This could take a moment. Please don't navigate away from this page." ).removeClass('light_box').addClass('notice notice_error');
    
                var h = [];
                $("ul.reorder-photos-list li").each(function() {  h.push($(this).attr('id').substr(9));  });
                
                $.ajax({
                    type: "POST",
                    url: "orderUpdate.php",
                    data: {ids: " " + h + ""},
                    success: function(){
                        window.location.reload();
                    }
                }); 
                return false;
            }   
            e.preventDefault();     
        });
    });
});
</script>

PHP & HTML Code:
Initially, all the images are retrieved from the database and listed as per the order specified in img_order field of images table using PHP and DB class. Once the reorder link is clicked, drag & drop feature is enabled to reorder the images.

<div class="container">
    <a href="javascript:void(0);" class="reorder_link" id="saveReorder">reorder photos</a>
    <div id="reorderHelper" class="light_box" style="display:none;">1. Drag photos to reorder.<br>2. Click 'Save Reordering' when finished.</div>
    <div class="gallery">
        <ul class="reorder_ul reorder-photos-list">
        <?php
        // Include and create instance of db class
        require_once 'DB.class.php';
        $db = new DB();

        // Fetch all images from database
        $images = $db->getRows();
        if(!empty($images)){
            foreach($images as $row){
        ?>
            <li id="image_li_<?php echo $row['id']; ?>" class="ui-sortable-handle">
                <a href="javascript:void(0);" style="float:none;" class="image_link">
                    <img src="images/<?php echo $row['file_name']; ?>" alt="">
                </a>
            </li>
        <?php } } ?>
        </ul>
    </div>
</div>

Update Images Order (orderUpdate.php)

The orderUpdate.php file is called by the Ajax request and receive the current images order from the index.php through Ajax. The images IDs string breaks into array and pass to the updateOrder()method of DB class to update the images order.

<?php

// Include and create instance of db class
require_once 'DB.class.php';
$db = new DB();

// Get images id and generate ids array
$idArray = explode(",",$_POST['ids']);

// Update images order
$db->updateOrder($idArray);

?>

style.css

The following CSS code specifies the style for the image list, links, and other elements.

body {
    background: #FFFFFF;
    margin: 0px;
    font-family: 'Roboto', sans-serif;
    font-size: 14px;
    color: #4f5252;
    font-weight: 400;
}
.container{
    margin-top:50px;
    padding: 10px;
}
ul, ol, li {
    margin: 0;
    padding: 0;
    list-style: none;
}
.reorder_link {
    color: #3675B4;
    border: solid 2px #3675B4;
    border-radius: 3px;
    text-transform: uppercase;
    background: #fff;
    font-size: 18px;
    padding: 10px 20px;
    margin: 15px 15px 15px 0px;
    font-weight: bold;
    text-decoration: none;
    transition: all 0.35s;
    -moz-transition: all 0.35s;
    -webkit-transition: all 0.35s;
    -o-transition: all 0.35s;
    white-space: nowrap;
}
.reorder_link:hover {
    color: #fff;
    border: solid 2px #3675B4;
    background: #3675B4;
    box-shadow: none;
}
#reorder-helper{
    margin: 18px 10px;
    padding: 10px;
}
.light_box {
    background: #efefef;
    padding: 20px;
    margin: 15px 0;
    text-align: center;
    font-size: 1.2em;
}

/* image gallery */
.gallery{ width:100%; float:left; margin-top:100px;}
.gallery ul{ margin:0; padding:0; list-style-type:none;}
.gallery ul li{ padding:7px; border:2px solid #ccc; float:left; margin:10px 7px; background:none; width:auto; height:auto;}
.gallery img{ width:250px;}

/* notice box */
.notice, .notice a{ color: #fff !important; }
.notice { z-index: 8888;padding: 10px;margin-top: 20px; }
.notice a { font-weight: bold; }
.notice_error { background: #E46360; }
.notice_success { background: #657E3F; }