All in one function to create/edit a FreePBX module table for usage in install.php

create_table

<?php
function create_table($tablename, $cols) {

	global $db;

	# create a table if none present 
	# The temp column will be automatically removed by the checking code below.
	$sql = "CREATE TABLE IF NOT EXISTS `$tablename` (`zzaa` INT);";
	$check = $db->query($sql);
	if (DB::IsError($check)) {
		die_freepbx( "cannot create table $tablename<br>($sql)<br>" . $check->getMessage() . "<br>");    
	}
	
	// Check all columns in $tablename and remove auto-increments which interfere with dropping primary key
	$sql = "DESCRIBE `$tablename`";
	$res = $db->query($sql);
	if (DB::IsError($res)) {
		die_freepbx( "SQL failed $tablename<br>($sql)<br>" . $res->getMessage() . "<br>");   
	}
	while($row = $res->fetchRow())  {
		if(array_key_exists($row[0],$cols)) {
			if ($row[5] == "auto_increment") {
				$sql ="ALTER TABLE $tablename MODIFY ".$row[0]." INT";
				$check = $db->query($sql);
				if (DB::IsError($check)) {
					die_freepbx( "Removing auto increment from ".$row[0]." ". $check->getMessage() .  "<br>");
				}
			}
		}
	}
	// Now that auto increments are gone, drop all primary keys
	$sql = "ALTER TABLE `$tablename` DROP PRIMARY KEY";  
	$check = $db->query($sql);   // ignoring errors because will get error if $tablename has no primary keys


	// Check to see that columns are defined properly and drop unnecessary columns
	// Scan through all existing columns in $tablename to ensure they match the definitions in $cols array
	$curret_cols = array();  // array of existing columns, needed below to add missing columns
	$sql = "DESCRIBE `$tablename`";
	$res = $db->query($sql);
	if (DB::IsError($res)) {
		die_freepbx( "SQL failed $tablename<br>($sql)<br>" . $res->getMessage() . "<br>");   
	}
	while($row = $res->fetchRow())  {
		if(array_key_exists($row[0],$cols)) {
			$curret_cols[] = $row[0];
			//make sure it has the latest definition
			$sql = "ALTER TABLE `$tablename` MODIFY `".$row[0]."` ".$cols[$row[0]];
			$check = $db->query($sql);
			if (DB::IsError($check)) {
			die_freepbx( "In table $tablename cannot update column ".$row[0]."<br>($sql)<br>" . $check->getMessage() .  "<br>");
			}
		}  
	}

	//add any missing columns that are not already in the table
	foreach($cols as $key=>$val)  {
		if(!in_array($key,$curret_cols)) {
			$sql = "ALTER TABLE `$tablename` ADD `".$key."` ".$val;
			$check = $db->query($sql);
			if (DB::IsError($check)) {
			die_freepbx( "In table $tablename cannot add column ".$key."<br>($sql)<br>" . $check->getMessage() . "<br>");
			} else {
				echo "In table $tablename added column ".$key."<br>";
			}
		}
	}

	
	// remove unneeded columns from $tablename
	$sql = "DESCRIBE `$tablename`";
	$res = $db->query($sql);
	if (DB::IsError($res)) {
		die_freepbx( "SQL failed $tablename<br>($sql)<br>" . $res->getMessage() . "<br>");   
	}
	while($row = $res->fetchRow())  {
		if(!array_key_exists($row[0],$cols)) {
			//remove the column
			$sql = "ALTER TABLE `$tablename` DROP COLUMN `".$row[0]."`";
			$check = $db->query($sql);
			if(DB::IsError($check)) {
			echo "In table $tablename cannot remove column ".$row[0]."<br>($sql)<br>" . $check->getMessage() . "<br>";  //not fatal error
			} else {
				echo "In table $tablename removed unused column ".$row[0]."<br>";
			}
		}
	}	
}