I would like to see a complete example of treegrid in javascript with connections to "mysql" bd which have fuciones crud | jQuery Forums

JS Jonesherine Stephen Syncfusion Team September 12, 2016 09:31 AM UTC

Hi Andres,

Thanks for contacting Syncfusion support

Query 1 I would like to see a complete example of treegrid in javascript with connections to "mysql" db which have functions crud

Solution: We have prepared the workaround and rendered the TreeGrid with mySql database.

And by using the “actionComplete” and “endEdit” client side events in TreeGrid we can update the data base during insert, update and delete action.

Please find the code example below:

[aspx]

$(function () {

var dataManager = ej.DataManager({

url: "/TreeGrid.aspx/GetTreeGridData",

adaptor: "UrlAdaptor"

});

$("#treeGrid").ejTreeGrid({

dataSource: dataManager,

endEdit: function (args) {

//To update after editing TreeGrid

var editedRecord = args.data.item;

PageMethods.UpdateIt(editedRecord);

},

actionComplete: function ActionComplete(args) {

if (args.requestType === 'addNewRow') {

//Newly Added Record is obtained here , which can be updated to database

addedRecord = args.addedRow;

PageMethods.AddIt(addedRecord);

}

else if (args.requestType === 'delete') {

var data = args.data;

var deletedRecord = data.item; //This is the deleted item.

PageMethods.DeleteIt(deletedRecord);

//If deleted item has child records, we need to delete that too

if (data.hasChildRecords) {

deleteChildRecords(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 deletedChildRecord = currentRecord.item; //This is the deleted child item.

PageMethods.DeleteIt(deletedChildRecord);

//If the above deleted child record has child records, then we need to delete that too.

if (currentRecord.hasChildRecords) {

deleteChildRecords(currentRecord);

}

Please find the code example below for sever side methods to render the TreeGrid with mySql database

[aspx.cs]

//we need to pass the servername,database name, username and password to connect the mySql server

string con_string = "Server=localhost;Database=tgrid;Uid=root;Pwd=Treegrid";

[WebMethod]

public void Update(TaskData Task)

{

MySqlConnection con;

MySqlCommand command;

con = new MySqlConnection(con_string);

command = con.CreateCommand();

command.CommandText = "UPDATE gantt SET taskName='" + Task.Name + "', startDate='" + Task.StartDate + "', duration='" + Task.Duration + "' WHERE taskID='" + Task.Id + "'";

con.Open();

command.ExecuteNonQuery();

con.Close();

}

public void Add(TaskData Task)

{

MySqlConnection con;

MySqlCommand command;

con = new MySqlConnection(con_string);

command = con.CreateCommand();

command.CommandText = "INSERT INTO gantt SET taskID='" + Task.Id + "', taskName='" + Task.Name + "', startDate='" + Task.StartDate + "',duration='" + Task.Duration + "',parentID='"+Task.ParentId + "'";

con.Open();

command.ExecuteNonQuery();

con.Close();

}

public void Delete(TaskData Task)

{

MySqlConnection con;

MySqlCommand command;

con = new MySqlConnection(con_string);

command = con.CreateCommand();

command.CommandText = "DELETE FROM gantt WHERE taskID='" + Task.Id + "'";

con.Open();

command.ExecuteNonQuery();

con.Close();

}

We have also prepared the sample to render the TreeGrid using mySql database with ASP as the code behind. Please find the sample from below location:

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/remotewithmysql-treegrid-2089062249

Please find our Knowledge base to render the TreeGrid with MySql Database using PHP.

KB: https://www.syncfusion.com/kb/7038/how-to-perform-crud-operation-in-treegrid-using-php

Disclaimer: Please refer the MySql.Data.dll to render the TreeGrid with mysql data base.

Query2: I perform this request because I cannot find good documentation on this control

Solution:

We are currently enhancing our online documentation. It will available at the end of October 2016.

Please let us know if you require further assistance on this.
Regards,
Jone sherine P S

AP andres patiño replied to Jonesherine Stephen September 12, 2016 11:35 PM UTC

Hi Andres,

Thanks for contacting Syncfusion support

Query 1 I would like to see a complete example of treegrid in javascript with connections to "mysql" db which have functions crud

Solution: We have prepared the workaround and rendered the TreeGrid with mySql database.

And by using the “actionComplete” and “endEdit” client side events in TreeGrid we can update the data base during insert, update and delete action.

Please find the code example below:

[aspx]

<script type="text/javascript">

        $(function () {

            var dataManager = ej.DataManager({

                url: "/TreeGrid.aspx/GetTreeGridData",

                adaptor: "UrlAdaptor"

            });

            $("#treeGrid").ejTreeGrid({

                dataSource: dataManager,

                endEdit: function (args) {

                    //To update after editing TreeGrid

                    var editedRecord = args.data.item;

                    PageMethods.UpdateIt(editedRecord);

                },

                actionComplete: function ActionComplete(args) {

                    if (args.requestType === 'addNewRow') {

            //Newly Added Record is obtained here , which can be updated to database

                        addedRecord = args.addedRow;

                        PageMethods.AddIt(addedRecord);

                    }

                    else if (args.requestType === 'delete') {

                        var data = args.data;

                        var deletedRecord = data.item; //This is the deleted item.

                        PageMethods.DeleteIt(deletedRecord);

                        //If deleted item has child records, we need to delete that too

                        if (data.hasChildRecords) {

                           deleteChildRecords(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 deletedChildRecord = currentRecord.item; //This is the deleted child item.

                PageMethods.DeleteIt(deletedChildRecord);

                //If the above deleted child record has child records, then we need to delete that too.

                if (currentRecord.hasChildRecords) {

                    deleteChildRecords(currentRecord);

                }

            }

        }

Please find the code example below for sever side methods to render the TreeGrid with mySql database

[aspx.cs]

//we need to pass the servername,database name, username and password to connect the mySql server

        string con_string = "Server=localhost;Database=tgrid;Uid=root;Pwd=Treegrid";

        [WebMethod]

        public void Update(TaskData Task)

        {

            MySqlConnection con;

            MySqlCommand command;

            con = new MySqlConnection(con_string);

            command = con.CreateCommand();

            command.CommandText = "UPDATE gantt SET taskName='" + Task.Name + "', startDate='" + Task.StartDate + "', duration='" + Task.Duration + "' WHERE taskID='" + Task.Id + "'";

            con.Open();

            command.ExecuteNonQuery();

            con.Close();

                    }

        public void Add(TaskData Task)

        {

            MySqlConnection con;

            MySqlCommand command;

           con = new MySqlConnection(con_string);

           command = con.CreateCommand();

           command.CommandText = "INSERT INTO gantt SET taskID='" + Task.Id + "', taskName='" + Task.Name + "', startDate='" + Task.StartDate + "',duration='" + Task.Duration + "',parentID='"+Task.ParentId + "'";

           con.Open();

           command.ExecuteNonQuery();

           con.Close();

        }

        public void Delete(TaskData Task)

        {

            MySqlConnection con;

            MySqlCommand command;

            con = new MySqlConnection(con_string);

            command = con.CreateCommand();

            command.CommandText = "DELETE FROM gantt WHERE taskID='" + Task.Id + "'";

            con.Open();

            command.ExecuteNonQuery();

            con.Close();

        }

We have also prepared the sample to render the TreeGrid using mySql database with ASP as the code behind. Please find the sample from below location:

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/remotewithmysql-treegrid-2089062249

Please find our Knowledge base to render the TreeGrid with MySql Database using PHP.

KB: https://www.syncfusion.com/kb/7038/how-to-perform-crud-operation-in-treegrid-using-php

Disclaimer: Please refer the MySql.Data.dll to render the TreeGrid with mysql data base.

Query2: I perform this request because I cannot find good documentation on this control

Solution:

We are currently enhancing our online documentation. It will available at the end of October 2016.

Please let us know if you require further assistance on this.
Regards,
Jone sherine P S

I'm Giando based on the example of the link

https://www.syncfusion.com/kb/7038/how-to-perform-crud-operation-in-treegrid-using-php

I can delete, create a new record and update perfectly

but I have the following problems:

- To create a new row does not show me the "taskid" (autoincrement) and if I press the esc key to exit, I delete the row

- When I drag a row to acerla daughter of another, delete my record

JS Jonesherine Stephen Syncfusion Team September 13, 2016 11:00 AM UTC

Hi Andres,

Please find the response below:

Query

Response

To create a new row does not show me the "taskid" (autoincrement)

At present there is no support to auto increment the taskId while we adding a new record. For this we have already logged a feature request regarding this. It will be implemented in any of our upcoming main release.

if I press the esc key to exit, I delete the row

1. As per the current behavior, if we press the “esc” key before saving the newly added record then it deletes the newly added record.

2. While rendering the TreeGrid with mySql database the taskId should be unique.

3. If incase another record having the taskId similar to the newly added record’s taskId that record will also be deleted along with newly added record after pressing “esc” key.

When I drag a row to acerla daughter of another, delete my record

We have prepared the work around and updated the mySql database during drag and drop action using “rowDrag” and “rowDragStop” client side events.

Please find the code example below:

[aspx]

var dragStart;

$(function () {

$("#treeGrid").ejTreeGrid({

allowDragAndDrop:true,

rowDrag: function (args) {

dragStart = true;

},

rowDragStop: function (args) {

dragStart = false;

PageMethods.UpdateIt(args.draggedRow.item);

},

actionComplete: function ActionComplete(args) {

//

else if (args.requestType === 'delete') {

if (dragStart != true) {

var data = args.data;

var deletedRecord = data.item; PageMethods.DeleteIt(deletedRecord);

if (data.hasChildRecords) {

deleteChildRecords(data);

}

});

We have also prepared the sample for your reference. Please find the sample from the below location.

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/remotewithmysql-drag705258346

Regards,
Jone sherine P S

AP andres patiño September 14, 2016 09:50 PM UTC

the example can be with mysql and php thanks

JS Jonesherine Stephen Syncfusion Team September 15, 2016 11:18 AM UTC

Hi Williams,

We have prepared the work around and rendered the TreeGrid sample with “mySql” database using PHP.

And also performed the CRUD operations by using the TreeGrid client side events “endEdit” “actionComplete”,”rowDragStop”

and using ajax post we have updated the data base in server side

Please find the code example below

Server side

Client side

DataBinding:

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);

}

$(function () {

var data = ej.DataManager({

url:"http://localhost:10080/ phpsample /crud.php/?action=getData", });

$("#treegrid").ejTreeGrid({

dataSource: data,

});

Update:

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);

}

$(function () {

$("#treegrid").ejTreeGrid({

rowDrag: function (args) {

dragStart = true;

},

rowDragStop: function (args) {

dragStart = false;

var encoded = JSON.stringify(args.draggedRow.item);

var data = encoded;

$.ajax({

type: "POST",

url: 'http://localhost:10080/phpsample/crud.php/?action=update',

data: data,

error: function () {

alert("error")

}

});

},

endEdit: function (args) {

if (args.requestType == "update") {

var encoded = JSON.stringify(args.currentValue);

var data = encoded;

}

else {

var encoded = JSON.stringify(args.data.item);

var data = encoded;

}

$.ajax({

type:"POST", url:'http://localhost:10080/phpsample/crud.php/?action=update',

data: data,

success: function (data) {

alert(data);

},

error: function () {

alert("error")

}

});

},

});

Add:

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'];

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);

}

$(function () {

$("#treegrid").ejTreeGrid({

actionComplete: function (args) {

if (args.requestType == "addNewRow") {

var encoded = JSON.stringify(args.addedRow);

var data = encoded;

$.ajax({

type: "POST",

url: 'http://localhost:10080/phpsample/crud.php/?action=add',

data: data,

success: function (data) {

alert(data);

},

error: function () {

alert("error")

}

});

}

});

Delete:

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);

}

$(function () {

$("#treegrid").ejTreeGrid({

actionComplete: function (args) {

if (args.requestType == "delete"){

if (dragStart != true) {

var encoded = JSON.stringify(args.data.item);

var data = encoded;

$.ajax({

type: "POST",

url: 'http://localhost:10080/phpsample/crud.php/?action=delete',

data: data,

success: function (data) {

alert(data);

},

error: function () {

alert("error")

}

});

if (args.data.hasChildRecords) { deleteChildRecords(args.data);

}

});

We have also prepared the sample for your reference .Please find the sample from below location:

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/phpsample-treegrid1744410994

Please let us know if you require further assistance on this,

Thanks,

Jone sherine P S

JS Jonesherine Stephen Syncfusion Team September 15, 2016 11:53 AM UTC

Hi Andres,

Please ignore the previous update.

We have prepared the work around and rendered the TreeGrid sample with “mySql” database using PHP. And also performed the CRUD operations by using the TreeGrid client side events “endEdit” “actionComplete”,”rowDragStop” and using ajax post we have updated the data base in server side

Please find the code example below

Server side

Client side

DataBinding:

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);

}

$(function () {

var data = ej.DataManager({

url:"http://localhost:10080/ phpsample /crud.php/?action=getData", });

$("#treegrid").ejTreeGrid({

dataSource: data,

});

Update:

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);

}

$(function () {

$("#treegrid").ejTreeGrid({

rowDrag: function (args) {

dragStart = true;

},

rowDragStop: function (args) {

dragStart = false;

var encoded = JSON.stringify(args.draggedRow.item);

var data = encoded;

$.ajax({

type: "POST",

url: 'http://localhost:10080/phpsample/crud.php/?action=update',

data: data,

error: function () {

alert("error")

}

});

},

endEdit: function (args) {

if (args.requestType == "update") {

var encoded = JSON.stringify(args.currentValue);

var data = encoded;

}

else {

var encoded = JSON.stringify(args.data.item);

var data = encoded;

}

$.ajax({

type:"POST", url:'http://localhost:10080/phpsample/crud.php/?action=update',

data: data,

success: function (data) {

alert(data);

},

error: function () {

alert("error")

}

});

},

});

Add:

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'];

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);

}

$(function () {

$("#treegrid").ejTreeGrid({

actionComplete: function (args) {

if (args.requestType == "addNewRow") {

var encoded = JSON.stringify(args.addedRow);

var data = encoded;

$.ajax({

type: "POST",

url: 'http://localhost:10080/phpsample/crud.php/?action=add',

data: data,

success: function (data) {

alert(data);

},

error: function () { alert("error")

}

});

}

});

Delete:

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);

}

$(function () {

$("#treegrid").ejTreeGrid({

actionComplete: function (args) {

if (args.requestType == "delete"){

if (dragStart != true) {

var encoded = JSON.stringify(args.data.item);

var data = encoded;

$.ajax({

type: "POST",

url: 'http://localhost:10080/phpsample/crud.php/?action=delete',

data: data,

success: function (data) {

alert(data);

},

error: function () { alert("error")

}

});

if (args.data.hasChildRecords) { deleteChildRecords(args.data);

}

});

We have also prepared the sample for your reference .Please find the sample from below location:

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/phpsample-treegrid1744410994

Please let us know if you require further assistance on this,

Thanks,

Jone sherine P S