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.

  • Nad

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

    has to be changed to

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

  • rubo77

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

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

    or so?

  • Pingback: Tutorial: How to check whether a particular field exist on SQL table - Hungred.com

  • http://www.imfirewall.us Bruce

    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

  • http://shinephp.com vladimir

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

  • http://www.bawey.pl bawey

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

  • http://achshar.com Achshar

    thnxx :) but yes mysql_list_fields is indeed depreciated..

  • http://www.winyourex.com get ex bf back

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

  • http://www.linkedin.com/in/wvasconcelos wvasconcelos

    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…”;