Buddy Wimberley
wimbre042@gmail.com (713) 851-1223 November 08, 2016
Jersey-Village Fire Dept Houston, TX
MySqli 5 Workbench 5.6 PhpEd Build 17016, 64 bit
ISSUE: WHAT IS MySQLi PARSING ERROR when PHP Generated SQL Script fails in PhpED with
error [1064], but same SQL script when executed in WORKBENCH WORKS?
<Code PHP creating SQL Query >
//==================================//
// Build SQL string: one co_org //
//==================================//
$org_id = $a_org_id[0]; // only 1 org_id specified
$sql1 = "SET @qdate = '$start_dte', ";
$sql1 .= " @qdate1 = '$start_dte', ";
$sql1 .= " @org1 = $org_id ;";
$sql1 .= " CREATE TABLE tmp_class AS ";
$sql1 .= " ( SELECT * FROM ce_class WHERE cl_date >= @qdate ); ";
$sql1 .= " SELECT * FROM tmp_class WHERE cl_date >= @qdate1 ";
$sql1 .= " AND cl_org_cd != @org1 ";
// save query - single exclusion selected
$_SESSION['qry-class'] = $sql1;
< PHP code that initiates Query $sql1 >
// check if qry-class filters setup from p_class-qry
if (isset($_SESSION['qry-class'])) {
$sql_tmp = $_SESSION['qry-class'];
}
$retval = $db->query( $sql_tmp ); // returns an object_array
if(!$retval ) {
die('Could not get data: ' . mysql_error());
}
< db-Query ERROR [1064] >
db-Query Error [1064] = 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 'CREATE TABLE tmp_class AS ( SELECT * FROM ce_class WHERE cl_date >= @qdate ); ' at line 1
db-Query failed:
db-Last SQL query: SET @qdate = '2016/01/01', @qdate1 = '2016/01/01', @org1 = 2 ; CREATE TABLE tmp_class AS ( SELECT * FROM ce_class WHERE cl_date >= @qdate ); SELECT * FROM tmp_class WHERE cl_date >= @qdate1 AND cl_org_cd != @org1
< SQL Script selected from PH generated above >
SET @qdate = '2016/01/01', @qdate1 = '2016/01/01', @org1 = 2 ; CREATE TABLE tmp_class AS ( SELECT * FROM ce_class WHERE cl_date >= @qdate ); SELECT * FROM tmp_class WHERE cl_date >= @qdate1 AND cl_org_cd != @org1
< above PASTED into MySQL56 Workbench and tested >
15:10:46 SET @qdate = '2016/01/01', @qdate1 = '2016/01/01', @org1 = 2 0 row(s) affected 0.000 sec
15:10:46 CREATE TABLE tmp_class AS ( SELECT * FROM ce_class WHERE cl_date >= @qdate ) 46 row(s) affected Records: 46 Duplicates: 0 Warnings: 0 0.125 sec
15:10:47 SELECT * FROM tmp_class WHERE cl_date >= @qdate1 AND cl_org_cd != @org1 LIMIT 0, 1000 14 row(s) returned 0.000 sec / 0.000 sec
< WORKBENCH RETURN Expected Results with No ERROR ???? >