SQL syntax error

Identical query works with phpMyAdmin, but not with mysql_query

[maerk]

I'm writing a plugin for wordpress that will allow users to disable all comments for old posts, and I'm having trouble with a MySQL query involved.

The PHP script generates the query based on options that the user chooses. When I try to get PHP to send the query straight to the database, I get a "1064: You have an error in your SQL syntax" error.

This is a mystery, since the query that the script sends is echoed, and when I copy and paste it into phpMyAdmin command line it works fine.

The query looks like this:

UPDATE `wp_posts` SET `comment_status` = 'closed';
UPDATE `wp_posts` SET `ping_status` = 'closed';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE `option_id` =20;
UPDATE `wp_options` SET `option_value` = 'closed' WHERE `option_id` =21;

The code I'm using to connect and send the query is:

// Connect to the database
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die(mysql_error($link));
mysql_select_db(DB_NAME) or die(mysql_error($link));

// Send the query
mysql_query($query, $link) or die(mysql_error($link));

The error message is:

"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 '; UPDATE `wp_posts` SET `ping_status` = 'closed'; UPDATE `wp_options` SET `optio' at line 1"

[Steerpike]

I'm taking a guess but it looks like you're sending that block of query as one chunk in your php/mysql statement.

I *think* what you have at the moment is:

$query = "UPDATE `wp_posts` SET `comment_status` = 'closed';
UPDATE `wp_posts` SET `ping_status` = 'closed';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE `option_id` =20;
UPDATE `wp_options` SET `option_value` = 'closed' WHERE `option_id` =21;";
mysql_query($query, $link) or die(mysql_error($link));

and what you *need* to have is:

$query = "UPDATE `wp_posts` SET `comment_status` = 'closed'";
mysql_query($query, $link) or die(mysql_error($link));
$query = "UPDATE `wp_posts` SET `ping_status` = 'closed'";
mysql_query($query, $link) or die(mysql_error($link));
$query = "UPDATE `wp_options` SET `option_value` = 'closed' WHERE `option_id` =20";
mysql_query($query, $link) or die(mysql_error($link));
$query = "UPDATE `wp_options` SET `option_value` = 'closed' WHERE `option_id` =21";
mysql_query($query, $link) or die(mysql_error($link));

Steerpike.

[Andrew Bassett]

MySQL doesn't support multiple statements in a a php execute function? Blow.

[DrDoc]

You may want to look into MySQL Improved: [php.net...]

[maerk]

Steerpike,

You're right, I'm sending the query as a block all at once. Do you mean I should send each statement individually?

Actually, I've just tested what happens when only one statement is sent, and it works.

Thanks! I'll get onto changing the code :D

[barns101]

When you send multiple queries through phpMyAdmin I believe that it breaks them up in the background before processing them.

[maerk]

Thanks, that's useful to know. The script works as I want it to now :D