Syntax error when doing two updates in a single command

Asked

Viewed 364 times

0

I’m trying to update more than one table in the same query, Local runs normal, but online gives syntax error in Mysql 5.1.72. What can be done to resolve?

$query = 
    "UPDATE isc_products SET prodcurrentinv = 12, prodretailprice = 25, prodprice = 26 WHERE prodcode = 121212121212 LIMIT 1;" . 
    "UPDATE isc_product_variation_combinations SET vcstock = 54 WHERE vcsku = 121212121212 LIMIT 1;";
    $query = $pdo->prepare($query);
    $query->execute();

Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[42000]: Syntax error or access Violation: 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 isc_product_variation_combinations SET vcstock = 54 WHERE vcsku = 1212121' at line 1' in /home/aoculist/public_html/admteste/template/main.php:5 Stack trace: #0 /home/aoculist/public_html/admteste/template/main.php(5): PDO->prepare('UPDATE isc_prod...') #1 /home/aoculist/public_html/admteste/index.php(55): include('/home/aoculist/...') #2 {main} thrown in /home/aoculist/public_html/admteste/template/main.php on line 5

php mysql

  • Raphael, a question. Developing Local vc ta on Windows and Linux Server(online)?

  • Windows, using Zend Server. Online is Linux Zend Server also.

  • I may be talking nonsense, because I don’t remember exactly, but I think I’ve been there, and the problem was that my query had lowercase tables and the server was uppercase... the old and simple Case Sensitive problem, take a look at the query and online database

  • No, on the server everything is in minuscule, as it was written in the query.

  • Are field and table versions of the location the same on the server? ------ I just saw something... in your second Update gives a space after ; just to kill my curiosity

  • Gives in the same problem.

  • I’m surprised it’s running normal locally. Are you sure it’s working? As far as I know, Mysql running from PHP does not allow two commands at once, thus separated by semicolons.

Show 2 more comments

1 answer

1

For security reasons, for a long time it was not possible to run more than one SQL command at a time, using Mysql from PHP.

Newer versions of PDO accept multiple commands.

To execute multiple commands at once you need:

  • PHP 5.3+
  • mysqlnd
  • Emulated Prepared statements. Make sure that PDO::ATTR_EMULATE_PREPARES is set to 1 (default). Alternatively you can avoid using Prepared statements and use $pdo->exec directly.

Source of the quote: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.