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 🙂
