Start Building Professional
Web Apps Today

Troubles inserting a new record in the grid

I have to use my own update function because on the client side the date fields are in the form dd/mm/yyyy and the MySQL requieres these dates to be in the YYYY/MM/DD format.

The update function works pretty good but the problem arises when a new record is inserted. Using the default insert function in the client side, after the record is saved, apears the information introduced by the user but when the grid is reloaded the information dissapears.
I've tried making my own insert function, but that it crashes because I'm not able to recover the ID (the autonumeric key in my table that in my case is called proforma_id).

Can you, please, give me any idea or a code sample to see the right way to do it?
Next you can find the code I've wrotten.

I'm using the dhtmlx Suite 2.1 open source.


Many thanks in advance,


Antoni


<?php
    require_once("db_login.php");
    require_once("./codebase/grid_connector.php");
    require_once("funcs_aux_tables.php");
    require_once("Log.php");
    
    $log = Log::singleton('file', './logs/proforma.log', 'proforma');
    
    $aNomCols = array("proforma_id","codi_proforma","factura_id","data_emissio","client_id","id_estat_vcli","id_venedor","data_cobro_artek","tipus_IVA","limit_pagament");
    
    $table = "proforma";
    $key = "proforma_id";
    
    
    $sql = "SELECT proforma_id,codi_proforma,factura_id,date_to_ddmmaaaa(data_emissio) as data_emissio";
    $sql .= ",client_id,id_estat_vcli,id_venedor,date_to_ddmmaaaa(data_cobro_artek) as data_cobro_artek";
    $sql .= ",tipus_IVA,limit_pagament,tara_proforma(proforma_id) AS total_tara, transport_proforma(proforma_id) AS total_transport FROM ".$table;
    
    $i = 0;
    foreach ($aNomCols as $value) {
        if ($i == 0){
             $cols_write = $value;
             $cols_select = $value;
        }else{
            $cols_write .= ','.$value;
            $cols_select .= ','.$value;
        }
        $i++;

    }

    $cols_select .= ',total_tara,total_transport';
    
    $res=mysql_connect($db_host,$db_username,$db_password);
    mysql_select_db($db_database);

    $grid = new GridConnector($res);
    $grid->event->attach("beforeUpdate",doBeforeUpdate);
    $grid->event->attach("beforeInsert",doBeforeInsert);
    $grid->event->attach("beforeDelete",doBeforeDelete);
    $grid->enable_log("./logs/dbmantproforma.log"); // Nomes escriu en el log en cas d'error.
    $grid->dynamic_loading(100);
    
    if($grid->is_select_mode()){        //code for loading data
        $grid->render_sql($sql, $key, $cols_select);
    }else{                                //code for other operations - i.e. update/insert/delete
        $grid->render_table($table,$key,$cols_write);
    }
    
    mysql_close($res);
    $log->close();
    
    return;
    
    
        
function doBeforeUpdate($action){
    global $aNomCols, $log;
    
    $log->debug('proforma_id: '.$action->get_value('proforma_id'));

    $camps = null;
    /* $id = $action->get_id(); */
    foreach ($aNomCols as $name) {
     $value = stripslashes($action->get_value($name));
              
     switch($name){
        case 'proforma_id':
            $id = $value;
            break;
        case 'data_emissio':
        case 'data_cobro_artek':
             $value = date_to_aaaammdd($value);
        default:
         if ($camps == null){
                $camps = $name."='".$value."'";
            }else{
                $camps .= ','.$name."='".$value."'";
            }
     }
    }

    $log->debug('id : '.$id);
    $sql = "UPDATE proforma SET ".$camps." WHERE proforma_id=".$id." LIMIT 1";
    $rc = updateProforma($sql);
    if (rc){ // OK
        $log->debug('Update OK');
        $action->success($id);
    }else{     // ERROR
         $log->debug('Error en Update');
        $action->error();
    }

    return;    
}



function doBeforeInsert($action){
    global $aNomCols, $log;
    
    $log->debug('proforma_id: '.$action->get_value('proforma_id'));

    $camps = null;
    /* $id = $action->get_id(); */
    $log->debug('insert get_id() = '.$action->get_id());
    foreach ($aNomCols as $name) {
     $value = stripslashes($action->get_value($name));
     $log->debug('insert '.$name.' = '.$value);         
     switch($name){
        case 'proforma_id':
            $id = $value;
            break;
        case 'data_emissio':
        case 'data_cobro_artek':
             $value = date_to_aaaammdd($value);
        default:
         if ($camps == null){
                $camps = $name."='".$value."'";
            }else{
                $camps .= ','.$name."='".$value."'";
            }
     }
    }

    $log->debug('id : '.$id);
    $sql = "INSERT proforma SET ".$camps;
    $rc = updateProforma($sql);
    if (rc){ // OK
        $log->debug('Update OK');
        $action->success($id);
    }else{     // ERROR
         $log->debug('Error en Update');
        $action->error();
    }

    return;    
}


    
    
function updateProforma($sql){    
    global $db_username, $db_password, $db_host, $db_database, $log;    
    
    $rc = true;     // OK per defecte
    
    try {
        $mdb2 = MDB2::factory("mysql://$db_username:$db_password@$db_host/$db_database");
        $log->debug('Query:'.$sql);
        $affected =& $mdb2->exec($sql);    
        if (PEAR::isError($affected)){
            $rc = false;
            $log->err("updateProforma Error:".$affected->getMessage());    
        }                  
    }catch (Exception $e) {
        $log->err($e->getMessage());
        $rc = false;
    }
    return $rc;    
}    



    
function doBeforeDelete($action){
    global $db_host,$db_username,$db_password,$log;

    
    
    $rc = false; // per defecte la fila no es pot esborrar
    $id = $action->get_id();
    $consulta = "SELECT COUNT(*) AS cnt FROM vehicles WHERE id_proforma_cli = ".$id;
    $conect=mysql_connect($db_host,$db_username,$db_password);
    if ($conect){
        $result = mysql_query($consulta);
        $line = mysql_fetch_array($result, MYSQL_BOTH);
        if ($line){
            if ($line['cnt'] == 0) $rc = true; // la fila es pot esborrar
        }
        mysql_free_result($result);
        mysql_close($conect);
    }
    
    if ($rc){
        if (esborra_id($id)) $action->success(); // la fila es pot esborrar
    }else{
        $action->invalid();
    }

}

    
function esborra_id($id){
    global $db_host,$db_username,$db_password,$table,$key,$log;    
    
    $rc = false;
    $sql = "DELETE FROM ".$table." WHERE ".$key."=".$id;
    $conect=mysql_connect($db_host,$db_username,$db_password);
    if ($conect){
        $rc = mysql_query($sql);    
        mysql_close($conect);
    }

    return $rc;
}


    
?>

>>Can you, please, give me any idea or a code sample to see the right way to do it
Please check if data actually sends to the server side and saves at your data base. You can attach debug version of dhtmlxdataprocessor.js file to check what requests DataProcessor sends to the server side:
<script src="../../codebase/dhtmlxdataprocessor_debug.js"></script>
Please find examples of implementing dhtmlxConnector at your dhtmlxConnector package dhtmlxConnector_php\samples\grid\

Hi,

The problem happens when a new row is inseted.
First of all takes place an INSERT and  apparently finishes OK. I say aparently because the sid and tid are same and I think the tid should be the value of the auto numeric field on the table.
This is the log I've got:


!nativeeditor_status => inserted
SQL code for insert mastered from template
Exec SQL: INSERT proforma SET codi_proforma='',factura_id='',data_emissio='',client_id='',id_estat_vcli='',
          id_venedor='',data_cobro_artek='',tipus_IVA='',limit_pagament=''

Edit operation finished
0 => action:inserted; sid:1251832752806; tid:1251832752806;

Done in 0.00215888023376ms


After the insert an UPDATE takes place with the following result:

!nativeeditor_status => updated
SQL code for update mastered from template
Exec SQL: UPDATE proforma SET codi_proforma='hola',factura_id='',data_emissio='',client_id='',
          id_estat_vcli='',id_venedor='',data_cobro_artek='',tipus_IVA='',limit_pagament=''
          WHERE proforma_id=1251832752806
Edit operation finished
0 => action:updated; sid:1251832752806; tid:1251832752806;

Done in 0.0039598941803ms


As you can see ID (in my case proforma_id) has a wrong value and MySQL doesn't return an error but does nothing.

This is my code:

    $res=mysql_connect($db_host,$db_username,$db_password);
    mysql_select_db($db_database);
    $grid = new GridConnector($res);
    $grid->enable_log("./logs/dbmantproforma.log",true); 
    $grid->dynamic_loading(100);
    $grid->event->attach("beforeDelete",doBeforeDelete);
    $grid->sql->attach("UPDATE","UPDATE ".$table." SET ".$cols_write." WHERE ".$key."={".$key."}");
    $grid->sql->attach("INSERT","INSERT ".$table." SET ".$cols_write);
    $grid->render_sql($sql, $key, $cols_select);
     mysql_close($res);


Looking at your knowledge base I've found a similar issue:


I've tried this adding the following lines to my code:
    $grid->sql->attach("INSERT","INSERT ".$table." SET ".$cols_write);
    $grid->event->attach("afterInsert","updateInsert");
    $grid->render_sql($sql, $key, $cols_select);

function updateInsert($action){
    global $aNomCols, $table, $key, $log;
   
    $tmp = "";
    foreach ($aNomCols as $nom => $valor){
        if ($valor == 'S'){
            if (strlen($tmp) == 0){
                $tmp = $nom."='{".$action->get_value($nom)."}'";
            }else{
                $tmp .= ",".$nom."='{".$action->get_value($nom)."}'";
            }   
        }
    }
    $qry_update = "UPDATE ".$table." SET ".$tmp." WHERE proforma_id='{$action->get_new_id()}'";
    $log->debug('updateInsert: '.$qry_update);
    mysql_query($qry_update);
    unset($tmp);
}

But this function isn't called.  I've had a look at the dhtmlxConnector events and the event afterInsert isn't mentioned.

Please, could you tell me if this problem has a solution?
I've thought using the afterProcessing event, but it's called after insert, update and delete and I don't know a way to know when this event is called after an insert. Do you kow if it is possible?

Thanks a lot for your help.


Antoni

How is the new ID generated for new rows ?
When connector uses auto-generated code for row adding, it is able to fetch new ID automatically, but it is not possible in case of custom sql queries. You need to define , which value to use as new ID 

function my_code($action){
       if ($action->get_status() == "insert"){
                $action->success(new_id_value); //set new ID for the row
       }
}
$grid->event->attach("afterProcessing","my_code");

>>$grid->event->attach("afterInsert","updateInsert");
afterInsert will be available in Connectors 1.0 , current version doesn't support such functionality, but you can use afterProcessing and check the status of operation from it.


Is there some reason why you can't use auto-generated insert query? ( it will resolve all ID related problems ) 

Finally I've succeed arranging the problem.
I've used the event beforeInsert. Inside this function takes place the insert followed by an update. I get the ID with the php mysql_insert_id() function.

Next you can have a look to the code.

$grid->event->attach("beforeInsert",doBeforeInsert);

function doBeforeInsert($action){
    global $aNomCols, $log;

    //$log->debug('--------------------- Entra en doBeforeInsert() -----------');
    $cols_write = getColsWrite($action);
    $sql  = "INSERT proforma SET ".$cols_write;
    $rc = updateProforma($sql);
    if ($rc){  // OK
        $id = mysql_insert_id();
        $sql = "UPDATE proforma SET ".$cols_write." WHERE proforma_id=".$id;
        $rc = updateProforma($sql);
        if ($rc){
            $action->success($id);
        }else{
            $log->alert('doBeforeInsert - Error en Update: '.$sql);
            $action->error();
        }
    }else{      // ERROR
         $log->alert('doBeforeInsert - Error en INSERT: '.$sql);
        $action->error();
    }
}       

The reasons to use my own insert and update queries are:

•   I have to insert/update less columns that in the select
  
•   I have to change the date format from dd/mm/yyyy to yyyy/mm/dd because MySQL requires this format
•   I have to execute the php stripslashes() function before writting in the database. This will avoid sequences of character repetition like //// or ''''' .

On the othe hand, if you don't mind I'd like to make you another question.
In a grid, is it possible to limit the length of the data introduced by the user without having to code for each column? Is there a property or method available to do that?


Thanks a lot for your help.

Antoni

>>In a grid, is it possible to limit the length of the data introduced by the user without having to code for each column? Is there a property or method available to do that?
There is no such inbuild functionality but you can use "onEditCell" event to validate user's input. This event occurs 1-3 times depending on cell's editability. onEditCell event passes the following parameters: 
stage - stage of editing (0-before start[can be canceled if returns false],1- the editor is opened,2- the editor is closed);
rId - id of the row;
cInd - index of the cell;
nValue - new value (only for the stage 2);
oValue - old value (only for the stage 2).
  grid.attachEvent("onEditCell", function(stage,rId,cInd,nValue,oValue){});

During normal edit process event fires 3 times 
cell edition initiated ( stage = 0 )
cell editor activated and ready for input ( stage = 1 )
cell edition finished ( stage = 2 )

The 3rd call can cause different results based on returned value 
{bool}true - confirm edit operation 
{bool}false - deny edit operation ( previous cell value will be restored ) 
{string} or {number} - provided value will be used a new value of the cell instead of one entered by user

Please find example here http://dhtmlx.com/docs/products/dhtmlxGrid/samples/cell_types/pro_maxlength.html