Category / Section
How to perform CRUD operations in Gantt using PHP
3 mins read
It is possible to perform CRUD operations in Gantt to My SQL database using PHP.
Data Binding:
Using ejDataManager we can link and get the server data from PHP file. In the following code example, we have taken the JSON data from server using getData method to pass it to Gantt control.
<script type="text/javascript">
$(function () {
var data = ej.DataManager({
url: " http://localhost:10080/GanttPHP/crud.php/?action=getData",
});
$("#gantt").ejGantt({
dataSource: data,
});
});
</script>
Server side method to retrieve the data from database
function getData(){
//connection to the database
$dbhandle = mysql_connect("localhost", "root", "Treegrid");
$selected = mysql_select_db("tgrid",$dbhandle);
//execute the SQL query and return records
$result = mysql_query("SELECT taskID, taskName,startDate, endDate,duration,Progress,parentID FROM gantt");
//fetch tha data from the database
$emparray=array();
while ($row = mysql_fetch_assoc($result)) {
if($row['parentID']== ""){
$row['parentID']= null;
}
$emparray[]=$row;
}
echo json_encode($emparray);
}
Add operation:
actionComplete client side event will be triggered while adding new row in Gantt. Using this event, we can insert new record into the database
The code snippet to add new item to database
<script type="text/javascript">
$("#gantt").ejGantt({
actionComplete: function (args) {
//To update the database while adding new record
if (args.requestType == "save" && args.addedRecord) {
var encoded = JSON.stringify(args.addedRecord.item);
var data = encoded;
$.ajax({
type: "POST",
url:'http://localhost:10080/GanttPHP/crud.php/?action=add',
data: data,
success: function (data) {
alert(data);
},
error: function () {
alert("error")
}
});
}
}
});
});
</script>
Server side method to add new task item into the database
function add(){
$conn = new mysqli("localhost", "root", "Treegrid","tgrid");
$json_del=file_get_contents("php://input");
$obj_del = json_decode($json_del,true);
$key=(int)$obj_del['taskID'];
$name=(string)$obj_del['taskName'];
$startDate=(string)$obj_del['startDate'];
$endDate=(string)$obj_del['endDate'];
$duration=(string)$obj_del['duration'];
$Progress=(string)$obj_del['Progress'];
//To update the parentID in database while adding as a child
if((string)$obj_del['parentID']){
$parentID=(string)$obj_del['parentID'];
echo json_encode($parentID);
$retval = mysqli_query($conn,"INSERT INTO gantt (taskID,taskName,startDate,endDate,duration,Progress,parentID)
VALUES ('$key', '$name','$startDate','$endDate','$duration','$Progress','$parentID')");
echo json_encode($retval); }
else{
$retval = mysqli_query($conn,"INSERT INTO gantt (taskID, taskName,startDate,endDate,duration,Progress)
VALUES ('$key', '$name','$startDate','$endDate','$duration','$Progress')");
echo json_encode($retval);
}
}
Update operation:
The endEdit client side event will be triggered while performing cellEdit in Gantt. Using this event, we can update the database. And for dialog editing we need to update the database using actionComplete event
The code snippet to update the database after editing:
<script type="text/javascript">
$(function () {
$("#gantt").ejGantt({
endEdit: function (args) {
//To update the database on cellEditing
var id = args.data.taskId;
var encoded = JSON.stringify(args.data.item, replacer);
var data = encoded;
$.ajax({
type: "POST",
url: 'http://localhost:10080/GanttPHP/crud.php/?action=update',
data: data,
success: function (data) {
alert(data);
},
error: function () {
alert("error")
}
});
},
//To update the database on dialogEdit,taskbar edit,indent,outdent
actionComplete: function (args) {
if (args.requestType == "indent" || args.requestType == "outdent" || args.requestType == "recordUpdate") {
var encoded = JSON.stringify(args.data.item, replacer),
data = encoded;
$.ajax({
type: "POST",
url: 'http://localhost:10080/GanttPHP/crud.php/?action=update',
data: data,
success: function (data) {
alert(data);
},
error: function () {
alert("error")
}
})
}
});
});
function replacer(key, value) {
if (key == "startDate" || key == "endDate") {
var ganttObj = $("#gantt").data("ejGantt");
//To convert the date to required dateFormat
return ej.format(new Date(value), ganttObj.model.dateFormat);
} else {
return value;
}
}
</script>
Server side method to update the database
function update(){
$conn = new mysqli("localhost", "root", "Treegrid","tgrid");
$json_del=file_get_contents("php://input");
$obj_del = json_decode($json_del,true);
$key=(int)$obj_del['taskID'];
$name=(string)$obj_del['taskName'];
$startDate=(string)$obj_del['startDate'];
$endDate=(string)$obj_del['endDate'];
$duration=(string)$obj_del['duration'];
$Progress=(string)$obj_del['Progress'];
if((string)$obj_del['parentID']){
$parentID=(string)$obj_del['parentID'];
echo json_encode($parentID);
$retval = mysqli_query($conn,"UPDATE gantt SET taskName='$name',startDate='$startDate',endDate='$endDate',duration='$duration',Progress='$Progress',parentID='$parentID' WHERE taskID=$key");
echo json_encode($retval);
}
else{
$parentID=null;
$retval = mysqli_query($conn,"UPDATE gantt SET taskName='$name',startDate='$startDate',endDate='$endDate',duration='$duration',Progress='$Progress',parentID='$parentID' WHERE taskID=$key");
echo json_encode($retval);
}
}
Delete operation:
The actionComplete client side event will be triggered while deleting any row in Gantt. Using this event, we can remove the existing record from the database
The code snippet to remove the record from database:
<script type="text/javascript">
$(function () {
$("#gantt").ejGantt({
actionComplete: function (args) {
if (args.requestType == "delete") {
var id = args.data.taskId;
var encoded = JSON.stringify(args.data.item);
var data = encoded;
$.ajax({
type: "POST",
url: 'http://localhost:10080/GanttPHP/crud.php/?action=delete',
data: data,
success: function (data) {
alert(data);
},
error: function () {
alert("error")
}
});
if (args.data.hasChildRecords) {
deleteChildRecords(args.data);
}
}
});
});
//Delete inner level child records
function deleteChildRecords(record) {
var childRecords = record.childRecords,
length = childRecords.length,
count, currentRecord;
for (count = 0; count < length; count++) {
currentRecord = childRecords[count];
var encoded = JSON.stringify(currentRecord.item);
var data = encoded;
$.ajax({
type: "POST",
url: 'http://localhost:10080/GanttPHP/crud.php/?action=delete',
data: data,
success: function (data) {
alert(data);
},
error: function () {
alert("error")
}
});
if (currentRecord.hasChildRecords) {
deleteChildRecords(currentRecord);
}
}
}
</script>
Please find the server side method to remove the record from database
function delete()
{
$conn = new mysqli("localhost", "root", "Treegrid","tgrid");
$json_del=file_get_contents("php://input");
echo json_encode($json_del);
$obj_del = json_decode($json_del,true);
echo json_encode($json_del);
$key=(int)$obj_del['taskID'];
echo json_encode($key);
$res1 = $conn->query("DELETE FROM `gantt` WHERE taskID=$key");
$success=array("Deleted"=>$key);
echo json_encode($success);
echo json_encode($res1);
}
A Gantt sample in PHP with CRUD operations is available in the following link,
