Wrong value placed when saving rec using optionsconnector

MarioZ · December 31, 2010, 1:06am

Hello,

When I change a value (not province), an error occurs: not the province_id is entered, but the province_name, the record is NOT saved into the database.

[code]DataProcessor object initialized
3_gr_id => 3
3_c0 => Family residence 65
3_c1 => Frung street 1023
3_c2 => Guang Torpkwa
3_c3 => Grinhime
3_c4 => WA 23809
3_c5 => 023-304 59 00
3_!nativeeditor_status => updated
ids => 3

Row data [3]
marker_id => 3
name => Family residence 65
address => Frung street 1023
city => Guang Torpkwa
province_id => Grinhime
zipcode => WA 23809
phone => 023-304 59 00
!nativeeditor_status => updated

UPDATE markers SET name=‘Family residence 65’, address=‘Frung street 1023’, city=‘Guang Torpkwa’, province_id=‘Grinhime’, zipcode=‘WA 23809’, phone=‘023-304 59 00’ WHERE marker_id=‘3’

Edit operation finished
0 => action:error; sid:3; tid:3;
[/code]

When I change the province from the pull-down, the record is saved into the database.

[code]
DataProcessor object initialized
3_gr_id => 3
3_c0 => Family residence 65
3_c1 => Frung street 1023
3_c2 => Guang Torpkwa
3_c3 => 7
3_c4 => WA 23809
3_c5 => 023-304 59 00
3_!nativeeditor_status => updated
ids => 3

Row data [3]
marker_id => 3
name => Family residence 65
address => Frung street 1023
city => Guang Torpkwa
province_id => 7
zipcode => WA 23809
phone => 023-304 59 00
!nativeeditor_status => updated

UPDATE markers SET name=‘Family residence 65’, address=‘Frung street 1023’, city=‘Guang Torpkwa’, province_id=‘7’, zipcode=‘WA 23809’, phone=‘023-304 59 00’ WHERE marker_id=‘3’

Edit operation finished
0 => action:updated; sid:3; tid:3;[/code]

[code]

<?php $res = mysql_connect("host","username","password"); mysql_select_db("database"); require("dhtmlxConnector/codebase/grid_connector.php"); require("dhtmlxConnector/codebase/combo_connector.php"); $gridConn = new GridConnector($res); $gridConn->enable_log("markers_log.txt"); $filter1 = new OptionsConnector($res); $filter1->render_sql(" SELECT province_id as value, province_name as label FROM provinces", "province_id", "province_id(value), province_name(label)"); $gridConn->set_options("province_name",$filter1); $gridConn->dynamic_loading(100); $gridConn->sql->attach("update", "UPDATE markers SET name='{name}', address='{address}', city='{city}', province_id='{province_id}', zipcode='{zipcode}', phone='{phone}' WHERE marker_id='{marker_id}'"); //$gridConn->sql->attach("insert", // "INSERT INTO markers (name,address,city,province_id,zipcode,phone) // VALUES ('{name}','{address}','{city}','{province_id}','{zipcode}','{phone}')"); if ($gridConn->is_select_mode()) //code for loading data { $gridConn->render_sql(" SELECT marker_id, name, address, city, markers.province_id, province_name, zipcode, phone FROM markers,provinces WHERE markers.province_id=provinces.province_id" , "marker_id", "name,address,city,province_name,zipcode,phone"); } else //code for other operations - i.e. update/insert/delete { $gridConn->render_table("markers", "marker_id", "name,address,city,province_id,zipcode,phone"); } ?>[/code]

(All data from the database is fictive, made-up)

What i’m I doing wrong?

Stanislav · December 31, 2010, 8:57am

You need to load the ID of province , not its name.
Currently you have

$gridConn->render_sql(" SELECT marker_id, name, address, city, markers.province_id, province_name, zipcode, phone FROM markers,provinces WHERE markers.province_id=provinces.province_id" , "marker_id", "name,address,city,province_name,zipcode,phone");

Valid code , will look as

$gridConn->render_sql(" SELECT marker_id, name, address, city, markers.province_id, province_name, zipcode, phone FROM markers,provinces WHERE markers.province_id=provinces.province_id" , "marker_id", "name,address,city,province_id,zipcode,phone");

MarioZ · December 31, 2010, 9:40am

The unchanged code shows the following select queries:

[code]SELECT province_id as value, province_name as label FROM provinces

SELECT marker_id,name,address,city,markers.province_id,province_name,zipcode,phone FROM markers,provinces WHERE markers.province_id=provinces.province_id LIMIT 0,100

SELECT COUNT(*) as DHX_COUNT FROM markers,provinces WHERE markers.province_id=provinces.province_id
[/code]

When I change the sql query as you suggested, the following error occors:

[code]SELECT DISTINCT province_id as value FROM markers,provinces WHERE markers.province_id=provinces.province_id

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
Column ‘province_id’ in field list is ambiguous
[/code]

When I change the query into this:

$gridConn->render_sql(" SELECT marker_id, name, address, city, markers.province_id, province_name, zipcode, phone FROM markers,provinces WHERE markers.province_id=provinces.province_id" , "marker_id", "name,address,city,provinces.province_id,zipcode,phone");
No errors, but now no province name is displayed. Also the pull-down filter now shows the id’s not the names.

This is entered into the log:

[code]SELECT DISTINCT provinces.province_id as value FROM markers,provinces WHERE markers.province_id=provinces.province_id

SELECT marker_id,name,address,city,markers.province_id,province_name,zipcode,phone FROM markers,provinces WHERE markers.province_id=provinces.province_id LIMIT 0,100

SELECT COUNT(*) as DHX_COUNT FROM markers,provinces WHERE markers.province_id=provinces.province_id

Undefined index: provinces.province_id at C:\xampp\htdocs\LionTravel\dhtmlxConnector\codebase\grid_connector.php line 111

Undefined index: provinces.province_id at C:\xampp\htdocs\LionTravel\dhtmlxConnector\codebase\grid_connector.php line 111[/code]

Also found out that the selection filter for province is not working, if I select a province, the grid becomes empty.

Stanislav · January 3, 2011, 2:59pm

Try to use

[code]$options = new OptionsConnector($res);
$options->render_table(“provinces”,“province_id”,“province_id(value),province_name(label)”);

$gridConn->set_options(“province_id”, $options);
$gridConn->render_sql("
SELECT
marker_id,
name,
address,
city,
markers.province_id,
province_name,
zipcode,
phone
FROM
markers,provinces
WHERE
markers.province_id=provinces.province_id" ,
“marker_id”,
“name,address,city,province_id,zipcode,phone”);[/code]

MarioZ · January 3, 2011, 3:45pm

That did it! Now the record is updated correctly and the province filter is showing all the stored provinces.

But, when I want to use the province filter an error occurs. I think this has to do that province_id is both stored under the same name in both the tables markers and provinces.
I could change the table provinces, but before I do that, is there an other solution?

[code]SELECT marker_id,name,address,city,markers.province_id,province_name,zipcode,phone FROM markers,provinces WHERE ( markers.province_id=provinces.province_id) AND province_id LIKE ‘%3%’ LIMIT 0,100

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
Column ‘province_id’ in where clause is ambiguous
[/code]

Stanislav · January 4, 2011, 12:55pm

It possible to use beforeFilter server side event to alter filtering SQL
docs.dhtmlx.com/doku.php?id=dhtm … :filtering

but in you original query , table join was used only to fetch province_name. With current request structure you may need not the table join at all.

MarioZ · January 12, 2011, 8:57am

I solved the problem by changing the name of province_id entry in table markers to provinceID.