Nolan Ritchie Nolan Ritchie - 6 months ago 23
SQL Question

Why do the line breaks dissapear when given to mysqli_query?

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

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);