TheSQL throws error, but if copied from error message or die() into phptheadmin it works

errorsMySQLstored-procedures

I want to get a script running, which creates a column in a table, if does not exist. I am using the script of user abahet from mysql ALTER TABLE if column not exists

My php code (inside of the db-class) looks like this:

$sql = '
    SET @preparedStatement = (SELECT IF(
        (SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE table_name = "' . $this->t($table) . '"
                 AND table_schema = DATABASE()
                 AND column_name = "' . $column . '"
        ) > 0,
        "SELECT 1;",
        "ALTER TABLE ' . $this->t($table) . ' ADD ' . $column . ' ' . $params . ';"
    ));

    PREPARE alterIfNotExists FROM @preparedStatement;
    EXECUTE alterIfNotExists;
    DEALLOCATE PREPARE alterIfNotExists;
';
//die($sql);
$this->db($sql,'utf8');

Once executed, it throws the following error:

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 'PREPARE alterIfNotExists FROM @preparedStatement;
            EXECUTE alterIfNotExists;
    ' at line 12

            SET @preparedStatement = (SELECT IF(
                (SELECT COUNT(*)
                        FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE table_name = "ddpos_2_user"
                         AND table_schema = DATABASE()
                         AND column_name = "id_signature"
                ) > 0,
                "SELECT 1;",
                "ALTER TABLE ddpos_2_user ADD id_signature mediumint(7) DEFAULT 0;"
            ));

            PREPARE alterIfNotExists FROM @preparedStatement;
            EXECUTE alterIfNotExists;
            DEALLOCATE PREPARE alterIfNotExists;

But when I copy the query from the error message into phpmyadmin, or take the output directly from die($sql) into phpmyadmin, the procedure works fine without errors, and the column id_signature is created.

Why?

Best Answer

It looks like you are having trouble executing multiple lines of SQL inside the one string. You need to find out of PHP Class can handle running multiple lines.

Instead of getting the SQL to run dynamic SQL, just execute it yourself

$sql = 'SELECT IF(
    (SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = "' . $this->t($table) . '"
             AND table_schema = DATABASE()
             AND column_name = "' . $column . '"
    ) > 0,
    "SELECT 1;",
    "ALTER TABLE ' . $this->t($table) . ' ADD ' . $column . ' ' . $params . ';")';

Execute this one line, take that resulting string, and execute that.

Hey, it may means running two SQL commands, but it will work.

An alternative would be to write a stored procedure with that code and then call the stored procedure.

Please look at my answer to the post MySQL: Create index If not exists where I used dynamic SQL to create an index if an index does not exist. You could frame a stored procedure for yourself and call it from PHP.

Related Question