MySQL, Check if a column exists in a table with SQL

This works well for me.

SHOW COLUMNS FROM `table` LIKE 'fieldname';

With PHP it would be something like...

$result = mysql_query("SHOW COLUMNS FROM `table` LIKE 'fieldname'");
$exists = (mysql_num_rows($result))?TRUE:FALSE;

@julio

Thanks for the SQL example. I tried the query and I think it needs a small alteration to get it working properly.

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'table_name' 
AND COLUMN_NAME = 'column_name'

That worked for me.

Thanks!

Just to help anyone who is looking for a concrete example of what @Mchl was describing, try something like

 SELECT * FROM information_schema.COLUMNS 
 WHERE TABLE_SCHEMA = 'my_schema' AND TABLE_NAME = 'my_table' 
 AND COLUMN_NAME = 'my_column'`

If it returns false (zero results) then you know the column doesn't exist.

Related