using heredoc for SQL statements

[Tom]

I've used heredocs for single SQL statements without a problem. Also,
I've tried this using the SQL form on PhpMyAdmin and it works so I
conclude it should work in PHP.

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

[Tim Van Wassenhove]

On 2006-01-17, Tom <klen...@gmail.com> wrote:
> '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?

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>

[Tom]

Just noticed another syntax error -- but this wasn't in my original
code and isn't the problem. Nevertheless, statements in code above
should read:

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

[z...@bytenoise.co.uk]

You're right: your use of heredocs is fine, and the semicolon is to
blame. Although you can group several queries together using the
command
line MySQL program, and using PhpMyAdmin, it doesn't work this way in
PHP. You have to submit only one query at a time, so that you can
process the result of each query seperately (even if the query won't
give you a particularly useful result, it still has to be seperated).

Zoe.

[Oli Filth]

Tom said the following on 17/01/2006 03:27:

> I've used heredocs for single SQL statements without a problem. Also,
> I've tried this using the SQL form on PhpMyAdmin and it works so I
> conclude it should work in PHP.
>
> 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`..."
>

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

[Tom]

Thanks for the responses. I finally found this:

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.