mySQL throws error, but if copied from error message or die() into phpmyadmin it works

Question

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?

• Rick James: Rewrite it so that you don't do the "select 1" kludge.

Answer 1

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.

• ddlab: Thumbs up :-) I added AS tmp_sql; at the end of the 1st query, then I needed to add only three lines, which are to perform the 2nd query $sql = ''; if ($result) $sql = $result->fetch_object()->tmp_sql; if (substr($sql,0,5) == 'ALTER') $this->db($sql,'utf8');. Thanks a lot.
• ddlab: But then, if I use two queries, I can make it simplier like this: $sql = 'SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = "' . $this->t($table) . '" AND table_schema = "ddpos" AND column_name = "' . $column . '" ;'; //die($sql); $result = $this->db($sql,'utf8'); $cnt = 0; if ($result) $cnt = $result->fetch_object()->cnt; if ($cnt == 0) $this->db('ALTER TABLE ' . $this->t($table) . ' ADD ' . $column . ' ' . $params . ';','utf8');
• RolandoMySQLDBA: You are right. That is far simpler. The approach in my answer and your question would be better suited for stored procedures. That way there is only one call instead of two SQL queries from a PHP client.
• ddlab: Finally I dropped the Information_schema part and replaced it with SHOW COLUMNS FROM ' . $this->t($table) . ' LIKE "' . $column . '" and return isset($result->fetch_object()->Field) ? TRUE : FALSE ; since I am afraid not to have access to Information_schema after the project is published and running on a hosted server.