CODEWITHSUNDEEP
phpmysql
Drew
Drew

Reputation: 3234

Need to find out of a table has certain columns before running alter table

I am writing a plugin for wordpress. When the plugin is initialised I need to find out if the users table contains the columns I am trying to insert to ensure I am not overwriting anything. Can someone provide me with the syntax that does this; I think it looks something like this:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

Thanks

Upvotes: 1

Views: 103

Answers (2)

dusoft
dusoft

Reputation: 11469

pseudocode:

DBQuery("SHOW COLUMNS FROM ".$table);
while (DBGetRow())
   {
   $columns[]=$access["Field"];
   }

Upvotes: 0

chaos
chaos

Reputation: 124297

If you're just trying to find out if the column exists, I'd do

SELECT 1
FROM information_schema.COLUMNS
WHERE COLUMN_NAME = 'column_name'
AND TABLE_NAME = 'table_name'
AND TABLE_SCHEMA = 'database_name'
LIMIT 1

Upvotes: 2

Related Questions