Check if a mysql field exist and create if not

Question

I am implementing the geonames webservice, like this:

$( "#loc_from" ).autocomplete({
    source: function( request, response ) {
        $.ajax({
            url: "http://ws.geonames.org/searchJSON",
            dataType: "jsonp",
            data: {
                featureClass: "P",
                style: "full",
                maxRows: 12,
                name_startsWith: request.term,
                searchlang: 'ES'
            },
            success: function( data ) {
                response( $.map( data.geonames, function( item ) {
                    item.label = item.name + (item.adminName1 ? ", " + item.adminName1 : "") + ", " + item.countryName;
                    return item;
                }));
            }
        });
    },
    minLength: 2,
    select: function( event, ui ) {
         ui.item.task = 'new_city';
         $.post('/includes/router.php', ui.item , function(){

         });
         $('#id_loc').val(ui.item.gid);
         //alert(ui.item.gid);
    }
});

where in router.php I have:

case 'new_city':
    /* comprobar si exite */
    $query = "SELECT geonameid FROM geonames";
    if(count(cache_query($query))>0){
        /* Its already in my database */
    }else{
        foreach ($_POST as $key => $value) {
                    check_field($key);
            echo $key.': '.$value.'<br>';
        }
    }
    /* si no existe, dar de alta*/
    break;

So I would like the function check_field($key) to check if the geonames.$key field exists and alter the table and add it.

Is that posible? ( Also, Do you recommend this? )

Answer 1

I think you are looking for this

SELECT * 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'geonames' 
AND COLUMN_NAME = 'column_name'

From PHP you can do this

function add_column_if_not_exist($db, $column, $column_attr = "VARCHAR( 255 ) NULL" ){
    $exists = false;
    $columns = mysql_query("show columns from $db");
    while($c = mysql_fetch_assoc($columns)){
        if($c['Field'] == $column){
            $exists = true;
            break;
        }
    }      
    if(!$exists){
        mysql_query("ALTER TABLE `$db` ADD `$column`  $column_attr");
    }
}