MySQL : Check if field exists

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.

Comments

  1. Nad on November 10, 2008

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

    has to be changed to

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

    Reply
  2. rubo77 on January 28, 2009

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

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

    or so?

    Reply
  3. […] the above snippet provided by Antonie Potgieter, the concept is good but it is still not optimum. A better alternative can be written as […]

    Reply
  4. Bruce on October 27, 2009

    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

    Reply
  5. vladimir on January 30, 2010

    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);
    }
    }
    ?>

    Reply
  6. bawey on April 30, 2010

    function findColumn($database, $tableName, $columnName){
    $result = $database->query(“select * from “.$tableName.”;”);
    while ($retrievedColumn=$result->fetch_field()){
    if ($retrievedColumn->name==$columnName){
    return true;
    }
    }
    return false;
    }

    Reply
  7. Achshar on June 29, 2010

    thnxx 🙂 but yes mysql_list_fields is indeed depreciated..

    Reply
  8. get ex bf back on March 11, 2011

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

    Reply
  9. wvasconcelos on April 6, 2012

    Following is yet another approach that works:

    $chkcol = mysql_query(“SELECT * FROM `table_name` LIMIT 1”);
    $mycol = mysql_fetch_array($chkcol);
    if(isset($mycol[‘my_column_name’]))
    echo “Column my_column_name exists! Do something…”;

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

%d bloggers like this: