|
How to use sql complex query of "Insert" statement Hi, I am using dhtmlxConnector to connect database , Update is ok , but an error occured while Insert data, Is any sample code for reference, thanks!!
require("../../dhtmlxConnector/codebase/grid_connector.php");
$grid = new GridConnector($res);
$grid->enable_log("temp.log",true);
$grid->dynamic_loading(100);
$grid->render_sql("Select * from grid, component where grid.gr_id=component.comp_id","grid.gr_id","GUID,setImagePath,setColTypes,setHeader,renderType,renderTable,renderSQL,renderIdentifier,renderFields,setInitWidths,setColSorting,setMultiLine,enableSmartRendering,enableDragAndDrop,enableColumnMove");
$grid->sql->attach("Update","Update grid setImagePath={setImagePath} where gr_id={grid.gr_id}");
$grid->sql->attach("Insert","????????????????????????????????"); Answer posted by Support on Jun 23, 2009 01:52 >>but an error occured while Insert data Please provide exact output from log file for problematic operation - with code, used in your case, auto-generated sql query must working fine enough As alternative solution you can use the next code function my_custom_insert($action){ $some = $action->get_value("setImagePath"); //any field can be retrieved in such way mysql_query("any custom sql here"); $action->success( mysql_insert_id()); } $grid->event->attach("beforeInsert","my_custom_insert");
Answer posted by Ivan on Jun 24, 2009 09:58 Hi, thanks for your replay
here under is the complete code
<?php
require_once("../../dhtmlxConnector/samples/config.php");
$res = mysql_connect($mysql_server,$mysql_user,$mysql_pass);
mysql_select_db("apps");
require("../../dhtmlxConnector/codebase/grid_connector.php");
$grid = new GridConnector($res);
$grid->enable_log("temp.log",true);
$grid->dynamic_loading(100);
function myInsert($action){
$sql = "Insert into grid (setImagePath,setColTypes) Values (";
$sql.= "'{$action->get_data('setImagePath')}','{$action->get_data('setColTypes')}'";
$sql.= ")";
mysql_query($sql);
$newId = mysql_insert_id();
$sql = "Insert into component(GUID,comp_type,comp_id) values (UUID(),'".$_GET["type"]."',".$newId.")";
mysql_query($sql);
$action->success();
}
function chkData($action){
if ($action->get_value("setImagePath")=="" || $action->get_value("setColTypes")=="")
$action->invalid();
}
if($_GET["type"]=="gr"){
$grid->render_sql("Select * from grid, component where grid.gr_id=component.comp_id","grid.gr_id","GUID,setImagePath,setColTypes,setHeader,renderType,renderTable,renderSQL,renderIdentifier,renderFields,setInitWidths,setColSorting,setMultiLine,enableSmartRendering,enableDragAndDrop,enableColumnMove");
$grid->sql->attach("Update","Update grid setImagePath={setImagePath} where gr_id={grid.gr_id}");
}else if ($_GET["type"]=="sd"){
}
$grid->event->attach("beforeProcessing",chkData);
$grid->event->attach("beforeInsert",myInsert);
?>
Here under is the msg from LOG
====================================
Log started, 24/06/2009 08:06:48
====================================
Edit operation started [GRID]
1245832060162_gr_id => 1245832060162
1245832060162_c0 =>
1245832060162_c1 => test
1245832060162_c2 => test
1245832060162_c3 => test
1245832060162_c4 =>
1245832060162_c5 =>
1245832060162_c6 =>
1245832060162_c7 =>
1245832060162_c8 =>
1245832060162_c9 =>
1245832060162_c10 => 0
1245832060162_c11 => 0
1245832060162_c12 => 0
1245832060162_c13 => 0
1245832060162_c14 =>
1245832060162_!nativeeditor_status => inserted
ids => 1245832060162
Row data [1245832060162]
gr_id => 1245832060162
0 =>
1 => test
2 => test
3 => test
4 =>
5 =>
6 =>
7 =>
8 =>
9 =>
10 => 0
11 => 0
12 => 0
13 => 0
14 =>
!nativeeditor_status => inserted
Exec SQL: INSERT INTO grid, component (setImagePath,setColTypes,setHeader,renderType,renderTable,renderSQL,renderIdentifier,renderFields,setInitWidths,setColSorting,setMultiLine,enableSmartRendering,enableDragAndDrop,enableColumnMove) VALUES ('','test','test','test','','','','','','','0','0','0','0')
MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' component (setImagePath,setColTypes,setHeader,renderType,renderTable,renderSQL,' at line 1
Edit operation finished
0 => action:error; sid:1245832060162; tid:1245832060162;
Done in 0.00996589660645ms
====================================
there two tables(grid & component) has been joined in my render_sql statement,
May I use sql->attach("Insert",".......") syntax to insert data or must use event handler?
although I had define myInsert() , but the SQL syntax show in the log is different from that I defined in myInsert() function.
THANKS FOR YOU HELP!!
Answer posted by Ivan on Jun 24, 2009 09:58 Hi, thanks for your replay
here under is the complete code
<?php
require_once("../../dhtmlxConnector/samples/config.php");
$res = mysql_connect($mysql_server,$mysql_user,$mysql_pass);
mysql_select_db("apps");
require("../../dhtmlxConnector/codebase/grid_connector.php");
$grid = new GridConnector($res);
$grid->enable_log("temp.log",true);
$grid->dynamic_loading(100);
function myInsert($action){
$sql = "Insert into grid (setImagePath,setColTypes) Values (";
$sql.= "'{$action->get_data('setImagePath')}','{$action->get_data('setColTypes')}'";
$sql.= ")";
mysql_query($sql);
$newId = mysql_insert_id();
$sql = "Insert into component(GUID,comp_type,comp_id) values (UUID(),'".$_GET["type"]."',".$newId.")";
mysql_query($sql);
$action->success();
}
function chkData($action){
if ($action->get_value("setImagePath")=="" || $action->get_value("setColTypes")=="")
$action->invalid();
}
if($_GET["type"]=="gr"){
$grid->render_sql("Select * from grid, component where grid.gr_id=component.comp_id","grid.gr_id","GUID,setImagePath,setColTypes,setHeader,renderType,renderTable,renderSQL,renderIdentifier,renderFields,setInitWidths,setColSorting,setMultiLine,enableSmartRendering,enableDragAndDrop,enableColumnMove");
$grid->sql->attach("Update","Update grid setImagePath={setImagePath} where gr_id={grid.gr_id}");
}else if ($_GET["type"]=="sd"){
}
$grid->event->attach("beforeProcessing",chkData);
$grid->event->attach("beforeInsert",myInsert);
?>
Here under is the msg from LOG
====================================
Log started, 24/06/2009 08:06:48
====================================
Edit operation started [GRID]
1245832060162_gr_id => 1245832060162
1245832060162_c0 =>
1245832060162_c1 => test
1245832060162_c2 => test
1245832060162_c3 => test
1245832060162_c4 =>
1245832060162_c5 =>
1245832060162_c6 =>
1245832060162_c7 =>
1245832060162_c8 =>
1245832060162_c9 =>
1245832060162_c10 => 0
1245832060162_c11 => 0
1245832060162_c12 => 0
1245832060162_c13 => 0
1245832060162_c14 =>
1245832060162_!nativeeditor_status => inserted
ids => 1245832060162
Row data [1245832060162]
gr_id => 1245832060162
0 =>
1 => test
2 => test
3 => test
4 =>
5 =>
6 =>
7 =>
8 =>
9 =>
10 => 0
11 => 0
12 => 0
13 => 0
14 =>
!nativeeditor_status => inserted
Exec SQL: INSERT INTO grid, component (setImagePath,setColTypes,setHeader,renderType,renderTable,renderSQL,renderIdentifier,renderFields,setInitWidths,setColSorting,setMultiLine,enableSmartRendering,enableDragAndDrop,enableColumnMove) VALUES ('','test','test','test','','','','','','','0','0','0','0')
MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' component (setImagePath,setColTypes,setHeader,renderType,renderTable,renderSQL,' at line 1
Edit operation finished
0 => action:error; sid:1245832060162; tid:1245832060162;
Done in 0.00996589660645ms
====================================
there two tables(grid & component) has been joined in my render_sql statement,
May I use sql->attach("Insert",".......") syntax to insert data or must use event handler?
although I had define myInsert() , but the SQL syntax show in the log is different from that I defined in myInsert() function.
THANKS FOR YOU HELP!!
Answer posted by Ivan on Jun 24, 2009 12:23 hi, the problem has been solved ...
"sql->attach" & "event->attach" should be write before "$grid->sql_render()" .....
sorry to disturb you !! |
