Why do the line breaks dissapear when given to mysqli_query?

Question

This Query to insert a trigger works when I paste it into phpmyadmin query window on the database in question. but when I try to query it to the database with mysqli_query the line breaks seem to disappear causing it to fail.

CODE:

$TriggerQwry = 'Delimiter $$

                DROP TRIGGER IF EXISTS sign_chat;

    $$

    CREATE TRIGGER sign_chat BEFORE INSERT ON dark_taigachat FOR EACH ROW BEGIN

    IF (INSTR(NEW.message,"(") != 1) THEN

    SET NEW.message = CONCAT("(MC) ", NEW.message);

    END IF ;

    END ;';

    $DBConnect = mysqli_connect("localhost", "root", "************");
        mysqli_set_charset($DBConnect, "utf8");
        mysqli_select_db($DBConnect, 'minecraft_xenforo');                          

        $QueryResult = mysqli_query($DBConnect, $TriggerQwry);
        if($QueryResult) 
        {
            $result = mysqli_affected_rows($DBConnect);
        }
        else 
        {
            $result = 0; 
            echo "<p>Unable to execute the query.[".$TriggerQwry."]</p>" 
            . "<p>Error code " . mysqli_errno($DBConnect)
            . ": " . mysqli_error($DBConnect) . "</p>";
        }
        echo "Result[$Result]";
        mysqli_close($DBConnect);

OUTPUT:

Unable to execute the query.[Delimiter $$ DROP TRIGGER IF EXISTS sign_chat; $$ CREATE TRIGGER sign_chat BEFORE INSERT ON dark_taigachat FOR EACH ROW BEGIN IF (INSTR(NEW.message,"(") != 1) THEN SET NEW.message = CONCAT("(MC) ", NEW.message); END IF ; END ;]

Error code 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Delimiter $$ DROP TRIGGER IF EXISTS sign_chat; $$ CREATE ' at line 1

I also tried using full quotes "" and \n and \n\r and \r\n instead on the TriggerQwry string but all had the same result.

Whats going on here?!

Answer 1

You are essentially attempting to use MySQLi's multi_query however MySQLi doesn't handle the delimiters. You should separate the two queries and this way you will not need delimiters.

Try:

<?php
$DBConnect = mysqli_connect("localhost", "root", "************");
mysqli_set_charset($DBConnect, "utf8");
mysqli_select_db($DBConnect, 'minecraft_xenforo'); 

$sql = "DROP TRIGGER IF EXISTS sign_chat";
$DBConnect->query($sql);

$sql = "
CREATE TRIGGER sign_chat BEFORE INSERT ON dark_taigachat 
  FOR EACH ROW BEGIN
    IF (INSTR(NEW.message,"(") != 1) THEN
        SET NEW.message = CONCAT("(MC) ", NEW.message);
    END IF ;
  END
";
$DBConnect->query($sql);