MySQL Forums
Forum List  »  PHP

PhpED - WorkBench MySQL Parsing Error [1064]
Posted by: RUSSELL E WIMBERLEY
Date: November 09, 2016 08:16PM

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

Options: ReplyQuote


Subject
Written By
Posted
PhpED - WorkBench MySQL Parsing Error [1064]
November 09, 2016 08:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.