why multiple update statement not working

Question

I have checked this query in phpMyAdmin it is ok, but in php script this is not working. I have tried more than 6 hours for it, but I do not understand what is the error. I am getting this error in $db->query($query) in PDO statement.

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 sefx_article_paras SET srl = '1' WHERE id = '24'; UPDATE `sefx' at line 4' in

But when I try with PDO exec there is no error, but UPDATE query is not executed.

Here is sql generated by PHP:

UPDATE `sef

x_article_paras` 
SET `srl` = '1' WHERE `id` = '23';

UPDATE `sefx_article_paras` 
SET `srl` = '1' WHERE `id` = '24';

UPDATE `sefx_article_paras` 
SET `srl` = '1' WHERE `id` = '26';

UPDATE `sefx_article_paras` 
SET `srl` = '1' WHERE `id` = '27';

UPDATE `sefx_article_paras` 
SET `srl` = '1' WHERE `id` = '31';

....................


UPDATE `sefx_article_paras` 
SET `srl` = '8' WHERE `id` = '196';

UPDATE `sefx_article_paras` 
SET `srl` = '8' WHERE `id` = '211';

UPDATE `sefx_article_paras` 
SET `srl` = '8' WHERE `id` = '229';

possible duplicate of [PHP multiple MYSQL commands in one mysql\_query() query](http://stackoverflow.com/questions/11106719/php-multiple-mysql-commands-in-one-mysql-query-query) — user4035, Feb 05 '14 at 10:27
I think, it's possible: http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — user4035, Feb 05 '14 at 10:43

score -1 · Answer 1 · answered Sep 22 '14 at 00:27

PDO does not allow multiple queries in a prepared statement. Split these up into multiple queries or use code to split them by ;

<?php
  // $sql is your SQL
  $sqls = explode(';', $sql);
  $db->query('BEGIN');
  foreach($sqls as $sql) {
    if (trim($sql)) {
      $db->query($sql);
    }
  }
  $db->query($sql);
?>

why multiple update statement not working

1 Answers1