Skip to content

Instantly share code, notes, and snippets.

@lgaetz
Last active August 29, 2015 14:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save lgaetz/11059268 to your computer and use it in GitHub Desktop.
Save lgaetz/11059268 to your computer and use it in GitHub Desktop.
All in one function to create/edit a FreePBX module table for usage in install.php
<?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>";
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment