Create a Project Board Using Materialize CSS #2 Connect database and Display data in Sortable Element

Repository

https://github.com/RubaXa/Sortable

What Will I Learn?

• Set Database
• Connect to database
• Show data in the element

Requirements

• MySQL
• Basic PHP OOP
• Localhost (Xampp, Wampp, or etc)
• Follow the previous tutorial

Difficulty

• Intermediate

Tutorial Contents

In this tutorial is a continuation of the previous tutorial series, if in the previous tutorial data we show be static, then in this tutorial, we will take data from the database for data to be dynamic.

Create Database

I will use MYSQL to create the database, I will create a database sortable and then I will create two tables ie groups and items. groups will contain groups that we will show and items are the contents of each group

• Table Groups
  We will create table groups with this structure:
• id: id is an auto increment and primary key. later we will do the left join with the primary key in table groups and foreign key in items. The type is ainteger.
• title: title is the name of each group. The type is varchar.
• order_id: order_id is the order of the group to be displayed. The type is tiny integer.

Name	Type	Length/value	Indeks
id	INT	11	Primary
title	Varchar	100	--
order_id	Tinyint	11	--

• Sql query

INSERT INTO `sortable`.`groups` (`id`, `title`, `order_id`) VALUES ('1', 'naruto', '1'), ('2', 'one piece', '2'), ('3', 'dragonball', '3');

• Table Items
  Table items are the contents of each group in the join in a table, but we will differentiate with group_id each item. here is the structure:

• id: id is an auto increment and primary key. later we will do the left join with groups.id on items.group_id.

• title: title is the name of each item. The type is varchar.

• group_id: group_id is the foreign key. The type is tiny integer.

• order_id: order_id is the order of the each items to be displayed. The type is tiny integer.

Name	Type	Length/value	Indeks
id	INT	11	Primary
title	Varchar	100	--
group_id	Tinyint	11	--
order_id	Tinyint	11	--

• SQL query

INSERT INTO `sortable`.`items` (`id`, `title`, `group_id`, `order_id`) VALUES (NULL, 'sasuke', '1', '1'), (NULL, 'sakura', '1', '2'), (NULL, 'shikamaru', '1', '3'), (NULL, 'luffy', '2', '1'), (NULL, 'sanji', '2', '2'), (NULL, 'nami', '2', '3'), (NULL, 'goku', '3', '1'), (NULL, 'bezita', '3', '2'), (NULL, 'bulma', '3', '3');

• Relation
  This is an illustration of the relation between the tables with the left join:

Connect to database

We will connect to the database, for that we need to run our localhost. in this tutorial, I am using Xampp. I will create an index.php file to connect to the database. I will use PHP OOP.

Example:

<?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);
}
$sql = "Select *, groups.title AS group_title, groups.order_id AS group_order_id 
        FROM groups LEFT JOIN items ON items.group_id = groups.id
        ORDER BY groups.order_id, items.order_id";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()){
    print_r($row)
}
?>

• We can connect our database by using new myql(), $conn = new mysql($server, $username, $password, $dbname);, The function requires 4 mandatory parameters to do connection. $dbname is the name of the database we use in this tutorial.

• We can check if there is an error when connecting to the database in this way if ($conn->connect_error), the error response is in the object $conn->connect_error.

• Then we do a left join, to merge the groups tables and items. LEFT JOIN items ON items.group_id = groups.id. We can display any column that we want to show, in this tutorial I use alias(AS) name because there is same column name in both table. Select *, groups.title AS group_title, groups.order_id AS group_order_id. and we can sort the data we will display ORDER BY groups.order_id, items.order_id".

• We can see the result by doing looping on SQL query
  

Noted: Make sure your localhost is running properly.

Show data in sortable element

• Looping group
  After we managed to do a left join. we get data in $result = $conn->query($sql);. We can do the looping in the element group < div class="group col s4" >< /div >.

Example:

<div class="wrapper">
        <div id="items" class="row">
              // looping 
            <? while ($row = $result->fetch_assoc()):?>
            <div id="group1" class="group col s4">
                <h1>Naruto</h1>
                <div class="group-items collection">
                    <li class="collection-item">Kakashi</li>
                    <li class="collection-item">Shikamaru</li>
                    <li class="collection-item">Sasuke</li>
                </div>
            </div>
            <? endwhile; ?>
        </div>
    </div>

• But if we do a loop like this, we will have problems. because we want to loop based on the number of groups instead of the number of items, $result contains the number of items(9). so when we run the code the result will be like this:

• Make Helper
  We will display the number of groups instead of the number of items, therefore we will create a helper to help us not to loop if group_idis different from the previous one.

Example:

<div class="wrapper">
        <div id="items" class="row">
            <? 
                $prev_group_id = "";
                while ($row = $result->fetch_assoc()):
            ?>
                <? if($prev_group_id != $row['group_id']): ?>
                    <div id="group1" class="group col s4">
                        <h1><?= $row['group_id'] ?></h1>
                        <div class="group-items collection">
                            <li class="collection-item"><?= $row['title'] ?></li>
                <? endif; ?>
                <? if($prev_group_id != $row['group_id']): ?>
                        </div>
                    </div>
                <? endif; ?>
            <? 
                $prev_group_id = $row['group_id'];
                endwhile; 
            ?>
        </div>
    </div>

• We create a variable $prev_group_id with value ' '. This variable is useful as a reference to whether the group_id that is in the looping is the same or not with the previous group_id.
• Then we can do if() before the element group is created. It's useful to make a conditionif($prev_group_id != $row['group_id']), if the condition result is true, then we will create a new element group < div class="group col s4" >< /div >.
  Noted: Do not forget to close < /div > the div element from the element group.

<? if($prev_group_id != $row['group_id']): ?>
//closing tag
            </div>
        </div>
<? endif; ?>

• We can retrieve data items and group names from the database via the $row that we declare in while ($row = $result->fetch_assoc()). We can retrieve data in an array like this $ row ['key'], as we seen in the previous section this is the form of the existing data in $row.

array(
        [id]                => 1
        [title]             => sasuke
        [order_id]          => 1
        [group_id]          => 1
        [group_title]       => naruto
        [group_order_id]    => 0
    )

• at the end at the end of the iteration < ?endwhile;? > , We must assign a new value to $prev_group_id, so that the value of $prev_group_id is dynamic. the value we give comes from $row['group_id'].

<? 
    $prev_group_id = $row['group_id'];
    endwhile; 
?>

• The result
  If it is done we run your local server and we will see the data has been taken from the database.
  

• Full Code

<?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);
}
$sql = "Select *, groups.title AS group_title, groups.order_id AS group_order_id 
        FROM groups LEFT JOIN items ON items.group_id = groups.id
        ORDER BY groups.order_id, items.order_id";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()){
    print_r($row)
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Sortable Like Trello</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0-beta/css/materialize.min.css">
</head>
<style type="text/css">
    body{
        background: #ec407a;
    }
    .wrapper{
        width: 80%;
        margin:10px auto;
    }
</style>
<body>
    <div class="wrapper">
        <div id="items" class="row">
            <? 
                $prev_group_id = "";
                while ($row = $result->fetch_assoc()):
            ?>
                <? if($prev_group_id != $row['group_id']): ?>
                    <div id="group1" class="group col s4">
                        <h1><?= $row['group_id'] ?></h1>
                        <div class="group-items collection">
                            <li class="collection-item"><?= $row['title'] ?></li>
                <? endif; ?>
                <? if($prev_group_id != $row['group_id']): ?>
                        </div>
                    </div>
                <? endif; ?>
            <? 
                $prev_group_id = $row['group_id'];
                endwhile; 
            ?>
        </div>
    </div>
<script type="text/javascript" src="node_modules/sortablejs/Sortable.min.js"></script>
<script type="text/javascript">
    var container = document.getElementById('items');
    Sortable.create(container,{
        handle:'h1',
        draggable:'.group'
    });
    var group = document.getElementsByClassName('group-items');
    for (var i = 0; i<group.length; i++) {
        Sortable.create(group.item(i),{
            group: 'item-list'
        });
    }   
</script>
</body>
</html>

We have finished fetching data from the database and now the data in the sorted element is dynamic. thank you for following this tutorial. in the next tutorial, I will create a sequence of updates based on the database.

Curriculum

• Create a Project Board Using Materialize CSS #1 Sortable List, Sort Group, Sort list in Group