Automatically create PHP classes from MySQL

Creating a database driven web application involves commonly used paradigms for data modification, which we commonly refer to as CRUD. Frameworks provides nice ORM wrappers to help the programmer. But for small projects frameworks can be an overkill. Still the programmer needs to design the basic database CRUD functionality, which can be quite tedious and repetitive. This is where auto database class generators can be helpful. If you have your database schema ready, you can automatically create the respective class wrappers for the tables.

I found two libraries which accomplish the task without much of an overhead.
Structy and table2class.

Take a sample database schema shown below.

CREATE TABLE `user` (
  `username` varchar(100) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The following is a php class automatically generated by ‘table2class’ from the above schema. The generated class file also includes additional support database initialization files. Note that some functions here may need modification to suit your particular application, but still, the major code required to get the application up and running is there. Any automation is better than nothing. And each such help to the programmer means faster project delivery time for the client.

<?php
/*************************************************************************
* Class Name:       user
* File Name:        class.user.php
* Generated:        Thursday, Sep 15, 2011 - 6:39:33 UTC
*  - for Table:     user
*   - in Database:  user
**************************************************************************/
 
// Files required by class:
require_once("class.database.php");
 
// Begin Class "user"
class user {
    // Variable declaration
    public $username; // Primary Key
    public $database;
 
    // Class Constructor
    public function __construct() {
        $this->database = new Database();
        $this->database->SetSettings("localhost", "user", "pass", "user");
    }
 
    // Class Destructor
    public function __destruct() {
        unset($this->database);
    }
 
    // GET Functions
    public function getusername() {
        return($this->username);
    }
 
    // SET Functions
    public function setusername($mValue) {
        $this->username = $mValue;
    }
 
    public function select($mID) { // SELECT Function
        // Execute SQL Query to get record.
        $sSQL = "SELECT * FROM user WHERE username = $mID;";
        $oResult = $this->database->query($sSQL);
        $oResult = $this->database->result;
        $oRow = mysql_fetch_object($oResult);
 
        // Assign results to class.
        $this->username = $oRow->username; // Primary Key
    }
 
    public function insert() {
        $this->username = NULL; // Remove primary key value for insert
        $sSQL = "INSERT INTO user () VALUES ();";
        $oResult = $this->database->query($sSQL);
        $this->username = $this->database->lastinsertid;
    }
 
    function update($mID) {
        $sSQL = "UPDATE user SET (username = '$this->username') 
                 WHERE username = $mID;";
        $oResult = $this->database->Query($sSQL);
    }
 
    public function delete($mID) {
        $sSQL = "DELETE FROM user WHERE username = $mID;";
        $oResult = $this->database->Query($sSQL);
    }
 
}
// End Class "user"
?>

‘Structy’ provides a somewhat different flavor as you can see from the code class generated by the library. This is more complete than ‘table2class’ but also a little complex. The ‘RET’ constant you see in the code below is generated by the library and is automatically defined in the other support files.

<?php
 
/**********************************************************************
user.class.php
Generated by STRUCTY 2011.09.15 08:33:41.
Copyright 2011 Structy, Frédéric Aebi. All rights reserved.
**********************************************************************/
 
define("USER", "user"); 
 
class user {
 
    private $username;
    private $password;
 
    public function setusername($pArg="0") {$this->username=$pArg;}
    public function setpassword($pArg="0") {$this->password=$pArg;}
 
    public function getusername() {return $this->username;}
    public function getpassword() {return $this->password;}
 
    public function readObject($array = array()) {
        $qry = "SELECT *".RET."FROM ".USER.RET;
        $and = "WHERE".RET;
 
        if($array['username'] != "") {
            $qry .= $and."username = '".$array['username']."'".RET;
            $and = "AND".RET;
        }
 
        if($array['password'] != "") {
            $qry .= $and."password = '".$array['password']."'".RET;
            $and = "AND".RET;
        }
 
        $record = Database::select($qry);
        if(count($record[0]) == 0) {
            return array();
        } else {
            $record = $record[0];
            $this->setusername($record['username']);
            $this->setpassword($record['password']);
            return true;
        }
    }
 
    public static function readArray($array = array()) {
        $qry = "SELECT *".RET."FROM ".USER.RET;
        $and = "WHERE".RET;
 
        if($array['username'] != "") {
            $qry .= $and."username = '".$array['username']."'".RET;
            $and = "AND".RET;
        }
 
        if($array['password'] != "") {
            $qry .= $and."password = '".$array['password']."'".RET;
            $and = "AND".RET;
        }
 
        $recordset = Database::select($qry);
        $class_objects = array();
        if(is_array($recordset) == true) {
            while(list($i, $record) = each($recordset)) {
                $class_object = new user();
                $class_object->setusername($record['username']);
                $class_object->setpassword($record['password']);
                $class_objects[$class_object->getusername()] = $class_object;
            }
        }
        return $class_objects;
    }
 
    public function insert($update = FALSE) {
        if($update == TRUE) {
            $qry  = "UPDATE ".USER.RET."SET".RET.
            "username = '".$this->getusername()."',".RET.
            "password = '".$this->getpassword()."'".RET.
    .RET;
 
            Database::insert($qry);
        } else {
            $qry  = "INSERT INTO ".USER." (".RET.
            "username, password".RET.
            ") VALUES (".RET.
            "'".$this->getusername()."',".RET.
            "'".$this->getpassword()."'".RET.
            ")".RET;
 
            Database::insert($qry);
        }
    }
 
    public static function delete($array = array()) {
        $qry = "DELETE".RET."FROM ".USER.RET;
        $and = "WHERE".RET;
 
        if($array['username'] != "") {
            $qry .= $and."username = '".$array['username']."'".RET;
            $and = "AND".RET;
        }
 
        if($array['password'] != "") {
            $qry .= $and."password = '".$array['password']."'".RET;
            $and = "AND".RET;
        }
 
        Database::delete($qry);
    }
}
 
?>


2 thoughts on “Automatically create PHP classes from MySQL

  1. QCodo is a PHP framework that does excellent database code generation including relationships and type tables and join tables. All this is really tight Object Oriented code and the framework supports MySQL, PostgreSQL, and SQL Server. You should check it out if you are interested in code generation.
    http://www.qcodo.com

Comments are closed.