CODEWITHSUNDEEP
phpmysql
Steve Price
Steve Price

Reputation: 600

SQL error when running command from php file

I need to check if a column exists before trying to add it if not present. Following information found in this post: mysql ALTER TABLE if column not exists

I added the following to my zen cart php file

$db->Execute("DROP PROCEDURE IF EXISTS `gdpr_accept`;
              DELIMITER //
              CREATE PROCEDURE `gdpr_accept`()
              BEGIN
              DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
              ALTER TABLE " . TABLE_CUSTOMERS . " ADD `gdpr_accept` TINYINT(1) NOT NULL DEFAULT '0' AFTER `COWOA_account`;
              END //
              DELIMITER ;
              CALL `gdpr_accept`();
              DROP PROCEDURE `gdpr_accept`;");

However, I get the following error logged

[05-May-2018 19:37:02 Europe/Paris] PHP Fatal error: 1064: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 PROCEDURE gdpr_accept() BEGIN ' at line 2 :: DROP PROCEDURE IF EXISTS gdpr_accept; DELIMITER // CREATE PROCEDURE gdpr_accept() BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; ALTER TABLE customers ADD gdpr_accept TINYINT(1) NOT NULL DEFAULT '0' AFTER COWOA_account; END // DELIMITER ; CALL gdpr_accept(); DROP PROCEDURE gdpr_accept; ==> (as called by) /Applications/MAMP/htdocs/gdpr/stOrm-PTO-fluSh/includes/init_includes/init_reconsent_popup_setup.php on line 72

However, when I run the same command in phpMyAdmin, after confirming that i want to "DROP PROCEDURE IF EXISTS gdpr_accept" it runs perfectly.

Note: If i attempt to split up the query, it will fail at

$db->Execute("DELIMITER //");

with this error: 1064 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 //' at line 1

Is there a reason why this SQL command can't be done via php, and is there a way round it?

Upvotes: 1

Views: 91

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

DELIMITER is a builtin command in the mysql client, it is not a statement that the MySQL server recognizes. You can't run a DELIMITER statement using PHP.

But you don't need to use DELIMITER. That's only to help the mysql client tell where your CREATE PROCEDURE statement ends, because a procedure usually contains semicolon characters, and otherwise it would be ambiguous which semicolon was part of the procedure body versus the end of the procedure definition.

You should run one statement at a time from PHP. Then it's not ambiguous.

$db->Execute("DROP PROCEDURE IF EXISTS `gdpr_accept`");
$db->Execute("CREATE PROCEDURE `gdpr_accept`()
              BEGIN
              DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
              ALTER TABLE " . TABLE_CUSTOMERS . " ADD `gdpr_accept` TINYINT(1) NOT NULL DEFAULT '0' AFTER `COWOA_account`;
              END");
$db->Execute("CALL `gdpr_accept`()");
$db->Execute("DROP PROCEDURE `gdpr_accept`;");

By the way, there's no reason you need a procedure for this task, since you just drop the procedure when you're done anyway. It would be much simpler to just run the ALTER TABLE directly:

$db->Execute("ALTER TABLE " . TABLE_CUSTOMERS . 
    " ADD `gdpr_accept` TINYINT(1) NOT NULL DEFAULT '0' AFTER `COWOA_account`");

I see a lot of questions on Stack Overflow from people who seem to think it's a good idea to use stored procedures in MySQL. While stored procedures are common in Oracle and Microsoft SQL Server, they're more trouble than they're worth in MySQL. I avoid using stored procedures in MySQL.

Upvotes: 1

Related Questions