Case Study: Handling MySQL Growth With a PHP Class

Case Study: Handling MySQL Growth With a PHP Class - The Tricky Part
(Page 3 of 5 )

Here comes the tricky part -– keeping the structure defined in the object and the structure of the database and its tables in sync. Here's the entire function, called syncTable:

function syncTable() {
// Step 1: Create table if it does not exist
$fullSQL = "CREATE TABLE IF NOT EXISTS `$this->tableName` (";
for( $i=0; $i<count($this->fields); $i++) {
$fieldName = $this->fields[$i][0];
$fullSQL .= "`$fieldName`"." ".$this->fields[$i][1].", ";
}
$fullSQL .= "PRIMARY KEY ($this->uniqueField));";

// Execute query on database
$result = WSdb::query($fullSQL);

// Step 2. Traverse through the fields and see if they match, if not -modify
$sql = "SHOW FIELDS FROM `$this->tableName`";
$result = WSdb::query($sql);

// populate the table_fields array with results
while($row=mysql_fetch_array($result)) {
$table_fields[] = array($row[0],$row);
}
$this->table_fields = $table_fields;

// Traverse through alle the field in the structure and compare
for( $i=0; $i<count($this->fields); $i++) {
// The following code simply runs through the array searching for
// a field with the same name from the table_fields array.
$fieldName = $this->fields[$i][0];
$fieldExists = false;
$fieldPosition = -1; // holds the position of the field in the table_fields array

for( $j=0; $j<count($table_fields); $j++) {
if($fieldName==$table_fields[$j][0]) {
$fieldExist = true;
$fieldPosition = $j; // $j marks the spot!
} else
$fieldExist = false;

if($fieldExist) {
// We found the field, now we can move on! :)
// the names match, does the type?
$field_type = substr($this->fields[$i][1],0,strlen($table_fields[$i][1][1]));
if($field_type==$table_fields[$i][1][1]) {
echo "Field match! Field comparator : ".$this->fields[$i][0]." $field_type<br>";
$this->fields_ok[] = $this->fields[$i];
}
// the type does not match, modify it
else {
$sql = "ALTER TABLE `$this->tableName` CHANGE `$fieldName` `$fieldName` ".$this->fields[$i][1];
echo $sql."<br>";
$result = WSdb::query($sql);
$this->fields_ok = $this->fields[$i];
}
}
} // for $j..
} // for $i
if(count($this->fields_ok)==count($this->fields) ) {
echo "<strong>all fields are OK! :) </strong><br>";
} else {
echo "<strong>there are some fields missing</strong> <br>";
for( $i=0; $i<count($this->fields); $i++) {
$field_is_ok = false;
$fieldName = $this->fields[$i][0];
for( $j=0; $j<count($this->fields_ok); $j++) {
if($fieldName==$this->fields_ok[$j][0]) {
$field_is_ok = true;
}
}
if(!$field_is_ok) {
$this->missing_fields[] = $this->fields[$i];
}
}
// Now that we've found the fields that are missing, add them
for( $i=0; $i<count($this->missing_fields); $i++) {
$sql = "ALTER TABLE `$this->tableName` ADD `".$this->missing_fields[$i][0]."` ".$this->missing_fields[$i][1];
echo $sql."<br>";
$result = WSdb::query($sql);
$this->fields_added = true;
}
}
}

Don't be confused by the code. It works... believe me! Here's a basic overview of what the code does:

1. Create the table if it doesn’t exist
2. Get the list of fields from the table and store them in an array
3. Run through the list of fields and match the names
4. If the names match, see if the field types match (ex: int(11) == int(11) )
5. If the field types match, nothing needs to be done
6. If they don’t match, update the field so they match
7. If the field name is not found, then add the field to the table

That's it! But there are some details I left out when showing you the class WSDynamicObject. Here is the complete variable list of the class:

class WSDynamicObject {
var $fields; // an array of the fields in this structure
var $newobject; // is this a new object or loaded values from db?
var $table_fields; // the database equivalent of $this->fields
var $fields_ok; // control-list during update
var $missing_fields;// a list over the fields not added to the table
var $formmode;
}
blog comments powered by Disqus