. SQL . SQL MySQL Add New Column to a Table With PHP

SQL MySQL Add New Column to a Table With PHP

Here is a little PHP function which will take a MySQL table and dynamically add a column to the table, if the table does not already have that column.

While it would probably be easier just to use phpMyAdmin to alter a MySQL table, you might need to alter a table on a server where that tool was not available to you.

I used this code for a client who did not have phpMyAdmin. I was altering their existing system, and I wanted to give them one complete new set of code. Without doing this I would have had to first give them one program to alter their table(s), asked them to run that, and then given them the new modules for their system. Of course, the next time I work on their system I do need to pull this code, as it would probably be best not to keep checking if that new column exists forever.

This function could be called perhaps by a login function or before a database call where the new column was to accessed.

mysqlAddColumn.php

//Copyright Lawrence Truett and www.FluffyCat.com May 29, 2007  


  function addColumnIfItDoesNotExist() {
    //this query checks for the new column
    $query = ("SHOW COLUMNS ".
              "FROM tableNameToAddColumnTo ".
              "LIKE '%newColumnName%'");
    $result = mysql_query($query)  
      or die("select table tableNameToAddColumnTo ".
             "in addColumnIfItDoesNotExist() not successful: ".
      mysql_error());
    $rarray = mysql_fetch_array($result);
    if (NULL == $rarray[0]) {
      //here are the specifics for your new column, 
      //  both the datatype and the position in the table
      $query = ("ALTER TABLE tableNameToAddColumnTo ".
                "ADD COLUMN newColumnName VARCHAR(100) ".
                "AFTER columnBeforeNewColumn;");
      $result = mysql_query($query) 
        or die("altering table tableNameToAddColumnTo not successful: ".
        mysql_error());      
    }
    return;
  }

download source, use right-click and "Save Target As..." to save with a .php extension.

Comments

to add the first comment for SQL MySQL Add New Column to a Table With PHP.