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:
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('L ibrary_tables.p hp');
// create a book entry, then insert it to the database
$book = new Book();
$book->setAuthor('Joh n Smith');
$book->setPublisher(" O'Reilly");
$book->setTitle('Grea test Book Ever Written');
$book->setAuthor_Emai l('john.smith@o reilly.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('Ne w 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('C onfig.php'); // this is how my PHP script reads the
config file
require_once('D BObject.php');
class Book extends DBObject {
function __construct($id =0) {
parent::__const ruct(
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($db config, $table, $fields, $id=0) {
$err = "DBObject $table Constructor Error: ";
// Only use the $dbconfig if it is of the correct object type
if (get_class($dbc onfig) == '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($fie lds)) {
$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_exis ts($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(subs tr($method, 0, 3));
$property = strtolower(subs tr($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_esca pe_string($raw_ id);
$query = "SELECT * FROM " . $this->table . " WHERE id='$id'";
$results = mysql_query($qu ery);
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_arr ay($results, MYSQL_ASSOC);
$this->id = $result['id'];
foreach(array_k eys($result) as $key) {
$this->fields[$key] = $result[$key];
}
}
function insert() {
$fields = join(", ", array_keys($thi s->fields));
// Make a string out of the object's attributes, each escaped,
// single quoted, and separated by commas.
$vals = array();
foreach(array_k eys($this->fields) as $filed) {
$vals[] = "'" . mysql_real_esca pe_string($this->fields[$filed]) .
"'";
}
$values = implode(", ", $vals);
$query = "INSERT INTO " . $this->table . " ($fields) VALUES
($values)";
// Handle invalid insert query
$results = mysql_query($qu ery);
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($searche s) {
$err = "Search " . $this->table . " Error: ";
if (!is_array($sea rches)) {
$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_exi sts($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($qu ery);
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_arr ay($results, MYSQL_ASSOC)) {
$id = $result['id'];
$result_array[] = new $this->table($id);
}
return $result_array;
}
function update() {
$id = mysql_real_esca pe_string($this->id);
$sets = array();
foreach(array_k eys($this->fields) as $field) {
if ($field != 'id') {
$sets[] = $field . "='" .
mysql_real_esca pe_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($sq l);
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
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:
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('L ibrary_tables.p hp');
// create a book entry, then insert it to the database
$book = new Book();
$book->setAuthor('Joh n Smith');
$book->setPublisher(" O'Reilly");
$book->setTitle('Grea test Book Ever Written');
$book->setAuthor_Emai l('john.smith@o reilly.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('Ne w 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('C onfig.php'); // this is how my PHP script reads the
config file
require_once('D BObject.php');
class Book extends DBObject {
function __construct($id =0) {
parent::__const ruct(
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($db config, $table, $fields, $id=0) {
$err = "DBObject $table Constructor Error: ";
// Only use the $dbconfig if it is of the correct object type
if (get_class($dbc onfig) == '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($fie lds)) {
$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_exis ts($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(subs tr($method, 0, 3));
$property = strtolower(subs tr($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_esca pe_string($raw_ id);
$query = "SELECT * FROM " . $this->table . " WHERE id='$id'";
$results = mysql_query($qu ery);
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_arr ay($results, MYSQL_ASSOC);
$this->id = $result['id'];
foreach(array_k eys($result) as $key) {
$this->fields[$key] = $result[$key];
}
}
function insert() {
$fields = join(", ", array_keys($thi s->fields));
// Make a string out of the object's attributes, each escaped,
// single quoted, and separated by commas.
$vals = array();
foreach(array_k eys($this->fields) as $filed) {
$vals[] = "'" . mysql_real_esca pe_string($this->fields[$filed]) .
"'";
}
$values = implode(", ", $vals);
$query = "INSERT INTO " . $this->table . " ($fields) VALUES
($values)";
// Handle invalid insert query
$results = mysql_query($qu ery);
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($searche s) {
$err = "Search " . $this->table . " Error: ";
if (!is_array($sea rches)) {
$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_exi sts($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($qu ery);
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_arr ay($results, MYSQL_ASSOC)) {
$id = $result['id'];
$result_array[] = new $this->table($id);
}
return $result_array;
}
function update() {
$id = mysql_real_esca pe_string($this->id);
$sets = array();
foreach(array_k eys($this->fields) as $field) {
if ($field != 'id') {
$sets[] = $field . "='" .
mysql_real_esca pe_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($sq l);
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
Comment