Create a Project Board Using Materialize CSS #3 Update the Group Order and Request AJAX

Repository

https://github.com/RubaXa/Sortable

What Will I Learn?

• Get Children Element
• Create a backend for sequence updates
• Update Data with AJAX

Requirements

• Basic CSS, Javascript , HTML
• Install Nodejs
• Materialize CSS
• Jquery >= 3.0
• Follow the previous tutorial

Difficulty

• Intermediate

Tutorial Contents

In the previous tutorial, we have created the database and retrieve data from the database. but we can not create the order dynamically and can be changed. it is what we will do in this tutorial, and not only that we will use the existing event at sortable.

Change Group Order

We will change the order when we drag and drop the group in frontend section. we can get element we are dragging with onUpdate () method on sortable, not only that we can also get attributes that exist in the element which will be useful when we use ajax to post data to the database.

• onUpdate()
  onUpdate is an event provided by sortable to detect the element being dragged. We can use it like this:
  Example:

var container = document.getElementById('items');
    Sortable.create(container,{
        handle:'h1',
        draggable:'.group',
        onUpdate: function(evt){
            console.log(evt)
        }
    });

• onUpdate has a callback function onUpdate: function(evt){} that contains elements of the dragged group, I will do console.log (evt) to see the contents of evt.

Get Children Element

• Add Attribute in Element
  To store the new group order, we need to know the value of the previous sequence for that we need to take the order of the existing group in the database with $row['group_id']. then $row['group_id'] I'll put in the data-id="< ?= $row['group_id'] ? >" attribute on the group element.

Example:

<div  class="group col s4" data-id="<?= $row['group_id'] ?>">
</div>

Noted: $row is a variable that contains data fetch from the database you can see the explanation in the previous tutorial.

If we look at the inspect element in the browser we can see the data-id from the database.

• Get new sequence
  We will take the data-id attribute and then we will do the iteration to put the order in the form of arrays for easier processing. We can take the child element by using from.children;

Example:

onUpdate: function(evt){
            var children    = evt.from.children;
            var items       = [];
            for (var i = 0; i < children.length; i++) {
                items.push(parseInt(children[i].getAttribute('data-id')));
            }
                          console.log(items)
        }

• We stored the element children from evt.from.children to var children. because we will do the iteration in it, and we create var items = []; to store looping results.

• We need data-id to store it in the database, to retrieve data-id we use getAttribute('data-id'). and then we push() to be combined in one array. Because the data type group_id in the database is in the integer, We have to change the data-id in the form of an integer by the method parseInt (). I will do console.log (items) to see the results.
  
  
  The Result
  
  
  

Create a backend for sequence updates.

After we get the latest order from the group. we can start creating a backend to update existing data in the database in the latest order. I will create a new file that is 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)";
}
if ($conn->query($sql)=== TRUE){
    echo 'berhasil update';
}else{
    echo 'error'.$conn->error;
}
$conn->close();
?>

• We need to create some key that we will make parameters in ajax in the frontend.
  1. $_POST['action']; : Key action to find out what action is being performed in AJAX. The key name on the frontend is 'action'.
  2. $_POST['type']; : as a reference of what part will be updated, the sequence of the group or the sequence of items in the group. The key name on the frontend is 'type'.
  3. $_POST['items']; : The result of the latest sequence we have made in the previous section. The key name on the frontend is 'items'.

• Action to update it is 'on_update', you can give another name if you want.

• We will make the query dynamic because we never know how many items will be updated. We need to store extra queries and do the looping in the query to make the query dynamic. We can do a looping on $items whose value we can from the frontend with the key 'items'.
  Example:

 $extra = '';
    foreach($items as $key => $item){
        $extra .= "WHEN id=$item THEN $key";
    } 

• 'items' that we pass to the backend in the form of arrays, we have to change it first into a string form to run on SQL Query. to separate and convert in string form we can use implode(). $id_lists = implode(",",$items);

• Now we can do the query with the data we have got. $sql = "UPDATE $column SET order_id = CASE $extra END WHERE id IN ($id_lists)";

1. $columns is the 'type' that we will update whether the group or items in the group.
2. $extra is a query that we are looping based on the number of 'items'.
3. $id_lists is a collection of group_id that we have changed in string form.

• And the last one we run the query with $conn->query($sql). And we close the connection with $conn->close();

Update Data with AJAX

• Installation
  After we finish creating the backend, now we will post the data using jQuery AJAX. We need to install jQuery. You can install jquery via NPM:
  Install:
  npm install jquery
  
  
  Use:
  We can use by putting the file directory before the closing tag < /body >.

<script type="text/javascript" src="node_modules/jquery/dist/jquery.min.js"></script>

• Post Data to Backend with AJAX
  We can post data using post () method, and create an object that contains the data that we will post to update.php.
  Example:

//request ajax
            data = {
                action  : "on_update",
                type    : "groups",
                items   : items
            };
            $.post('update.php',data).done(function(data){
                console.log(data)
            });

• We store data in data which contains action, type, and items.

1. action: This key will be received on the backend in $ _POST ['action']. and its value is "on_update"as a parameter to be processed by the backend.
2. type: This key will be received on the backend in $ _POST ['type']. and its value is "groups". This as a parameter what table will be updated.
   this as a parameter what table will be updated. in the previous tutorial, we have created a sortable database with two tables namely groups and items.
3. items: This key will be received on the backend in $ _POST ['items']. and its value is derived from iterating data-id attributes.

• Method post() has two parameters which first is URL or File. and the second is data to be posted to the backend. If we have finished we can see the result as follows:

We can see now that after we move the group, and we reload the browser page, we will find the same result. because the data we change is stored in the database. Thank you for following this tutorial. may be useful !!.

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

Proof of Work Done

https://github.com/Ryanalfarisi/sortable