Conditionally Add Fields in MySQL DB Table

The Problem

When building an installation script for your software you usually have to create the required database tables. This is easy to do with running simple DB queries. Works great when installing first version. But once you have to start upgrades it can quickly become a nightmare. Of course you can't always create the tables from scratch because the user might already have some data in them! So you have to update them by adding the new fields only.

The Typical Solution

The usual way of handling this is storing the current version in the database, then running ALTER TABLE queries only if the version installed is older than the version when the new fields are added. This is a decent approach but rather error-prone because you have to be very careful of updating version numbers and assigning new fields to them.

The Better Approach

Instead of this, I am suggesting you the simpler approach we follow with our Wordpress plugins - for example BroadFast for Wordpress. We do frequent upgrades there and instead of trying to match ALTER TABLE statements to version numbers, we choose a conditional approach to adding fields in the tables. Conditional simply means that we'll check if a field is already in the table, and if not, we'll add it only then. Let's see the function:

1. // $fields - array of arrays. Each field is associative array having name and type.  
2. // see the sample call after the function to get better idea  
3. // $table - the name of the target table  
4. function add_db_fields($fields, $table) {  
5.     global $wpdb; // this is in WordPress, you may use some other object in your system  
6.       
7.     // get existing fields  
8.     $table_fields = $wpdb->get_results("SHOW COLUMNS FROM `$table`");  
9.     // let's store the names only in this array  
10.     $table_field_names = array();  
11.     foreach($table_fields as $f) $table_field_names[] = $f->Field;  
12.       
13.     // and this is the array of fields that we'll need to add         
14.     $fields_to_add=array();  
15.       
16.     // let's fill $fileds_to_add  
17.     foreach($fields as $field) {  
18.          if(!in_array($field['name'], $table_field_names)) {  
19.               $fields_to_add[] = $field;  
20.          }   
21.     }  
22.       
23.     // now if there are fields to add, run the query  
24.     if(!empty($fields_to_add)) {  
25.          $sql = "ALTER TABLE `$table` ";  
26.            
27.          foreach($fields_to_add as $cnt => $field) {  
28.              if($cnt > 0) $sql .= ", ";  
29.              $sql .= "ADD $field[name] $field[type]";  
30.          }   
31.            
32.          $wpdb->query($sql);  
33.     }  
34. }  

This is the whole function you need. It gets your array with fields, for each field check if it exists, and if not, adds it. The function can be called with multiple fields, but should be called once for each table where you are adding fields. Here is how to call the function:

1. $fields = array(  
2.       array("name"=>"sender", "type"=>"VARCHAR(255) NOT NULL DEFAULT ''"),  
3.       array("name"=>"require_name", "type"=>"TINYINT UNSIGNED NOT NULL DEFAULT 0"),  
4.       array("name"=>"auto_subscribe", "type"=>"VARCHAR(255) NOT NULL DEFAULT ''")  
5. );  
6. add_db_fields($fields, "mailing_lists");  

There isn't much to comment here. The table is called "mailing_lists" and we are conditionally adding three fields to it. Note that $fields is array of associative arrays, and each of them has name and type. The field "type" contains the full list of SQL arguments for the fields, not only the type. Obviously this function works only for adding new fields. This is the most common operation when releasing upgrades. Of course, you can expand it further to support CHANGE queries (but it won't change field names).