Reorder images and save to MySQL database – CodeIgniter 4

Using jQuery UI sortable you can enable HTML element dragging on your page.

Initialize it on the element that you want to allow the users to reorder.

In this tutorial, I show how you can reorder images using jQuery UI and save them to the MySQL database in CodeIgniter 4.

Reorder images and save to MySQL database - CodeIgniter 4


Contents

  1. Database configuration
  2. Enable CSRF
  3. Create Table
  4. Model
  5. Route
  6. Controller
  7. View
  8. Output
  9. Conclusion

1. Database configuration

  • Open .env file which is available at the project root.

NOTE – If dot (.) not added at the start then rename the file to .env.

  • Remove # from start of database.default.hostname, database.default.database, database.default.username, database.default.password, database.default.DBDriver, database.default.DBPrefix, and database.default.port.
  • Update the configuration and save it.
database.default.hostname = 127.0.0.1
database.default.database = codeigniterdb
database.default.username = root
database.default.password = root
database.default.DBDriver = MySQLi
database.default.DBPrefix =
database.default.port = 3306

2. Enable CSRF

  • Again open .env file.
  • Remove # from the start of the security.tokenName,security.headerName, security.cookieName, security.expires,and security.regenerate.
  • I update the security.tokenName value with 'csrf_hash_name'. With this name read CSRF hash. You can update it with any other value.
  • If you don’t want to regenerate CSRF hash after each request then set security.regenerate = false.
security.tokenName = 'csrf_hash_name' 
security.headerName = 'X-CSRF-TOKEN' 
security.cookieName = 'csrf_cookie_name' 
security.expires = 7200 
security.regenerate = true
  • Open app/Config/Filters.php file.
  • Uncomment 'csrf' in 'before' if commented.
// Always applied before every request
public $globals = [
    'before' => [
       //'honeypot'
       'csrf',
    ],
    'after' => [
       'toolbar',
       //'honeypot'
    ],
];

3. Create Table

  • Create a new table images using migration.
php spark migrate:create create_images_table
  • Now, navigate to app/Database/Migrations/ folder from the project root.
  • Find a PHP file that ends with create_images_table and open it.
  • Define the table structure in the up() method.
    • filename – This field is used to store file name.
    • path – This field is used to store the upload file path.
    • sort – Store position.
  • Using the down() method delete images table that calls when undoing migration.
<?php namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateImagesTable extends Migration
{
    public function up() {
       $this->forge->addField([
             'id' => [
                 'type' => 'INT',
                 'constraint' => 10,
                 'unsigned' => true,
                 'auto_increment' => true,
             ],
             'filename' => [
                 'type' => 'VARCHAR',
                 'constraint' => '100',
             ],
             'path' => [
                 'type' => 'VARCHAR',
                 'constraint' => '255',
             ],
             'sort' => [ 
                 'type' => 'INT', 
                 'constraint' => '5', 
             ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('images');
    }

    //--------------------------------------------------------------------

    public function down() {
       $this->forge->dropTable('images');
    }
}
  • Run the migration –
php spark migrate

I added some records to the table.


4. Model

  • Create Images Model –
php spark make:model Images
  • Open app/Models/Images.php file.
  • In $allowedFields Array specify field names – ['filename','path','sort'] that can be set during insert and update.

Completed Code

<?php

namespace App\Models;

use CodeIgniter\Model;

class Images extends Model
{
     protected $DBGroup = 'default';
     protected $table = 'images';
     protected $primaryKey = 'id';
     protected $useAutoIncrement = true;
     protected $insertID = 0;
     protected $returnType = 'array';
     protected $useSoftDeletes = false;
     protected $protectFields = true;
     protected $allowedFields = ['filename','path','sort'];

     // Dates
     protected $useTimestamps = false;
     protected $dateFormat = 'datetime';
     protected $createdField = 'created_at';
     protected $updatedField = 'updated_at';
     protected $deletedField = 'deleted_at';

     // Validation
     protected $validationRules = [];
     protected $validationMessages = [];
     protected $skipValidation = false;
     protected $cleanValidationRules = true;

     // Callbacks
     protected $allowCallbacks = true;
     protected $beforeInsert = [];
     protected $afterInsert = [];
     protected $beforeUpdate = [];
     protected $afterUpdate = [];
     protected $beforeFind = [];
     protected $afterFind = [];
     protected $beforeDelete = [];
     protected $afterDelete = [];
}

5. Route

  • Open app/Config/Routes.php file.
  • Define 2 routes –
    • / – Display images list in view.
    • images/updatePosition – Update file position.

Completed Code

$routes->get('/', 'ImagesController::index');
$routes->post('images/updatePosition', 'ImagesController::updatePosition');

6. Controller

  • Create ImagesController Controller –
php spark make:controller ImagesController
  • Open app/Controllers/ImagesController.php file.
  • Import Images Model.
  • Create 2 methods –
    • index() – Fetch all records from images table and order by sort field. Assign fetched records to $data['images'].

Load images view and pass $data Array.

    • updatePosition() – Using this method update file position.

Read POST data and check imageids is POST or not. If POST then assign $postData['imageids'] to $imageids_arr Array.

Loop on $imageids_arr Array to read update id. Update sort field with new $position value. Assign 1 to $status.

Store new token hash to $data['token'] and $status to $data['status']. Return $data Array in JSON format.

Completed Code

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\Images;

class ImagesController extends BaseController
{
     public function index(){
         // Fetch data 
         $images = new Images();
         $data['images'] = $images->select('*')
                          ->orderBy('sort','asc')
                          ->findAll();

         return view('images',$data); 
     }

     // Update position
     public function updatePosition(){
         $request = service('request'); 
         $postData = $request->getPost(); 

         $status = 0;
         $imageids_arr = array();
         if(isset($postData['imageids'])){

              $imageids_arr = $postData['imageids'];

              if(count($imageids_arr) > 0){
                  // Update sort position of images
                  $position = 1;
                  foreach($imageids_arr as $id){

                      $image = new Images();
                      $image->set('sort', $position);
                      $image->where('id', $id);
                      $image->update();

                      $position ++;
                  }

              }

              $status = 1;
         }

         // New CSRF token
         $data['token'] = csrf_hash();

         $data['status'] = $status;

         return $this->response->setJSON($data);
     }
}

7. View

HTML

  • Create images.php file in app/Views/.
  • Include jQuery, jQuery UI, and jQuery touch library.
<!-- jQuery UI CSS -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.13.2/themes/smoothness/jquery-ui.css">

<!-- jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>

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

<!-- jQuery touch -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.touch/1.1.0/jquery.touch.min.js"></script>

NOTE – Using jquery.touch.min.js to enable touch event on the mobile devices.

  • Create a hidden element to store the token name and hash.
  • Create <ul id="sortable" > to list images and initialize sortable.
  • Loop on $images to add <li > elements.
  • In the <li > add class="ui-state-default" and store image id in data-id attribute.
  • Add <img > element. Pass $path in src attribute.
  • Create a button to update the position of the images using jQuery AJAX.

Script

  • Initialize sortable() on <ul id="sortable" >.
  • On button click read CSRF token name and hash.
  • Create imageids_arr Array to store image ids for updating records.
  • Loop on <ul id="sortable" > <li> and read data-id attribute value and push to imageids_arr.
  • Send AJAX POST request to site_url('images/updatePosition').
  • Here, pass CSRF token and imageids_arr Array.
  • On successful callback update CSRF token hash and alert a success message.

Completed Code

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Reorder images and save to MySQL database - CodeIgniter 4</title>

    <!-- jQuery UI CSS -->
    <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.13.2/themes/smoothness/jquery-ui.css">

    <!-- jQuery -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>

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

    <!-- jQuery touch -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.touch/1.1.0/jquery.touch.min.js"></script>

    <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>
</head>
<body>

    <!-- CSRF Token -->
    <input type="hidden" class="txt_csrfname" name="<?= csrf_token() ?>" value="<?= csrf_hash() ?>" />

    <div style='width: 100%;'>
         <!-- List Images -->
         <ul id="sortable" >
              <?php
              foreach($images as $image){
                  $id = $image['id'];
                  $filename = $image['filename'];
                  $path = $image['path'];

                  echo '<li class="ui-state-default" data-id="'.$id.'" >
                            <img src="'.$path.'" title="'.$filename.'" ><br>
                            '.$filename.'
                        </li>';
              }
              ?>
         </ul>
    </div>
    <div style="clear: both; margin-top: 20px;">
        <input type='button' value='Save' id='btnsave'>
    </div>

    <!-- Script -->
    <script type="text/javascript">
    $(document).ready(function(){

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

         // Save order
         $('#btnsave').click(function(){

              // CSRF Token name 
              var csrfName = $('.txt_csrfname').attr('name'); 
              
              var csrfHash = $('.txt_csrfname').val(); // CSRF hash

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

              // AJAX request
              $.ajax({
                   url: "<?=site_url('images/updatePosition')?>",
                   type: 'post',
                   data: {[csrfName]: csrfHash,imageids: imageids_arr},
                   success: function(response){

                        // Update token
                        $('.txt_csrfname').val(response.token);

                        if(response.status == 1)
                            alert('Save successfully.');
                   }
              });
         });
    });
    </script>
</body>
</html>

8. Output

View Output


9. Conclusion

You can use the same concept to save other dragged element positions.

In the example, I am calling AJAX request to update the position when button gets clicked but if you want to send request when any image position gets changed then use sortable sortchange event.

Learn more about sortchange event from here.

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

Leave a Comment