SQL Insert / Update / Delete for Inline Grid

[mjg77025]

Does anyone have any code examples for inserting / updating / deleting records in SQL for the Inline Editing Grid?

Thanks!

[paramvir]

Session is used in the inline editing examples.

you have to replace the session store with database store.

For example in this demo http://paramquery.com/pro/demos/editing you have to insert a new record into table when $_GET[ "pq_add"] is received on the server.
1) if( isset($_GET["pq_add"]))   

the field values are in the $_GET variable as $_GET["ProductName"], $_GET["QuantityPerUnit"], etc.

http://www.w3schools.com/php/php_mysql_insert.asp

Then you have to get the primary key of last inserted record with SELECT LAST_INSERT_ID(); and return it back to grid.

http://www.w3schools.com/php/php_mysql_insert_lastid.asp

echo "{\"recId\": \"" . primary_key_value . "\"}";

2) and update the record when $_GET[ "pq_update" ]

else if( isset($_GET["pq_update"]))   

http://www.w3schools.com/php/php_mysql_update.asp

3) and delete the record when $_GET["pq_delete"]

else if( isset($_GET["pq_delete"])) 

http://www.w3schools.com/php/php_mysql_delete.asp


Please let know if you need any further help on construction of queries.

[mjg77025]

Thanks for the info.

I managed to get add, delete, and update to work however the page does not refresh after the operation.

What am I missing?

[paramvir]

Please check whether the changes persist in the database.

Does the refresh button in the paging toolbar in your grid able to refresh data?

Then put a breakpoint or an alert button in the success callback of both deleteRow and update functions.

[mjg77025]

The changes do persist in the database.   If I refresh the page the changes show.

How would I put a break point or alert in the callback?

Here is my code for the delete function...

else if( isset($_GET["pq_delete"]))   
{
    session_start();
    $productid = $_GET["productid"];
    $products = json_decode($_SESSION["products"], true);   
    foreach($products as $i => $product){
        if($product["productid"] == $productid){           
            unset($products[$i]);
        }       
    }
   
    $sqldel = "delete from products where productid='$productid'";
   $conndel = new PDO("pgsql:host=localhost;dbname=xxxxx","xxxxx","xxxxx");
    $stmtdel = $conndel->prepare($sqldel);
   $stmtdel->execute();
   
    $_SESSION["products"]= json_encode($products);   
    echo "{\"result\": \"success\"}";
    $conndel = null;
}   

[paramvir]

I meant putting debugger; statement in the javascript $.ajax success function callbacks to check whether they are being called correctly.

When you make changes in the grid, do you see the cells dirty ( red icon in the corner). If not, then check whether you have mentioned dataModel.recIndx ( primary key) correctly.

Also there is no need for you to save the products state in $_SESSION variable.

[mjg77025]

Got it!   Removed all of the excess session and array code and made it work for all three!  Thanks!

Example:

else if( isset($_GET["pq_delete"]))   
{
    session_start();
    $productid = $_GET["productid"];
   
    $sqldel = "delete from products where productid='$productid'";
   $conndel = new PDO("pgsql:host=localhost;dbname=fredhost","xxxx","xxxx");
    $stmtdel = $conndel->prepare($sqldel);
   $stmtdel->execute();
     
    echo "{\"result\": \"success\"}";
    $conndel = null;
}