How to use PHP web service to Kanban data binding and CRUD operations?

4 mins read

Persist data in server

Edited data can be persisted in database using RESTful web services.

All the CRUD operations in Kanban are done through DataManager. The DataManager contains an option to bind all the CRUD related data in server-side. Please refer to this documentation to know about DataManager.

You can configure data using URL adaptor. The url property of DataManager is used to fetch JSON data on webservice and bind it into Kanban at initial-loading. The ‘crudUrl’ property is used to invoke the web service to get the JSON object that contains the CRUD data when performing the CRUD operations.

Refer to this documentation to know about basic rendering of the Kanban control.

PHP web service for Kanban CRUD operations

The following steps explain how to use PHP web service to Kanban data binding and CRUD operations.

Step 1:

Open XAMPP control panel applications, and then click Start button for ‘Apache’ and ‘MySQL’. The sample can be run directly on the browser through localhost with appropriate port numbers, on which the Apache server is currently listening.

Click MySQL admin button, it will navigate to phpMyAdmin home page with corresponding localhost port number.

Now, you can see the running indicator as shown in the following screenshot.

Screenshot of running indicator

Step 2:

Create your own database table in phpMyAdmin home page. Use the following guidelines to create a database table.

Create your own database by clicking New on the left in panel.
Specify table name and number of columns when clicking the database name, and then click Go button.
On the next page, configure the structure of the columns in a new table. After configured, click the Save button.
Add records inside a database table, open the table with phpMyAdmin, click the Insert tab, and then add your data.

The following screenshot illustrates a table, which was created with some tasks’ data.

Table with some 'tasks' data

Step 3:

Create the GetData.php file to get the JSON data to be loaded into the Kanban. When using UrlAdaptor, data should be returned on-demand, and it should be in the form of result and count.

<?php
// Use it to connect database
$host = "localhost";
$db_name = "kanbandatabase"; /* Put your database name */
$username = "root";
$password = ""; /* Put your password */
header('Content-Type: application/json');
 
// Create connection
$link = mysqli_connect($host,$username,$password,$db_name);
 
$json_param = file_get_contents("php://input");
$params = json_decode($json_param,true);
/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
 
// SQL query for retrieve all the column data from a table
$query =$sql = "SELECT * FROM tasks";
 
$result = $link->query($query);
 
$json=array();
 
while ($row = mysqli_fetch_array($result)) {
    array_push($json,array('Id' => (int)$row{'Id'}, 'Status' => $row{'Status'}, 'Summary' => $row{'Summary'}, 'Type' => $row{'Type'}, 'Priority' => $row{'Priority'}, 'Tags' => $row{'Tags'}, 'Estimate' => (int)$row{'Estimate'}, 'Assignee' => $row{'Assignee'}, 'RankId' => (int)$row{'RankId'}));
}
 
if(isset($params['select']) !=null){
    echo json_encode($json);
}
else {
    $countquery = mysqli_query($link,$sql);
    $count=mysqli_num_rows($countquery);  // Return total records count
 
    $response=array("result"=>$json,"count"=>(int)$count);
    echo json_encode($response);
}
 
?>

Step 4:

Create the KanbanCrud.php file to update, insert, and delete operations performed in Kanban.

<?php
// Use to connect database
$host = "localhost";
$db_name = "kanbandatabase";
$username = "root";
$password = "";
 
$link = mysqli_connect($host,$username,$password,$db_name);
header('Content-Type: application/json');
$json_param = file_get_contents("php://input");
$params = json_decode($json_param,true);
/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
if (isset($params['action'])) {  
     // Perform update operation
      if ($params['action'] == "batch" && !empty($params['changed'])) {
              $value=$params['changed'][0];
               $Id=$value["Id"];
                $Status =$value["Status"];
                $Summary =$value["Summary"];
                $Type =$value["Type"];
                $Priority =$value["Priority"];
                $Tags =$value["Tags"];
                $Estimate =$value["Estimate"];
                $Assignee =$value["Assignee"];
                $RankId =$value["RankId"];
                $sql = "UPDATE tasks SET Id = $Id, Status= '$Status', Summary ='$Summary', Type='$Type', Priority='$Priority', Tags='$Tags', Estimate=$Estimate,  Assignee='$Assignee', RankId='$RankId' WHERE tasks.Id = $Id";
                $updatequery =mysqli_query($link, $sql);
                $success=array("Updated"=>array("Id"=>$Id,"Status"=>$Status,"Summary"=>$Summary, "Type"=>$Type, "Priiority"=>$Priority, "Tags"=>$Tags, "Estimate"=>$Estimate, "Assignee"=>$Assignee, "RankId"=>$RankId));
                echo json_encode($success);
        }
        // Perform insert operation
        if ($params['action'] == "batch" && !empty($params['added'])) {
                $value=$params['added'][0];
                $Id=(int)$value["Id"];
                $Status =isset($value["Status"]) ? $value["Status"] : null;
                $Summary =isset($value["Summary"]) ? $value["Summary"] : null;
                $Type =isset($value["Type"]) ? $value["Type"] : null;
                $Priority =isset($value["Priority"]) ? $value["Priority"] : null;
                $Tags =isset($value["Tags"]) ? $value["Tags"] : null;
                $Estimate =isset($value["Estimate"]) ? (float)$value["Estimate"] : null;
                $Assignee =isset($value["Assignee"]) ? $value["Assignee"] : null;
                $RankId =isset($value["RankId"]) ? (int)$value["RankId"] : null;
                $res2=mysqli_query($link,"INSERT INTO tasks(Id,Status,Summary,Type,Priority,Tags,Estimate,Assignee,RankId) VALUES('$Id','$Status','$Summary','$Type','$Priority','$Tags','$Estimate', '$Assignee', '$RankId')");
                $success=array("Inserted"=>array("Id"=>$Id,"Status"=>$Status,"Summary"=>$Summary,"Type"=>$Type,"Priority"=>$Priority,"Tags"=>$Tags,"Estimate"=>$Estimate,"Assignee"=>$Assignee,"RankId"=>$RankId ));
                echo json_encode($success);
        }
        // Perform delete operation
        if ($params['action'] == "batch" && !empty($params['deleted'])) {
                $value=$params['deleted'][0];
                $key=(int)$value['Id'];
                $res1 = mysqli_query($link, "DELETE FROM tasks WHERE Id = $key");
                $success=array("Deleted"=>$key);
                echo json_encode($success);
        }
}
?>

Step 5:

Bind the above two php files into the ‘url’ and ‘crudUrl’ properties of DataManager.

<!DOCTYPE html>
    <html>
        <head>
            <title>Getting Started - Kanban</title>
            <!--Dependency files references-->
            <link href="http://cdn.syncfusion.com/16.4.0.42/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" />
                <link href="http://cdn.syncfusion.com/16.4.0.42/js/web/responsive-css/ej.responsive.css" rel="stylesheet" />
                <script src="http://cdn.syncfusion.com/js/assets/external/jquery-3.1.1.min.js"></script>
                <script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js"></script>
                <script src="http://cdn.syncfusion.com/16.4.0.42/js/web/ej.web.all.js"></script>
        </head>
        <script id="delete" type="text/x-jsrender">
           <a class="e-customdelete  e-icon" />
        </script>
        <style type="text/css" class="cssStyles">
            .e-customdelete:before {
                content: "\e800";
                line-height: 26px;
                min-height: 26px;
                min-width: 14px;
                display: inline-block;
            }
        </style>
        <script type="text/javascript" language="javascript">
           function toolbarClick(args) {
                if (args.itemName == "delete" && this.element.find(".e-kanbancard").hasClass("e-cardselection")) {
                    var selectedcard = this.element.find(".e-cardselection");
                    this.KanbanEdit.deleteCard(selectedcard.attr("id"));
                }
            }
        </script>
        <body>
        <button type="button">Click Me</button>
             <?php 
                require_once 'Src\AutoLoad.php';
                $dataManager= new EJ\DataManager();
                $dataManager->url('GetData.php')->crudUrl('KanbanCrud.php')->adaptor('UrlAdaptor');
                $kanban = new EJ\Kanban("defaultKanban");    
                $column = new EJ\Kanban\Column();
                $column ->key("Open")->headerText("Backlog")->showAddButton(true);    
                $column1 = new EJ\Kanban\Column();
                $column1 ->key("InProgress")->headerText("In Progress")->showAddButton(true); 
                $column2 = new EJ\Kanban\Column();
                $column2 ->key("Close")->headerText("Done")->showAddButton(true);      
                $fields = new EJ\Kanban\Field();
                $fields ->content("Summary")->primaryKey("Id");
                $columns = array( 
                $column,$column1,$column2
                );    
                $customToolbarItem = new EJ\Kanban\CustomToolbarItem();
                $customToolbarItem ->template("#delete");
                $customToolbarItems = array($customToolbarItem);
                $editItem = new EJ\Kanban\EditItem();
                $editItem->field("Id");
                $editItem1 = new EJ\Kanban\EditItem();
                $editItem1->field("Status")->editType("dropdownedit");
                $editItem2 = new EJ\Kanban\EditItem();
                $editItem2->field("Assignee")->editType("dropdownedit");
                $editItem3 = new EJ\Kanban\EditItem();
                $editItem3->field("Summary")->editType("textarea");
                $editSetting = new EJ\Kanban\EditSetting();
                $editSetting->allowEditing(true)->allowAdding(true)->editItems(array($editItem,$editItem1,$editItem2,$editItem3));
                echo $kanban ->columns($columns)->toolbarClick("toolbarClick")->customToolbarItems($customToolbarItems)->editSettings($editSetting)->dataSource($dataManager)->fields($fields)->keyField("Status")->render();
            ?>
        </body>
    </html>

Output Images:

Before dragging