I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql library that only executes a single statement in one call. This the query I was attempting to use.
SELECT COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';
Unfortunately the DATABASE() function returns information_schema instead of current connection DB at least in PHPMyAdmin. It may be different through the PHP mysql driver. Therefore, it returns zero for the column count.
SELECT DATABASE(), COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';
This returns: "Information_schema", "0" despite the fact that the original connection was to a different database.
I have come up with a workaround in PHP but would still like to know if this is possible using only SQL.