Create a Project Board Using Materialize CSS #4 Update the order of items in each group and Move items to another group

Repository

https://github.com/RubaXa/Sortable

What Will I Learn?

• Create dynamic functions
• Using AJAX to move the item to another group
• Using AJAX to update the item in a group.

Requirements

• Basic CSS, Javascript , HTML
• Localhost (xampp, wampp, or etc)
• Install Nodejs
• Materialize CSS
• Jquery >= 3.0
• Follow the previous tutorial

Difficulty

• Intermediate

Tutorial Contents

This tutorial is a continuation of the previous tutorial, the previous tutorial we have changed the order of the group and make AJAX request to the database to change the latest order. in this tutorial, we will change the order of items in each group.

Parameter changes in onUpdate()

There is a change in the parameters on the function onUpdate(), we will make the parameters become more dynamic. if we previously passed the object evt. we will change it by directly pass the children. and not through objects like this: evt.from.children.

Before: previous tutorial

onUpdate: function(evt){
    update_via_ajax(evt,'groups');
}

After:

onUpdate: function(evt){
    update_via_ajax(evt.from.children,'groups');    
}

• So with the changed parameters we no longer need to create variables like this: var children = evt.from.children;

Create AJAX request function

We will create a function to perform AJAX requests that will be used over and over again and become dynamic. because we will use this function to update the group order and update the order in the items in each group. I will create a function named update_via_ajax(element, items).

Example:

function update_via_ajax(element,type)
            var items       = [];
            for (var i = 0; i < element.length; i++) {
                items.push(parseInt(element[i].getAttribute('data-id')));
            }
            //request ajax
            data = {
                action  : "on_update",
                type    : type,
                items   : items
            };
            $.post('update.php',data).done(function(data){
                console.log(data)
            });
    }

• update_via_ajax(element, type); : This function has two parameters.

1. The first parameter : The first parameter is the element children we get from onUpdate: function (evt) {}.

2. The second parameter: The second parameter is the type or more precisely this is the reference table what will be updated. in the tutorial about the database, we have created two tables ie groups and items, so parameter type is worth 'groups' or 'items'.

• in the previous tutorial type that existed in the data object is a static value, now we will make it dynamic by getting the value through parameters.

Example:

function update_via_ajax(evt,type){
  data = {
       action   : "on_update",
       type     : type, //from 'groups or items' to type
       items    : items
  };
};

Using AJAX request function

• Use to update the group order
  We will use the function in onUpdate: function (evt) {}.

Example:

onUpdate:function(evt){
    update_via_ajax(evt,'groups');
}

• We pass two parameters, the first parameter is the child element and the second parameter is the table that we will update. There are two choices of 'groups' or 'items'.
  
  
• Use to update the order of group items
  We will use the function in onUpdate: function (evt) {}.

Example:

onUpdate:function(evt){
    update_via_ajax(evt,'items');
}

• We pass two parameters, the first parameter is the child element and the second parameter is the table that we will update. There are two choices of 'groups' or 'items'.
  
  
  The Result
  Now not only the order of groups that can be updated but we can also update the order of items in each group.

Move items to another group

During this tutorial series, we only sort the items in each group. this time we will move the item to another group with method onAdd().

• onAdd()
  We have changed the parameters in the function update_via_ajax (), the purpose is that there are differences in the onUpdate() and onAdd() functions. if on function onUpdate() we need to know the parent element of that element comes from evt.from.children. but if on method onAdd() we need to know the intended parent element and get the children element. evt.item.parentElement.children.

Example:

onAdd:function(evt){
       update_via_ajax(evt.item.parentElement.children,'items');
}

I will do console.log(evt.item.parentElement.children) and inspect the element in the browser to see the element evt.item.parentElement.children.

• Post data to AJAX requests
  The previous tutorial we have one action that is on_update. We will create a new action 'on_add' to handle the ajax request to move the item to another group. But before making the backend part we will make the first data post in frontend section.

Example:

onAdd:function(evt){
    update_via_ajax(evt.item.parentElement.children,'items');
        data = {
            action      : "on_add",
            type        : "items",
            items       : parseInt(evt.item.getAttribute('data-id'));
            group_id    : parseInt(evt.item.parentElement.parentElement.getAttribute('data-id'));
         };
$.post('update.php',data).done(function(data){
                    console.log(data)
                });
            }

• We create a data object, then we put the key and its value respectively.
  1. action: "on_add": on_add is the name of the action we will use as the action parameter to be performed in the backend.
  2. type: "items": items is the name of the table that we will update in the database, in this tutorial we have created two tables are groups and items.
  3. items: parseInt(evt.item.getAttribute('data-id'));: to update to the database we need the id of the item to be updated, we get the id on the $row variable coming from the backend and put it in the data-id attribute:

<li class="collection-item" data-id="<?= $row['group_id'] ?>"><?= $row['title'] ?></li>

You can use shorthand php <?= $row['group_id']?> is the same as php<? echo $row['group_id']; ?>. We can get the data-id item attribute with evt.item.getAttribute ('data-id'). Because type data in database is Tinyint we need to convert string to interger with method parseInt().

4. group_id: We also need group_id that we will update, to get group_id can do with evt.item.parentElement.parentElement.getAttribute('data-id');. to get group_id we need to pass two parent element parent > parent.

• Then we can post data to update.php with method $.post('update.php',data), and pass the data object as its parameter.
  
  

Create backend for handle action 'on_add'

We already have the action on_update. now we will add else if{} in update.php to add the on_add action.

Example:

if($action == "on_update"){
 ......
}else if($action == "on_add"){
    $group_id   = $_POST['group_id'];
    $sql = "UPDATE $column SET group_id = $group_id WHERE id = $items";
}

• We can get the group_id parameter with $_POST['group_id'];. then we can do the SQL update request based on the parameters we have passed on the frontend.
  UPDATE 'tablename' SET group_id = group_id WHERE id = id_items.

• FULL CODE update.php

<?php
$server     = "localhost";
$username   = "root";
$password   = "root";
$dbname     = "sortable";
//Create Connection
$conn = new mysql($server, $username, $password, $dbname);
//Check Connection
if ($conn->connect_error){
    die("Connection Failed: ". $conn->connect_error);
}
$action     = $_POST['action'];
$column     = $_POST['type'];
$items      = $_POST['items'];
if($action == "on_update"){
    $extra = '';
    foreach($items as $key => $item){
        $extra .= "WHEN id=$item THEN $key";
    }   
    $id_lists   = implode(",",$items);
    $sql        = "UPDATE $column SET order_id = CASE $extra END WHERE id IN ($id_lists)";
}else if($action == "on_add"){
    $group_id   = $_POST['group_id'];
    $sql = "UPDATE $column SET group_id = $group_id WHERE id = $items";
}
if ($conn->query($sql)=== TRUE){
    echo 'berhasil update';
}else{
    echo 'error'.$conn->error;
}
$conn->close();
?>

• The Result
  After we have finished requesting ajax to the backend we can see the result by running localhost. We can see when we move the item to another group and reload the page, the item remains in the latest group. it's because we change the order and its group in the database

Thank you for following this tutorial, may be useful for you.

Curriculum

• Create a project board #1 Sortable List, Sort Group, Sort list in Group
• Create a Project Board #2 Connect database and Display data in Sortable Element
• Create a Project Board #3 Update the Group Order and Request AJAX

Proof of Work Done

https://github.com/Ryanalfarisi/sortable