Problem: getting syntax error with following SQL statement:
$_sql = <<<SQLDOC
LOCK TABLES `table_name` WRITE;
INSERT INTO `table_name` ('col_a', 'col_b') VALUES ('value_a1',
'value_a2');
INSERT INTO `table_name` ('col_a', 'col_b') VALUES ('value_b1',
'value_b2');
UNLOCK TABLES;
SQLDOC;
Error Message:
"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 '; INSERT INTO `table_name`..."
The manual didn't help me. I suspect maybe it has something to do with
the way the semicolon is parsed? What am I missing?
Thanks in advance,
Tom
This is obviously a (My)SQL syntax error, thus you need to search in that
manual. Notice that there are no quotes around the column names:
http://dev.mysql.com/doc/refman/5.0/en/insert.html
INSERT INTO table (cola, colb) VALUES ('val1', 'val2');
In case you are using reserverd keywords as a table or column name, you should
use ``` to inform MySQL about that.
INSERT INTO `table` (`cola`) VALUES ('vala');
--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be>
INSERT INTO `table_name` (`col_a`, `col_b`) VALUES ('value_a1',
'value_a2');
or even:
INSERT INTO `table_name` VALUES ('value_a1', 'value_a2');
which produces in my script SQL syntax 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 '; INSERT INTO `table_name`..."
Thanks again,
Tom
Zoe.
mysql_query() doesn't support multiple SQL statements separated by
semi-colons. You'll either need to separate your statements into
separate mysql_query() calls, or use the mysqli_multi_query() statement,
if it's available.
--
Oli
http://www.php.net/manual/en/function.mysql-query.php#37870
The PHP manual isn't explicit on the point, though it does note, "The
query string should not end with a semicolon."
Below is my variation on the function provided in the comment:
/* fx mysql_query_batch
source: http://www.php.net/manual/en/function.mysql-query.php#31381
*************************************************/
function mysql_query_batch($query, $as_transaction=TRUE)
{
// *** DATA
# internal
$_SPLIT = array();
$_statement = '';
# return
$query_result = 0;
// *** MANIPULATE
# transaction-safe query
if ( $as_transaction )
{
$query = 'START TRANSACTION;' . $query . '; COMMIT;';
}
# split query
$_SPLIT = preg_split("/[;]+/", $query);
# process statements one-by-one
foreach ( $_SPLIT as $_statement )
{
$_statement = trim($_statement);
if ( !empty($_statement) )
{
# try query
$query_result = mysql_query($_statement);
# catch
if ( !$query_result )
{
trigger_error('MySQL error number '.mysql_errno().': '.mysql_error());
break;
}
}
}
// *** RETURN
return $query_result;
} # end Fx
/*______________________________________________*/
Not extensively tested, but it's met my demands thus far.
Incidentally, I dug out the phpmyadmin function -- it parses the
textarea post character-by-character and splits up the statements.