0

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

Community
  • 1
  • 1
Fletcher
  • 21
  • 5
  • 2
    You should name the table "calamity" – samayo Feb 20 '14 at 18:13
  • You need to show what the ACTUAL query looks like. This PHP code is pretty much useless because we have no idea what your `$source_column` text contains. `1064` is a generic syntax error, and since you havne't provided the full error code, we can't help you any more than this. – Marc B Feb 20 '14 at 18:18
  • Thanks for your answers… You're right, calamity is a good name for this job. Perhaps i do the wrong way. – Fletcher Feb 20 '14 at 19:28

0 Answers0