Different cell values on grid and connector

Hello again,
is it possible to display some values on a whole column while sending back different ones on the server side?
example:
on grid
column_name
John
George
Irene

on server
column_name
1
2
3

As you may have already understood I am trying to bypass the “multiple linked tables CRUD” problem on grid. I want to get some ID values from the server and display them as a text of my choosing (changing them in my code) but when I send data back to the server I want them to be changed back to ID values without letting the user know the IDs.
I am using grid, dataprocessor and a php connector.
Thanks in advance,
AndreasI.

It can be done on grid’s level

You can define column in grid as “co” type - which will allow to define list of ids and labels for that column ( grid will show labels, but load|save ids )

If you need this column to be readonly - you can force readonly mode through onEditCell event

As for pure connector solution - it possible to assign custom events, which will fire before data saving and change incoming data, but there is no ready solution for such label-id translation ( you will need to write sql query and process it results )

docs.dhtmlx.com/doku.php?id=dhtm … date_event

Thanks for your time Stanislav, I know that this is not the place for combobox discussion but is there a combo.setValue() function? I read about a combo.put() one but it says it adds a new record in the collection.
About custom events, I will deffinately going to check it out and let you know, thank you.

There is no public API to change values for already existing options.
put command adds both label and value to the list of options.

you can work with combo cell with normal grid’s api
grid.cells(i,j).setValue(value)

will set new value for the cell and will show the related label

I tried using my own update event but I don’t seem to make it work.
Where does the connector expect to find the field names? As you can see in my log it says “incorrect field name”.
I am trying to update a record by using dep_id as an id value even though I display it in my grid as the name of the department. (e.id instead of d.department).

$grid->sql->attach("Update","UPDATE employee_for_dhtmlx SET firstname='{firstname}', lastname='{lastname}', start_date='{start_date}', end_date='{end_date}', dep_id='{dep_id}', hours='{hours}', kids='{kids}', insurance='{insurance}', comments='{comments}' WHERE employee_id='{employee_id}'");

$grid->render_sql( "SELECT e.employee_id, e.dep_id, e.firstname, e.lastname, e.start_date, e.end_date, e.hours, d.department, e.kids, e.insurance, e.comments FROM employee_for_dhtmlx e, departments d WHERE e.dep_id = d.dep_id","e.employee_id", "firstname,lastname,start_date,end_date,hours,department,kids,insurance,comments,dep_id,employee_id");

====================================
Log started, 30/08/2012 11:08:47

SELECT DISTINCT department as value FROM employee_for_dhtmlx e, departments d WHERE ( e.dep_id = d.dep_id)

SELECT e.employee_id, e.dep_id, e.firstname, e.lastname, e.start_date, e.end_date, e.hours, d.department, e.kids, e.insurance, e.comments
FROM employee_for_dhtmlx e, departments d WHERE ( e.dep_id = d.dep_id) LIMIT 0,1000

SELECT COUNT(*) as DHX_COUNT FROM employee_for_dhtmlx e, departments d WHERE ( e.dep_id = d.dep_id)

Done in 0.30638098716736s

====================================
Log started, 30/08/2012 11:08:56

DataProcessor object initialized
1346320727x18_gr_id => 1346320727x18
1346320727x18_c0 => Test
1346320727x18_c1 => TestLastName
1346320727x18_c2 => 2012-08-30
1346320727x18_c3 =>
1346320727x18_c4 => 8
1346320727x18_c5 => IT
1346320727x18_c6 => 0
1346320727x18_c7 => ΙΚΑ
1346320727x18_c8 =>
1346320727x18_!nativeeditor_status => updated
ids => 1346320727x18

Row data [1346320727x18]
e.employee_id => 1346320727x18
firstname => Test
lastname => TestLastName
start_date => 2012-08-30
end_date =>
hours => 8
department => IT
kids => 0
insurance => ΙΚΑ
comments =>
!nativeeditor_status => updated

Incorrect field name used: dep_id

data
e.employee_id => 1346320727x18
firstname => Test
lastname => TestLastName
start_date => 2012-08-30
end_date =>
hours => 8
department => IT
kids => 0
insurance => ΙΚΑ
comments =>
!nativeeditor_status => updated

Incorrect field name used: employee_id

data
e.employee_id => 1346320727x18
firstname => Test
lastname => TestLastName
start_date => 2012-08-30
end_date =>
hours => 8
department => IT
kids => 0
insurance => ΙΚΑ
comments =>
!nativeeditor_status => updated

UPDATE employee_for_dhtmlx SET firstname=‘Test’, lastname=‘TestLastName’, start_date=‘2012-08-30’, end_date=’’, dep_id=’’, hours=‘8’, kids=‘0’, insurance=‘ΙΚΑ’, comments=’’ WHERE employee_id=’’

Edit operation finished
0 => action:updated; sid:1346320727x18; tid:1346320727x18;

Done in 0.12215709686279s

There are two problems

a) employee_id field, instead of
$grid->render_sql("…sql…",“e.employee_id”, “…fields
you need to use
$grid->render_sql(”…sql…",“employee_id”, "…fields

b) dep_id field
As far as I can see you are extracting on server side bigger data count than columns count of grid on client side, right? Unfortunately it will not work in such way, grid will lost extra data. You can add few more lines to store that data on client side

function store_extra($data){ $data->set_userdata("dep_id", $data->get_value("dep_id")); } $grid->event->attach("beforeRender", "store_extra"); $grid->render_sql("...sql...","employee_id", "...fields

above code will instruct to store dep_id in userdata section, so it will be correctly stored on client side, and will be available during update operation

You are very helpful so far Stanislav, store_extra function worked for me!
Then I got a the following msg:

====================================
Log started, 31/08/2012 11:08:25

DataProcessor object initialized
150_gr_id => 150
150_c0 => TestFirstNa
150_c1 => TestLastName
150_c2 => 2012-08-30
150_c3 =>
150_c4 => 8
150_c5 => IT
150_c6 => 0
150_c7 => ΙΚΑ
150_c8 =>
150_dep_id => 4
150_!nativeeditor_status => updated
ids => 150

Row data [150]
employee_id => 150
firstname => TestFirstNa
lastname => TestLastName
start_date => 2012-08-30
end_date =>
hours => 8
department => IT
kids => 0
insurance => ΙΚΑ
comments =>
dep_id => 4
!nativeeditor_status => updated

UPDATE employee_for_dhtmlx SET firstname=‘TestFirstNa’, lastname=‘TestLastName’, start_date=‘2012-08-30’, end_date=’’, dep_id=‘4’, hours=‘8’, kids=‘0’, insurance=‘ΙΚΑ’, comments=’’ WHERE employee_id=‘150’

exception ‘Exception’ with message ‘MySQL operation failed
Incorrect date value: ‘’ for column ‘end_date’ at row 1’ in C:*\dhtmlxSuite\dhtmlxConnector_php\codebase\db_common.php:965
Stack trace:
#0 C:*
\dhtmlxSuite\dhtmlxConnector_php\codebase\dataprocessor.php(210): MySQLDBDataWrapper->query(‘UPDATE employee…’)
#1 C:*\dhtmlxSuite\dhtmlxConnector_php\codebase\dataprocessor.php(173): DataProcessor->check_exts(Object(DataAction), ‘update’)
#2 C:*
\dhtmlxSuite\dhtmlxConnector_php\codebase\dataprocessor.php(102): DataProcessor->inner_process(Object(DataAction))
#3 C:*\dhtmlxSuite\dhtmlxConnector_php\codebase\base_connector.php(475): DataProcessor->process(Object(DataConfig), Object(DataRequestConfig))
#4 C:*
\dhtmlxConnector_php\codebase\base_connector.php(432): Connector->render()
#5 C:**********\connector_2.php(24): Connector->render_sql(‘SELECT e.employ…’, ‘employee_id’, ‘firstname,lastn…’)
#6 {main}

Edit operation finished
0 => action:error; sid:150; tid:150;

Done in 0.26506900787354s

I leave end_date field in grid blank, as it was when I got the value from the SELECT query but when I try to send this value back it seems incorrect. So, I added:

if($data->get_value("end_date")=="") $data->set_userdata("end_date", "NULL"); //(also tried "'NULL'" and NULL as a parameter, no results)
in my store_extra function to change “” to NULL (the table allows the end_date to be NULL) and I get the following:

====================================
Log started, 31/08/2012 11:08:36

SELECT e.employee_id, e.dep_id, e.firstname, e.lastname, e.start_date, e.end_date, e.hours, d.department, e.kids, e.insurance, e.comments
FROM employee_for_dhtmlx e, departments d WHERE ( e.dep_id = d.dep_id) LIMIT 0,1000

SELECT COUNT(*) as DHX_COUNT FROM employee_for_dhtmlx e, departments d WHERE ( e.dep_id = d.dep_id)

Done in 0.55686187744141s

====================================
Log started, 31/08/2012 11:08:40

DataProcessor object initialized
150_gr_id => 150
150_c0 => TestFirstN
150_c1 => TestLastName
150_c2 => 2012-08-30
150_c3 =>
150_c4 => 8
150_c5 => IT
150_c6 => 0
150_c7 => ΙΚΑ
150_c8 =>
150_end_date => NULL
150_dep_id => 4
150_!nativeeditor_status => updated
ids => 150

Row data [150]
employee_id => 150
firstname => TestFirstN
lastname => TestLastName
start_date => 2012-08-30
end_date => NULL
hours => 8
department => IT
kids => 0
insurance => ΙΚΑ
comments =>
dep_id => 4
!nativeeditor_status => updated

UPDATE employee_for_dhtmlx SET firstname=‘TestFirstN’, lastname=‘TestLastName’, start_date=‘2012-08-30’, end_date=‘NULL’, dep_id=‘4’, hours=‘8’, kids=‘0’, insurance=‘ΙΚΑ’, comments=’’ WHERE employee_id=‘150’

exception ‘Exception’ with message ‘MySQL operation failed
Incorrect date value: ‘NULL’ for column ‘end_date’ at row 1’ in C:\xampp\htdocs\FocusProject1\dhtmlxSuite\dhtmlxConnector_php\codebase\db_common.php:965
Stack trace:
#0 C:*\dhtmlxSuite\dhtmlxConnector_php\codebase\dataprocessor.php(210): MySQLDBDataWrapper->query(‘UPDATE employee…’)
#1 C:*
\dhtmlxSuite\dhtmlxConnector_php\codebase\dataprocessor.php(173): DataProcessor->check_exts(Object(DataAction), ‘update’)
#2 C:*\dhtmlxSuite\dhtmlxConnector_php\codebase\dataprocessor.php(102): DataProcessor->inner_process(Object(DataAction))
#3 C:*
\dhtmlxSuite\dhtmlxConnector_php\codebase\base_connector.php(475): DataProcessor->process(Object(DataConfig), Object(DataRequestConfig))
#4 C:*\dhtmlxSuite\dhtmlxConnector_php\codebase\base_connector.php(432): Connector->render()
#5 C:*
\connector_2.php(24): Connector->render_sql(‘SELECT e.employ…’, ‘employee_id’, ‘firstname,lastn…’)
#6 {main}

Edit operation finished
0 => action:error; sid:150; tid:150;

Done in 0.27286505699158s

I can’t find a way to send the value null to the db without it being a String parameter using attach event :frowning: Is there a workaround using connector properties?

As for null values, Its a bit problematic, connector has hard time separating NULL as null value and NULL as text string

It can be configured for auto-generated queries, but in case of custom sql I don’t see any simple solution, which will be SQL injection safe in the same time.
Next is not very beautiful solution, but will allow to use different update sql for cases when start date is not provided.

function before_save($action){ global $grid; if ($action->get_value("start_date") == "") $grid->sql->attach("update", "...SQL without start_date..."; } $grid->event->attach("beforeProcessing", before_save);

I was trying something similar using beforeUpdate event, changing the String that would go to the Update event without any success. I will now try your way and let you know, thank you :slight_smile:

It worked as expected, thank you very much Stanislav.