PHP Mysqli multi_query and trigger's delimiter

Question

I need to run a db creation script inside PHP. Using multi_query I find some trouble with trigger delimiter:

$sql="
CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT);
DELIMITER ;;
CREATE TRIGGER `tr_insert_test` AFTER INSERT ON `test` FOR EACH ROW BEGIN UPDATE log SET dirty = 1 WHERE data = DATE(NEW.emissione); END;;
DELIMITER ;";
$mysqli->multi_query($sql);

With this I get: 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 'DELIMITER ;; CREATE TRIGGER tr_insert_test AFTER INSERT ON `test' at line 1

If I try the same script inside phpmyadmin it works... can you tell me why? Thanks

score 1 · Accepted Answer · answered Jun 26 '13 at 11:16

Since multi_query (afaik) does not handle custom delimiters, the simplest way is probably splitting it into two separate, normal queries which do not need delimiters at all;

<?php
  $mysqli = new mysqli('localhost', 'test', '', 'test');

  $sql = "CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, emissione INT)";
  $mysqli->query($sql);

  $sql = "
  CREATE TRIGGER `tr_insert_test` AFTER INSERT ON `test` 
    FOR EACH ROW BEGIN 
      UPDATE log SET dirty = 1 WHERE data = DATE(NEW.emissione);
    END
  ";
  $mysqli->query($sql);

PHP Mysqli multi_query and trigger's delimiter

1 Answers1