Sigma Grid 2.1 Tutorial 3 - Create, Read, Update and Delete on database

[steven]

In tutorial 2, we have talked about how to retrieve data from server/database and display them page by page. It seems to be insteresting. You don't think so? All right, let me let it be this time.
Let's go over some knowledges about stuff transfered between grid and server. Do you still memeber paging process : When a paging button(first, previous, next and last) is pressed,  sigma grid will send paging relevant info to server.
So does saving data. When save button pressed, sigma grid will send deleted, appended and updated rows to server. The data structure looks like

{   action : 'save',   fieldsName : [..],   insertedRecords : [..],   updatedRecords : [..],   deletedRecords : [..],   recordType : "object"/"array",   parameters : {} }

As you can see, those deleted, appended and updated rows are right there.

[steven]

Do you remember how to convert these pretty string into php object? Right, we have a very powerful function named json_decode from PHP5.20. If you are using older version, you may need a third-party library, for example, Zend JSON at http://framework.zend.com/manual/en/zend.json.html. As we know, all the posted info is stored by post variable named _gt_json. The following two lines help us get the deleted records.
$json=json_decode(stripslashes($_POST["_gt_json"])); $deletedRecords = $json->{'deletedRecords'};
Here $deletedRecords is array of array/hash(JSON object), which depends on what format you feed grid with. Take hash for example, you can get all the order numbers of deleted rows with following lines.
$params = array(); $deletedRecords = $json->{'deletedRecords'}; foreach ($deletedRecords as $value){     $params[] = $value->order_no; }

[steven]

The thing left is to operate database with PHP API. To be more professional, I recomend you using a PHP framework, such as CakePHP, to make your application safe and stable. But in this tutorial, I still write bare sql to achieve it. Here are the whole code to delete rows.
$deletedRecords = $json->{'deletedRecords'}; foreach ($deletedRecords as $value){   $params[] = $value->order_no; } $sql = "delete from orders where order_no in (" . join(",", $params) . ")"; if(mysql_query($sql)==FALSE){   $errors .= mysql_error(); }
It's really easy, isn't?

[steven]

By far, we have two php files, one for loading by page(see tutorial2), the other for deleting. The last but not least thing we need to do is to make saveUrl of grid point to the php file.
var gridOption={     loadURL : 'mydemo2.php',     saveURL : 'mydemo3.php' }
In fact, we could merge those two ones into a single file. But how can we determine whether grid is ask for data or posting data for saving? Ah, good question. We have a field reserved for this. See the definition of _gt_json and you will see first field is action. It could be load or save. 
if($json->{'action'} == 'load'){   //read data from database and feed grid }else if($json->{'action'} == 'save'){   //save deleted/added/updated data to database }

[cpb.sos]

Please.

I need a php structure sample to save data grid.

Thanks

[steven]

please see the structure at
http://www.sigmawidgets.com/products/sigma_grid2/docs/dataexchange.html

[Darshan]

 ???I faced one problem here..
If i have 3-4 editable columns and when i edit one column and immediately i change other at that time it cant save the first column and directly goes to change for second column..
So, what i can do here to solve this?
Thanks in advance..........

[godfather21]

i have one problem,when i press save it does not save and it returns the old value.what can i do?

[incaib]

Fabulous only one day put sigma grid in my site on with this tutorials. But i have a little problem. The tutorial three say over update and add but when i add a line in my table i can't edit text in blank line. And if select a grid with data i can't edit for update. I put on the code of example for delete. I need make all code for update and add? or exist for work in the grind in realtime? sorry my english. Good weekend all from Mallorca(Spain) IncaIB Pep Sanchez

SOLUTION ADD:

First when make column for example if you like edit column nombre add in the line this editor:{type:"text"}

var colsOption = [
     {id: 'nombre' , header: "Name" , width :50, editor:{type:"text"} },
}

and in this moment on edit all columns of Name and the code in php for save if you update any grid is this for my work reedit for your grid.

<?php header('Content-type:text/javascript;charset=UTF-8'); $conexion=mysql_connect("62.149.150.131","Sql449024","9467da7d") or     die("Could not connect: " . mysql_error()); mysql_select_db("Sql449024_2",$conexion) or     die("Could not select database: " . mysql_error()); $json=json_decode(stripslashes($_POST["_gt_json"])); $deletedRecords = $json->{'deletedRecords'}; $updatedRecords = $json->{'updatedRecords'}; if($updatedRecords) { $a=0; $params = array(); $ids = array(); foreach ($updatedRecords as $value){ $ids[] = $value->id;     $params[] = $value->direccion_ip; $sql = "update visitas SET direccion_ip=\"".$params[$a]."\" where id=\"".$ids[$a]."\""; if(mysql_query($sql)==FALSE){   $errors .= mysql_error(); } $a=$a+1; } } if($deletedRecords) { $params = array(); foreach ($deletedRecords as $value){     $params[] = $value->id; } $sql = "delete from visitas where id in (" . join(",", $params) . ")"; if(mysql_query($sql)==FALSE){   $errors .= mysql_error(); } } ?>


If any friend like test how functionally is this in my server test is
http://www.ibsegundamano.es/admin/sigma/mydemo1.html
You can delete two or three or more lines and edit many line in one time and save at finish and go good thank's for this fabulous job all people. Bye.