How To 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  
  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;  
  13.     // and this is the array of fields that we'll need to add         
  14.     $fields_to_add=array();  
  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.     }  
  23.     // now if there are fields to add, run the query  
  24.     if(!empty($fields_to_add)) {  
  25.          $sql = "ALTER TABLE `$table` ";  
  27.          foreach($fields_to_add as $cnt => $field) {  
  28.              if($cnt > 0) $sql .= ", ";  
  29.              $sql .= "ADD $field[name] $field[type]";  
  30.          }   
  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).


  1. I believe Web time sheet software makes the complete employee time clock tracking task easier. Its easy to update, approve and maintain the time sheets in no

    time.Time Attendance System

  2. It is very nice to manage code and lesser redundancy. It is always appreciable to get customize solutions. I am also using hand written code for PHP Web Application Development for any websites.

  3. Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    Awesome post
    <a title="http://www.hadooponlinetraining.net/>hadoop training</a>

  4. lately, I did not give plenty of thought to giving responses on site page reports and have positioned responses even less.

    Reading through your pleasant content, will assist me to do so sometimes.
    <a title="http://www.microsoftdynamicsonlinetraining.com/>dynamics training</a>

  5. Blasphemy! LOL Just kidding. Ive read similar things on other blogs. Ill take your word for it. Stay solid! your pal.
    Hmm, that is some compelling information youve got going! Makes me scratch my head and think. Keep up the good writing!
    <a title="http://www.maacareer.com/>Latest Jobs</a>

  6. Hey! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on.
    You have done an outstanding job!

    PHP training in Lucknow

  7. Really good information to show through this blog. I really appreciate you for all the valuable information that you are providing us through your blog. Application Development using Angularjs and MongoDB

  8. Thanks for the great information in your blog PHP


  9. this blog is new and informative , it is really interesting and useful too , thanks for sharing this information.

    php training in chennai

  10. Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post. graphic designing courses in jalandhar

  11. Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.

    SEO Company in Chennai

  12. Nice to see your blog post.. I really enjoyed by reading your blog post. Thanks a lot for sharing this with us.. Keep on sharing like this informative post.

    Branding Services in Chennai

  13. My friend Suggest me this blog and I can say this is the best blog to get the basic knowledge.Thank you so much for this Selenium Training in Chennai

  14. Well explained. Got to learn new things from your Blog on Appium.Appium training in chennai

  15. Thanks for this blog. provided great information. All the details are explained clearly with the great explanation. Thanks for this wonderful blog. Step by step processes execution are given clearly.Know the details about different thing.
    Digital Marketing Company in Chennai

  16. Really Good blog post.provided a helpful information about how to conditionally add fields in mysql.keep updating...
    Digital marketing company in Chennai

  17. Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp great deal more around this condition.
    Authorized apple service center in Chennai | apple service center in chennai | Authorized apple service center in Chennai | apple service center in chennai | Mobile service center in chennai | iphone water damage service