Start Building Professional
Web Apps Today

Dhtmlx Connector Not update after complex SQL query

Hi,

i'm tring to upate a mysql db with Dhtmlx Connector:
The version of Dhtmlx Grid is 2.5:

here is the code:

<?php
    require_once("../dhtmlxSuite/dhtmlxConnector/php/codebase/grid_connector.php");

    $res=mysql_connect("localhost","root","sandbgroup");
    mysql_select_db("hr_mysql");

    $timesheet = new GridConnector($res);
    $timesheet->enable_log("temp.log",true);
    $timesheet->set_encoding("ISO-8859-1");
    $timesheet->dynamic_loading(100);
    
    
    
    $timesheet->render_sql("SELECT hs_hr_attendance.punchin_time, hs_hr_attendance.in_note, hs_hr_attendance.punchout_time, hs_hr_attendance.out_note, hs_hr_attendance.status from hs_hr_attendance INNER JOIN hs_hr_employee ON hs_hr_attendance.employee_id=hs_hr_employee.emp_number WHERE hs_hr_employee.employee_id LIKE" ."'". $_GET["filter"] ."%" ."'", "hs_hr_attendance.attendance_id","punchin_time,in_note,punchout_time,out_note,status");

$timesheet->sql->attach("Update","Update hs_hr_attendance set punchin_time={punchin_time}    ,in_note={in_note}, punchout_time={punchout_time}, out_note={out_note}, status={status} where attendance_id={attendance_id}");

?>

If i change all into render_table all works ok .... in above situation this is the error log:

====================================
Log started, 21/09/2009 09:09:53
====================================

Row data [1253561390_0]

Incorrect field name used: punchin_time

data

DataProcessor object initialized
1253561390_0_gr_id => 1253561390_0
1253561390_0_c0 => 2009-01-22 10:40:20
1253561390_0_c1 => Entr
1253561390_0_c2 => 2009-01-22 18:40:21
1253561390_0_c3 => Uscita
1253561390_0_c4 => 1
1253561390_0_!nativeeditor_status => updated
ids => 1253561390_0

Incorrect field name used: in_note

data

Incorrect field name used: punchout_time

Row data [1253561390_0]

data

Incorrect field name used: out_note

data

Incorrect field name used: punchin_time

Incorrect field name used: status

data

data

Incorrect field name used: in_note

data

UPDATE hs_hr_attendance INNER JOIN hs_hr_employee ON hs_hr_attendance.employee_id=hs_hr_employee.emp_number SET punchin_time='',in_note='',punchout_time='',out_note='',status='' WHERE hs_hr_attendance.attendance_id='1253561390_0' AND ( hs_hr_employee.employee_id LIKE'600-1012%')

Incorrect field name used: punchout_time

data

Incorrect field name used: out_note

data

Incorrect field name used: status

data

Edit operation finished
0 => action:updated; sid:1253561390_0; tid:1253561390_0;

UPDATE hs_hr_attendance INNER JOIN hs_hr_employee ON hs_hr_attendance.employee_id=hs_hr_employee.emp_number SET punchin_time='',in_note='',punchout_time='',out_note='',status='' WHERE hs_hr_attendance.attendance_id='1253561390_0' AND ( hs_hr_employee.employee_id LIKE'600-1012%')

Done in 0.00283598899841s

Edit operation finished
0 => action:updated; sid:1253561390_0; tid:1253561390_0;

Done in 0.00258708000183s


What i'm not doing right?

Thank u for anwer

What version of dhtmlxConnectors do yo use? Please try to update it to the latest one http://dhtmlx.com/docs/products/dhtmlxConnector/index.shtml

i'm using 0.95 Php Version

I've adjusted the code...now is:

<?php

    require("../dhtmlxSuite/dhtmlxConnector/php/codebase/grid_connector.php");

    $res=mysql_connect("localhost","root","sandbgroup");
    mysql_select_db("hr_mysql");

    $grid = new GridConnector($res);
    $grid->set_encoding("ISO-8859-1");
    $grid->enable_log("log/employee_connector.log",true);
    $grid->dynamic_loading(100);
    //$grid->render_table("hs_hr_attendance","attendance_id","employee_id,punchin_time,in_note,punchout_time,out_note,status");
   
   
      if ($grid->is_select_mode()) {
     
        $grid->render_sql ("SELECT  hs.loc_code, hs.loc_city, b.employee_id, b.emp_lastname, b.emp_firstname, a.emp_number " .
        "FROM hs_hr_location hs " .
        "JOIN (hs_hr_emp_locations a " .
        "JOIN hs_hr_employee  b " .
        "ON (a.emp_number=b.emp_number)) " .
        "ON (a.loc_code=hs.loc_code) " .
        "WHERE b.emp_status<>'EST009' AND b.emp_status<>'EST011' AND b.emp_status<>'EST012'","emp_number","employee_id,emp_lastname,emp_firstname,loc_city");
     }
     else
     {
         $grid->event->attach("beforeUpdate",doBeforeUpdate);
         $grid->render_table ("hs_hr_attendance","attendance_id","punchin_time,punchout_time,in_note,out_note,status");
     }
   
function doBeforeUpdate($action)
{
    $gr_id = $action->get_id();
    $db = new db_class;
    $success=mysql_query("UPDATE hs_hr_attendance SET " .
            "punchin_time='{$action->get_value('punchin_time')}'," .
            "punchout_time='{$action->get_value('punchout_time')}' ," .
            "in_note='{$action->get_value('in_note')}' ," .
            "out_note='{$action->get_value('out_note')}' ," .
            "status='{$action->get_value('status')}' " .
            "WHERE attendance_id={$action->get_value('attendance_id')}");

    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
    }
}

?>


for my opinion seems to be correct but no data loading and updating and the log says to me:



====================================
Log started, 22/09/2009 11:09:37
====================================

Undefined offset:  3 at /var/www/timesheet/includes/dhtmlxSuite/dhtmlxConnector/php/codebase/grid_connector.php line 143

!!!Uncaught Exception
Code: 0
Message: Incorrect dataset minimization, master field not found.

why?
DhtmlxGrid 2.5 / DhtmlxConnector 0.95
Thank u very much for all

I've tried to comment  if ($grid->is_select_mode()){

and i see that the error disapear ..

obiviously the update process doesn't work....

anyway ...  what's wrong about that is statement?

have i to include anything else?

This issue confirmed. We'll provide you solution as soon as possible.

Is there any workaround in a while...?

or may i use any kind of alternatice?

could u pls tell me when the issue will be corrected?

Thank U

We'll try provide you solution in 1-2 weeks. 

Very Good,

it will be a new release or just a fix for me?

just to know if i have to check on your site or wait for a mail from you...

This will be solution for you. We'll inform you by email.

ok...thank u again

Any Update regarding it?

is there any workaround in a while?

thank u

Hi, any update?

u wrote to me : " We'll try provide you solution in 1-2 weeks. "

Are you using co|coro columns in the grid , or #connector_select_filter ?
The above error occurs when complex SQL was used for data rendering and auto-fill for some client side collection requested - exception is thrown because code can't build the collection filling SQL from provided configuration. 

You can 
a) add next line to server side code

$grid->set_options("field_name",array());

Where field_name name of the field for which you have co | coro column in the grid. 
(other options can be checked at http://dhtmlx.com/dhxdocs/doku.php?id=dhtmlxconnector:select-box_columns_in_grid )

b) you can replace the existing grid_connector.php with provided one, it has auto-filling disabled. 

ok...thank u

i'm tring to do all stuff anyway my code seems to not update rows....surelly i'm write wrong function:

Here is my log:

====================================
Log started, 15/10/2009 11:10:28
====================================

DataProcessor object initialized
1255600345_0_gr_id => 1255600345_0
1255600345_0_c0 => 601-1084
1255600345_0_c1 => SPECCHIO
1255600345_0_c2 => ALESSANDRO
1255600345_0_c3 => 2009-09-27 07:36:46
1255600345_0_c4 => Entrat
1255600345_0_c5 => 2009-09-27 11:13:08
1255600345_0_c6 => Uscita
1255600345_0_!nativeeditor_status => updated
ids => 1255600345_0

Row data [1255600345_0]

Incorrect field name used: punchin_time

data

Incorrect field name used: in_note

data

Incorrect field name used: punchout_time

data

Incorrect field name used: out_note

data

Event code for update processed

Edit operation finished
0 => action:updated; sid:1255600345_0; tid:1255600345_0;

Done in 0.00210690498352s

I've tried to change the code many times but always wuth the same result:

here is my code:

<?php
require_once("dhtmlxSuite/dhtmlxConnector/php/codebase/grid_connector.php");

    $res=mysql_connect("localhost","root","xxx");
    mysql_select_db("hr_mysql");
   
   
    $timesheet_grid = new GridConnector($res);
    $timesheet_grid->enable_log("timesheet_grid_connector.log",true);
    $timesheet_grid->set_encoding("ISO-8859-1");
    $timesheet_grid->dynamic_loading(100);
   
   
    function my_update($action){
$punchin_time=$action->get_value("punchin_time");
$in_note=$action->get_value("in_note");
$punchout_time=$action->get_value("punchout_time");
$out_note=$action->get_value("out_note");
mysql_query("UPDATE hs_hr_attendance SET punchin_time='{$punchin_time}', in_note='{$in_note}', punchout_time='{$punchout_time}', out_note='{$out_note}' WHERE id='{$action->get_id()}");
$action->success(); //if you have made custom update - mark operation as finished
}
   
$timesheet_grid->event->attach("beforeUpdate","my_update");

$timesheet_grid->render_sql("SELECT" .
            "a.employee_id, a.emp_lastname, a.emp_firstname," .
            "b.punchin_time, b.in_note, b.punchout_time, b.out_note, b.status  " .
            "from hs_hr_employee a " .
            "JOIN hs_hr_attendance b " .
            "ON a.emp_number=b.employee_id " .
            "ORDER BY b.punchin_time ASC",
            "attendance_id","employee_id, emp_lastname, emp_firstname,punchin_time,in_note,punchout_time,out_note,status");      
     
   
?>

Really sorry for boring u... but maybe its not clear for me correct update procedure