Tutorial: How to check whether a particular field exist on SQL table

I was doing some plugin enhancement when i needed to find out whether a field exist on the SQL table. Hence, i went Google and search for a good answer. I found two method to solve this problem but i prefer the one using pure SQL query. Hence, i decide to write them down for future use and also make it as a discussion regarding the pros and cons.

PHP Solution

The first solution i found was a method that used PHP and perform a calculation in order to conclude whether the field exist on the SQL table. Here's the snippets:

$fields = mysql_list_fields("databasename", "tablename");
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {
    $field_array[] = mysql_field_name($fields, $i);
}
       
if (!in_array(‘list_id’, $field_array)) {
    mysql_query("ALTER TABLE `tablename` ADD `fieldname` INT(11) NOT NULL DEFAULT ‘1′;");
}

From the above snippet provided by Antonie Potgieter, the concept is good but it is still not optimum. A better alternative can be written as follow.

$result = mysql_query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME ='tablename'");
$field_array = mysql_fetch_array($result);      
if (!in_array(‘list_id’, $field_array)) {
    mysql_query("ALTER TABLE `tablename` ADD `fieldname` INT(11) NOT NULL DEFAULT ‘1′;");
}

The code above have not been tested but the query, yes. Nonetheless, a loop can be removed anyhow by retrieving the set of array resist within the return value of mysql_fetch_array. The objective is to avoid loop and shorten the code as much as possible.

SQL Solution

I found a better alternative on DZone! I find that it is a much effective way to check the existence of a field. Here's the snippets:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TEST’ AND COLUMN_NAME = ‘TEST_DATE’)
BEGIN
   ALTER TABLE TEST ADD TEST_DATE DATETIME
END

Since we have taken away all the necessary code, only a direct query is needed in this case.

Summary

Which method would you prefer in this case? Why? Any better method than the one present above? I bet these two are the best we have for checking up a field existence unless you are talking about other languages than PHP. I would love to know your thoughts 🙂

No related posts.