Dynamically add fileds to MySQL table using PHP

[lobo235]

I am working on a script that will be copied to multiple servers. It will add a few new features to an existing script but these new features also require some new fields to be added to the database on each site. I have looked around for an easy way to do this but i keep getting stuck. I want the script to do the following:

1. Check to see if the table already has the fields in question.
2. If it has the fields, run a query.
3. If it doesn't have the fields, add the fields and then run the query.

These are the areas I am getting stuck on:

How do I check a table to see if it has the field I am looking for? By running a check to see if those fields exist before running the query am I going to be producing a lot of overhead?

[jatar_k]

try this
mysql_fetch_field [php.net]
also read through the function descriptions on this page, something else might jump out at you
[php.net...]

I needed this one time, might give you some insight as well
getting information about mysql tables and fields with php [webmasterworld.com] msg16

[lobo235]

I found a way to do this. Here is the function I wrote to see if the field exists or not:

// This function will check to see if a field exists in a table 
function fieldExists($table, $field) 
{ 
 $ret = FALSE; 
 $q = "DESC ".$table; 
 $fields = getSelectArray($q); 
 foreach($fields as $val) 
 { 
 if($val['Field'] == $field) 
 $ret = TRUE; 
 } 
 return $ret; 
} // end function fieldExists()

So if the field does not exist I run the query to alter the table and add it:

if(!fieldExists('mytable', 'myfield') 
{ 
 $q = "ALTER TABLE `mytable` ADD `myfield` VARCHAR(16) NOT NULL AFTER `anotherfield`"; 
 mysql_query($q); 
}

Just in case anyone is wondering what getSelectArray() does, here it is:

// This function creates an associative, multidimensional array from a SQL SELECT statement. 
function getSelectArray( $query ) 
{ 
 $res = mysql_query( $query ); 
 $resnum = mysql_num_rows( $res ); 
 for ( $i = 0; $i < $resnum; $i++ ) 
 { 
 $t[$i] = mysql_fetch_assoc( $res ); 
 } 
 return $t; 
} // end function getSelectArray( )

I use this for getting results for all SELECT queries; using a foreach loop it is easy to throw results into a table.