MySQL : Check if field exists

Mon, Sep 10, 2007

Sometimes, you need to check if a field exists in a table and if it doesn’t, you need to add it to the table and ensure that its there. Below is a snippet which you can use a reference for checking whether a field/column exists in a MySQL database table.

$tableFields = 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′;");
}

I hope that you find this useful.
The code above should give you a good idea of how to do it.

This post was written by:

Antonie Potgieter - who has written 71 posts on Lost-In-Code.

I (Antonie Potgieter) am a software engineer/web developer located in South Africa. My full-time work is the management of Tribulant Software and the development of its software packages.

Contact the author

7 Comments For This Post

Nad Says:
November 10th, 2008 at 4:39 am

$tableFields = mysql_list_fields(“databasename”, “tablename”);

has to be changed to

$fields = mysql_list_fields(“databasename”, “tablename”);

rubo77 Says:
January 28th, 2009 at 1:38 am

is it not possible to check this inside the mysql-statement?

if exists tablename.fieldname ALTER TABLE `tablename` ADD `fieldname` …

or so?

Bruce Says:
October 27th, 2009 at 9:11 pm

You can try this:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=’table’ AND column_name=’qwerty’) BEGIN SELECT * FROM table ORDER BY qwerty END

vladimir Says:
January 30th, 2010 at 11:20 pm

mysql_list_fields() function is deprecated. PHP manual recommends use
“SHOW COLUMNS FROM tablename” MySQL query instead.
Example from manual:
0) {
while ($row = mysql_fetch_assoc($result)) {
print_r($row);
}
}
?>

bawey Says:
April 30th, 2010 at 5:08 am

Achshar Says:
June 29th, 2010 at 1:02 am

thnxx but yes mysql_list_fields is indeed depreciated..

get ex bf back Says:
March 11th, 2011 at 5:01 pm

[...] MySQL : Check if field exists | Lost-In-Code [...]

1 Trackbacks For This Post

Nad Says:
November 10th, 2008 at 4:39 am
$tableFields = mysql_list_fields(“databasename”, “tablename”);

has to be changed to

$fields = mysql_list_fields(“databasename”, “tablename”);
rubo77 Says:
January 28th, 2009 at 1:38 am
is it not possible to check this inside the mysql-statement?

if exists tablename.fieldname ALTER TABLE `tablename` ADD `fieldname` …

or so?
Bruce Says:
October 27th, 2009 at 9:11 pm
You can try this:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=’table’ AND column_name=’qwerty’) BEGIN SELECT * FROM table ORDER BY qwerty END
vladimir Says:
January 30th, 2010 at 11:20 pm
mysql_list_fields() function is deprecated. PHP manual recommends use
“SHOW COLUMNS FROM tablename” MySQL query instead.
Example from manual:
0) {
while ($row = mysql_fetch_assoc($result)) {
print_r($row);
}
}
?>
bawey Says:
April 30th, 2010 at 5:08 am
function findColumn($database, $tableName, $columnName){
$result = $database->query(“select * from “.$tableName.”;”);
while ($retrievedColumn=$result->fetch_field()){
if ($retrievedColumn->name==$columnName){
return true;
}
}
return false;
}
Achshar Says:
June 29th, 2010 at 1:02 am
thnxx but yes mysql_list_fields is indeed depreciated..
get ex bf back Says:
March 11th, 2011 at 5:01 pm
[...] MySQL : Check if field exists | Lost-In-Code [...]

Tutorial: How to check whether a particular field exist on SQL table - Hungred.com Says:
September 20th, 2009 at 9:28 am
[...] the above snippet provided by Antonie Potgieter, the concept is good but it is still not optimum. A better alternative can be written as [...]

Lost-In-Code