Posted by Tony Marston on 06/10/06 08:06

This has already been done. Like you I have a separate class for each
database table, but I create all the PHP code from a data dictionary, not a
perl script. The data dictionary has an IMPORT function which reads the
database schema, and an EXPORT function which creates two files per table -
the class file and a structure file. If the class file already exists it
does not overwrite it as it may have been customised. If any table is
amended the IMPORT function will synchronise the dictionary with any
changes, and the EXPORT will overwrite the structure file only.

Each table class is actually an extension of an abstract class which
contains all the code which is common to every database table. This means
that I never have to write any sql as it is generated for me at runtime.

You can read about my data dictionary at
http://www.tonymarston.co.uk/php-mysql/data-dictionary.html

--
Tony Marston

http://www.tonymarston.net
http://www.radicore.org


<josh.kuo@gmail.com> wrote in message
news:1149894797.470117.321910@u72g2000cwu.googlegroups.com...
> Sorry about the subject, I can't think of a better one.
>
> I recently wrote some PHP classes that I think might be of interest to
> this group. Since I have been reaping the benefits of reading news
> groups for years, I figure it's time for me to contribute a little bit
> back, maybe some people out there will find this useful.
>
> * Introduction
>
> This is a "how-to" style article, showing by example how to dynamically
> generate PHP code based on database table structure. Most of the code
> and ideas were taken from the following two URLs, I just put them
> together with some tweaking of my own:
>
> http://www-128.ibm.com/developerworks/opensource/library/os-php-flexobj/?ca=dgr-lnxw01DynamicPHP
> http://www.onlamp.com/pub/a/php/2005/06/16/overloading.html
>
> Basically, I am tired of writing a new class for each table in my
> database, I want it done for me by a script or through some Object
> Oriented magic.
>
> * How it works
>
> Say you have a database 'Library', with a table 'Book' like this:
>
> +--------------+
> | Book |
> +--------------+
> | id |
> | author |
> | publisher |
> | title |
> | author_email |
> +--------------+
>
> And you want to end up with an object 'Book' with methods to manipulate
> each of the fields. I have written the following components:
> - A 'Config' file that contains the database access information (host
> name, database name, username, password, etc) that is both readable by
> perl and PHP
> - A perl script named 'gendb' that reads the config file, connects to
> the database, fetches the table structures, and creates a PHP object
> for each table (This sounds like a lot of work, but it is not)
> - A PHP object named 'DBObject.php' (taken mostly from IBM's URL
> posted above), that serve as the parent object to every table.
>
> After running my 'gendb' script, I will end up with a file named
> 'Library_tables.php'. We will go into more details later on how it
> works, here's my PHP code utilizing it:
>
> <?php
> require_once('Library_tables.php');
> // create a book entry, then insert it to the database
> $book = new Book();
> $book->setAuthor('John Smith');
> $book->setPublisher("O'Reilly");
> $book->setTitle('Greatest Book Ever Written');
> $book->setAuthor_Email('john.smith@oreilly.com');
> $insert_id = $book->insert();
>
> // Or we can load a book, knowing its ID, and even make
> // changes to it
> $book = new Book('156932');
> $author = $book->getAuthor();
> $book->setTitle('New Title');
> $book->{'publisher'} = "New Publisher"; // notice how we can access it
> differently
> $book->update();
>
> // We can also do searches, below shows how to search for
> // all the books made by John Smith
> $search = new Book();
> $books = $search->search( array('author'=>'John Smith'));
> foreach ($books as $book) {
> echo " BOOK: " . $book->getName() . "\n";
> }
>
> // Or search for all books written by John Smith, and published
> // by O'Reilly
> $search = new Book();
> $books = $search->search( array('author'=>'John Smith',
> 'publisher'=>"O'Reilly"));
> foreach ($books as $book) {
> echo " BOOK: " . $book->getName() . "\n";
> }
> ?>
>
>
> * The Code Under The Hood:
>
> First of all, let's look at the PHP code generated by the perl script:
>
> <?php
> require_once('Config.php'); // this is how my PHP script reads the
> config file
> require_once('DBObject.php');
>
> class Book extends DBObject {
> function __construct($id=0) {
> parent::__construct(
> new Config('Library'),
> 'Book',
> array('author', 'publisher', 'title', 'author_email'),
> $id
> );
> }
> }
> ?>
>
> And this is what DBObject.php looks like:
>
> <?php
> class DBObject {
> private $id = 0;
> private $table;
> private $fields = array();
> private $dbh; // database handler
> private $dbconfig;
>
> function __construct($dbconfig, $table, $fields, $id=0) {
> $err = "DBObject $table Constructor Error: ";
> // Only use the $dbconfig if it is of the correct object type
> if (get_class($dbconfig) == 'Config') {
> $this->dbconfig = $dbconfig;
> } else {
> $err .= "Did not provide valid 'Config' object at time of ".
> "initialization.";
> throw new Exception($err);
> }
> $this->table = $table;
>
> // Verify that $feilds is an array
> if (!is_array($fields)) {
> $err .= "Database table fields must be an array.";
> throw new Exception($err);
> }
> foreach($fields as $key) {
> $this->fields[$key] = null;
> }
>
> // Now attempt to initiate a connection to the database,
> // so we can set the database handler
> if (!$this->dbh = mysql_pconnect($this->dbconfig->host(),
> $this->dbconfig->user(),
> $this->dbconfig->pass())) {
> $err .= "Cannot connect to database server " .
> $this->dbconfig->host() . ", " . mysql_error();
> throw new Exception($err);
> }
>
> // Select the database we want to use
> if (!mysql_select_db($this->dbconfig->db(), $this->dbh)) {
> $err .= "Cannot select database " . $this->dbconfig->db() .
> ", " . mysql_error();
> throw new Exception($err);
> }
>
> // load the object attributes if an ID is specified
> if ($id) {
> $this->load($id);
> }
> }
>
> // Get
> function __get($key) {
> return $this->fields[$key];
> }
>
> // Set
> function __set($key, $value) {
> if (array_key_exists($key, $this->fields)) {
> $this->fields[$key] = $value; return true;
> }
> return false;
> }
>
> // Dyanmic method overload, this gives us the getXXX() and
> // setXXX() methods on the fly.
> function __call($method, $arguments) {
> $prefix = strtolower(substr($method, 0, 3));
> $property = strtolower(substr($method, 3));
> if (empty($prefix) || empty($property)) {
> return;
> }
> if ($prefix == "get" && isset($this->fields[$property])) {
> return $this->fields[$property];
> }
> if ($prefix == "set") {
> $this->$property = $arguments[0];
> }
> }
>
> function load($raw_id) {
> $id = mysql_real_escape_string($raw_id);
> $query = "SELECT * FROM " . $this->table . " WHERE id='$id'";
> $results = mysql_query($query);
> if (!$results) {
> $err = "Invalid load query: " . mysql_error();
> throw new Exception($err);
> }
>
> // check the number of rows returned, we should get exactly one
> $num_of_rows = mysql_num_rows($results);
> if ($num_of_rows < 1) {
> $err = "Database returned no results for ID[$id].";
> throw new Exception($err);
> } elseif ($num_of_rows > 1) {
> $err = "Database returned more than 1 results for ID[$id].";
> throw new Exception($err);
> }
>
> // if we made it here, we only have one set of results
> $result = mysql_fetch_array($results, MYSQL_ASSOC);
> $this->id = $result['id'];
> foreach(array_keys($result) as $key) {
> $this->fields[$key] = $result[$key];
> }
> }
>
> function insert() {
> $fields = join(", ", array_keys($this->fields));
>
> // Make a string out of the object's attributes, each escaped,
> // single quoted, and separated by commas.
> $vals = array();
> foreach(array_keys($this->fields) as $filed) {
> $vals[] = "'" . mysql_real_escape_string($this->fields[$filed]) .
> "'";
> }
> $values = implode(", ", $vals);
> $query = "INSERT INTO " . $this->table . " ($fields) VALUES
> ($values)";
>
> // Handle invalid insert query
> $results = mysql_query($query);
> if (!$results) {
> $err = "Invalid insert query: " . mysql_error();
> throw new Exception($err);
> }
>
> // Get the insert ID and set the object attribute, as well as
> // returning it
> $insert_id = mysql_insert_id();
> if ($insert_id) {
> $this->id = $insert_id;
> return $insert_id;
> }
> $err = "Insert failed.";
> throw new Exception($err);
> }
>
> function search($searches) {
> $err = "Search " . $this->table . " Error: ";
> if (!is_array($searches)) {
> $err .= "search must be an array.";
> throw new Exception($err);
> }
>
> // check to make sure that all fields specified in the search
> // matches what the object has
> $search_query = array();
> foreach ($searches as $key=>$val) {
> if (!array_key_exists($key, $this->fields)) {
> $err .= "$key is not a valid field for " . $this->table;
> throw new Exception($err);
> }
> $search_query[] = $key . "='" . $val ."'";
> }
> $matches = implode(" AND ", $search_query);
> $query = "SELECT id FROM " . $this->table . " WHERE $matches";
>
> $results = mysql_query($query);
> if (!$results) {
> $err .= "Invalid SQL query";
> throw new Exception($err);
> }
>
> // check the number of rows returned, if none, we can quit now,
> just
> // spit back an empty array
> $num_of_rows = mysql_num_rows($results);
> if ($num_of_rows < 1) {
> return array();
> }
>
> // if we made it here, we have at least one set of results
> $result_array = array();
> while ($result = mysql_fetch_array($results, MYSQL_ASSOC)) {
> $id = $result['id'];
> $result_array[] = new $this->table($id);
> }
> return $result_array;
> }
>
> function update() {
> $id = mysql_real_escape_string($this->id);
>
> $sets = array();
> foreach(array_keys($this->fields) as $field) {
> if ($field != 'id') {
> $sets[] = $field . "='" .
> mysql_real_escape_string($this->fields[$field]) .
> "'";
> }
> }
> $set = join(", ", $sets);
> $sql = "UPDATE " . $this->table . " SET " . $set .
> " WHERE id='$id'";
>
> echo "<pre><font color=blue>SQL = [$sql]</font></pre>";
> $results = mysql_query($sql);
> if (!$results) {
> $err = "Invalid update query: " . mysql_error();
> throw new Exception($err);
> }
> }
> }
> ?>
>
> I am not going to discuss Config.php and the perl script, as they are
> beyond the scope of this post.
>
>
>
> Basically, this enables me to make changes to my database schema, and
> then run my perl script:
>
> $ ./gendb Library
>
> This spits out a fresh copy of Library_tables.php that is based on the
> new database structure, and then I have instant OO access to the tables
> in the databas.e
>
> I also found this to be very helpful when dealing with an older
> database that I did not create, all I have to do is create the database
> configuration, run my perl script:
>
> $ ./gendb "Old Database"
>
> And then I can concentrate my efforts and time on writing the actual
> program, not having to worry too much about SQL.
>
>
> * Drawbacks and Limitations:
>
> There are a few drawbacks:
> 1. Database connection overhead: Each time you create a new object,
> a database connection is initiated. While we are using mysql_pconnect()
> for connection pooling, you need to keep this in mind when you are
> writing your code. For example, when you perform a search that returned
> 10 results, under the hood, 11 mysql_pconnect() calls were made, one
> for the search, and another one for each of the search results.
> 2. Use more resources: When you create a database table object,
> every field of the object is loaded. So if your 'Book' table has 15
> columns, every time you create an 'Book' object, it will load all 15 of
> them via SELECT * FROM Book WHERE id=XXX. This can use up more
> resources on larger tables, especially those with BLOB column types.
> 3. Less flexible SQL query: This one is pretty obvious, you lose the
> flexibility of running more complicated SQL queries, but that's kind of
> the whole point, so programmers do not have to worry about SQL queries,
> and just work with objects.
>
>
> I hope this helps. Any criticism and suggestion welcome, please email
> me directlyr:
>
> josh dot kuo at gmail dot com
>

[Back to original message]