How Can I Reliably Check for Column Existence in a MySQL Table?

Determining Column Existence in a MySQL Table

In MySQL, verifying the presence of a column in a table can be a bit perplexing compared to other database systems. The commonly attempted method:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME='prefix_topic' AND column_name='topic_last_update') 
BEGIN 
ALTER TABLE `prefix_topic` ADD `topic_last_update` DATETIME NOT NULL;
UPDATE `prefix_topic` SET `topic_last_update` = `topic_date_add`;
END;

seems to fail.

However, a reliable solution involves using the SHOW COLUMNS command:

SHOW COLUMNS FROM `table` LIKE 'fieldname';

PHP Implementation

To determine column existence in PHP, you can use the mysql_num_rows() function:

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

This approach allows you to swiftly check for the presence of a specific column in a MySQL table, ensuring the proper handling of non-existent columns.

The above is the detailed content of How Can I Reliably Check for Column Existence in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!