Start Building Professional
Web Apps Today

Cross tables Data Update Functionality on render_sql()

I have a problem with updating operation at datagrid which is from two tables content.

I used render_sql by joining two tables together so that the datagrid fields are cross two tables.

At server, I used $gridConn->event->attach("beforeUpdate",doBeforeUpdate) before render_table at "else" block to declare which table should be update with grid data. The problem is that the update is not fully successful. Because the value of field were updated at DB by the value of column at datagrid not exactly match, even I explicitly declare which field should be updated by which column.

And I do not know why. I should mention I am using Standard Version DHTMLX 2.1 suite.

    if ($gridConn->is_select_mode()) {
        $gridConn->render_sql("Select c.name catname,p.productID,p.categoryID,p.name,p.product_code,p.brief_description,p.Price,p.in_stock,p.items_sold,p.enabled,p.description from ss_products p left join ss_categories c on p.categoryID=c.categoryID", "p.productID","catname,p.categoryID,productID,p.name,product_code,brief_description,Price,in_stock,items_sold,enabled,description");
"p.productID","catname,p.categoryID,p.name,p.product_code,p.brief_description,p.Price,p.in_stock,p.items_sold,p.enabled,p.description");
    } else {
        $gridConn->event->attach("beforeUpdate",doBeforeUpdate);
$gridConn->render_table("ss_products","productID","categoryID,name,product_code,brief_description,Price,in_stock,items_sold,enabled,description");
    }    


function doBeforeUpdate($action)
{
    $gr_id = $action->get_id();
    $db = new db_class;
    $success=mysql_query("UPDATE ss_products SET product_code='{$action->get_value('product_code')}',brief_description='{$action->get_value('brief_description')}' WHERE productID={$action->get_value('productID')}");

    if ($success) {
        $action->success();
    } else {
        //include XML Header (as response will be in xml format)
        if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
            header("Content-type: application/xhtml+xml");
            header("Content-type: text/xml");
        }
        echo("<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>\n");
?>
        <!-- response xml -->
        <data>
<?php
            print("<action type='error' sid='".$gr_id."' tid='".$gr_id."'>SQL query error</action>");
?>
        </data>
<?php
    }
}

You are using different order of column in select and update render commands. As result grid incorrectly link incoming data to DB fields.

You can change your code as


$gridConn->render_sql("Select c.name catname,p.productID,p.categoryID,p.name,p.product_code,p.brief_description,p.Price,p.in_stock,p.items_sold,p.enabled,p.description from ss_products p left join ss_categories c on p.categoryID=c.categoryID", "p.productID","catname,p.categoryID,productID,p.name,product_code,brief_description,Price,in_stock,items_sold,enabled,description"); 
"p.productID","catname,p.categoryID,p.name,p.product_code,p.brief_description,p.Price,p.in_stock,p.items_sold,p.enabled,p.description");
   
$gridConn->event->attach("beforeUpdate",doBeforeUpdate); 
 function doBeforeUpdate($action)
{
   $success=mysql_query("UPDATE ss_products SET product_code='{$action->get_value('product_code')}',brief_description='{$action->get_value('brief_description')}' WHERE productID={$action->get_value('productID')}"); 
   if ($success) { 
        $action->success(); 
   } else { 
        $action->set_response_text("SQL query error");
        $action->error();
   }
}