I try to check if all the column exist in a MySql ddb. I use this answer MySQL add column if not exist but i have a 1064 error. Anyboby can helps me ?
Thanks
foreach($source_bdd as $each_column)
{
$source_column = explode('***',$each_column);
$nullable = $source_column[4] == 'YES' ? 'DEFAULT NULL' : 'NOT NULL';
$verify_and_update_column .= '
DELIMITER $$
DROP PROCEDURE IF EXISTS Alter_Table_'.$source_column[0].''.$source_column[1].' $$
CREATE PROCEDURE Alter_Table_'.$source_column[0].''.$source_column[1].'()
BEGIN
DECLARE '.$source_column[0].''.$source_column[1].'_count INT;
SET '.$source_column[0].''.$source_column[1].'_count = ( SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "'.$source_column[0].'" AND
COLUMN_NAME = "'.$source_column[1].'");
IF '.$source_column[0].''.$source_column[1].'_count = 0 THEN
ALTER TABLE '.$source_column[0].'
ADD COLUMN '.$source_column[1].' '.$source_column[9].' '.$nullable.';
END IF;
END $$
CALL Alter_Table_'.$source_column[0].''.$source_column[1].'() $$
DELIMITER ;
';
}
Here is an extract of the query and the error at the end :
DELIMITER $$
DROP PROCEDURE IF EXISTS Alter_Table_table_1_column_1 $$
CREATE PROCEDURE Alter_Table_table_1_column_1()
BEGIN
DECLARE table_1_column_1_count INT;
SET table_1_column_1_count = ( SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "table_1" AND
COLUMN_NAME = "table_1");
IF table_1_column_1_count = 0 THEN
ALTER TABLE table_1
ADD COLUMN table_1 varchar(512) DEFAULT NULL;
END IF;
END $$
CALL Alter_Table_table_1_column_1() $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS Alter_Table_table_1_column_2 $$
CREATE PROCEDURE Alter_Table_table_1_column_2()
BEGIN
DECLARE table_1_column_2_count INT;
SET table_1_column_2_count = ( SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "table_1" AND
COLUMN_NAME = "table_1");
IF table_1_column_2_count = 0 THEN
ALTER TABLE table_1
ADD COLUMN table_1 varchar(512) DEFAULT NULL;
END IF;
END $$
CALL Alter_Table_table_1_column_2() $$
DELIMITER ;
….
DELIMITER $$
DROP PROCEDURE IF EXISTS Alter_Table_table_25_column_12 $$
CREATE PROCEDURE Alter_Table_table_25_column_12()
BEGIN
DECLARE table_25_column_12_count INT;
SET table_25_column_12_count = ( SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "table_1" AND
COLUMN_NAME = "table_1");
IF table_25_column_12_count = 0 THEN
ALTER TABLE table_1
ADD COLUMN table_1 varchar(512) DEFAULT NULL;
END IF;
END $$
CALL Alter_Table_table_25_column_12() $$
DELIMITER ;
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 $$ DROP PROCEDURE IF EXISTS Alter_Table_table_1_column_1 $$ CREATE PROC' at line 11064