MySQL
  Home arrow MySQL arrow Page 3 - Case Study: Handling MySQL Growth With a P...
Dev Articles Forums 
ADO.NET  
Apache  
ASP  
ASP.NET  
C#  
C++  
ColdFusion  
COM/COM+  
Delphi-Kylix  
Design Usability  
Development Cycles  
DHTML  
Embedded Tools  
Flash  
Graphic Design  
HTML  
IIS  
Interviews  
Java  
JavaScript  
MySQL  
Oracle  
Photoshop  
PHP  
Reviews  
Ruby-on-Rails  
SQL  
SQL Server  
Style Sheets  
VB.Net  
Visual Basic  
Web Authoring  
Web Services  
Web Standards  
XML  
Moblin 
JMSL Numerical Library 
IBM® developerWorks 
Sun Developer Network 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

Case Study: Handling MySQL Growth With a PHP Class
By: Bjorn Roysland
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 9
    2002-11-12

    Table of Contents:
  • Case Study: Handling MySQL Growth With a PHP Class
  • Generic MySQL Tables
  • The Tricky Part
  • Formmode
  • Conclusion

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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;
    }

    More MySQL Articles
    More By Bjorn Roysland


     

    MYSQL ARTICLES

    - MySQL and BLOBs
    - Two Lessons in ASP and MySQL
    - Lord Of The Strings Part 2
    - Lord Of The Strings Part 1
    - Importing Data into MySQL with Navicat
    - Building a Sustainable Web Site
    - Creating An Online Photo Album with PHP and ...
    - Creating An Online Photo Album with PHP and ...
    - PhpED 3.2 – More Features Than You Can Poke ...
    - Creating An Online Photo Album with PHP and ...
    - Creating An Online Photo Album with PHP and ...
    - Security and Sessions in PHP
    - Setup Your Personal Reminder System Using PHP
    - Create a IP-Country Database Using PERL and ...
    - Developing a Dynamic Document Search in PHP ...






    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway
    Stay green...Green IT