By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

PHP Dynamic Database Code

P: n/a
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/developerwork...xw01DynamicPHP
http://www.onlamp.com/pub/a/php/2005...erloading.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('j*********@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

Jun 9 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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-mys...ictionary.html

--
Tony Marston

http://www.tonymarston.net
http://www.radicore.org
<jo******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.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/developerwork...xw01DynamicPHP
http://www.onlamp.com/pub/a/php/2005...erloading.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('j*********@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

Jun 10 '06 #2

P: n/a
With phpPeanuts, after putting database access information in
scriptMakeSettings.php you only have to write a class like this:

includeClass('PntDbObject', 'pnt/db');
class Book extends PntDbObject {

function initPropertyDescriptors() {
parent::initPropertyDescriptors();
$this->addDbFieldProps();
}

function getTableName() {
return 'Book';
}

function getClassDir() {
return 'library';
}
}

Now you can do things like this:

include ("../classes/classSite.php");
$site = new Site('library');

// create a book entry, then insert it to the database
$book = new Book();
$book->set('author', 'John Smith');
$book->set('publisher', "O'Reilly");
$book->set('title', 'Greatest Book Ever Written');
$book->set('author_Email', 'j*********@oreilly.com');
$book->save();
$insert_id = $book->get('id');

// Or we can load a book, knowing its ID, and even make
// changes to it
$clsDes =& PntClassDescriptor::getInstance('Book');
$book =& $clsDes->getPeanutWithId('156932');
$author = $book->get('author');
$book->set('title', 'New Title');
$book->set('publisher', "New Publisher");
$book->save();

// Or search for all books written by John Smith
$clsDes =& PntClassDescriptor::getInstance('Book');
$books =& $clsDes->getPeanutsWith('author', 'John Smith');

So far it is all much alike, except that it will not only work in php5
but also in php4. Of course it is usually the user who enters this kind
of data. If we replace all this code by simply:

include ("../classes/classSite.php");
$site = new Site('library');
$site->handleRequest();

and put it in library/index.php we can have the user edit a new book at
the following url:
library/index.php?pntType=Book&id=0
This will generate a form the user can fill out. He can insert the new
book by pressing the 'Create New' button. The values he entered will
then be converted from strings to numbers, date etc according to the
type taken from the table columns using the locale settings made in
classStringConverter, validated by the Book object against the maximum
lenghts from the table columns and the book will be inserted.

editing an existing book by id can be done the same way at the following
url:
library/index.php?pntType=Book&id=156932

and searching for books at:
library/index.php?pntType=Book&pntHandler=SearchPage
As you can see at the advanced search form, the queries can be
reasonably complex.

But in the end the entire database design needs to be improved:
+--------------+ +--------+
| Book | | Author |
+--------------+ +--------+
| id | | id |
| authorId |--------| name |
| publisher | | email |
| title | +--------+
+--------------+

we need the following classes for this:
includeClass('PntDbObject', 'pnt/db');

class Book extends PntDbObject {

function initPropertyDescriptors() {
parent::initPropertyDescriptors();
$this->addDbFieldProps();
$this->addDerivedProp('author', 'Author', false);
}

function getTableName() {
return 'Book';
}

function getClassDir() {
return 'library';
}
}

class Author extends PntDbObject {

function initPropertyDescriptors() {
parent::initPropertyDescriptors();
$this->addDbFieldProps();
$this->addMultiValueProp('books', 'Book');
}

function getTableName() {
return 'Author';
}

function getClassDir() {
return 'library';
}
}

now we can have the user edit a new author at
library/index.php?pntType=Author&id=0
after he has inserted the new author he can once again edit a new book at:
library/index.php?pntType=Book&id=0
in the form that is generated the 'author' field will now hold a
dropdown from which the user van select an author. The 'author' label
will act as a hyperlink to the author. Once the new book has inserted it
will automatically show up in the list that is under the button 'Books'
of the author's edit-page. Or we can obtain this page by the following url:
library/index.php?pntType=Author&id=121&pntProperty=books& pntHandler=PropertyPage
(assuming the id of the author record is 121)

The code for making and modifying books and authors programatically will
look like this:

// create a new author entry, then insert it to the database
$author = new Author();
$author->set('name', 'John Smith');
$author->set('email', 'j*********@oreilly.com');
$author->save();

// create a book entry, then insert it to the database
$book = new Book();
$book->set('author', $author);
$book->set('publisher', "O'Reilly");
$book->set('title', 'Greatest Book Ever Written');
$book->save();
$insert_id = $book->get('id');

// Or we can load a book, knowing its ID, and make
// changes to it
$clsDes =& PntClassDescriptor::getInstance('Book');
$book =& $clsDes->getPeanutWithId('156932');
$book->set('publisher', "New Publisher");

//we can navigate to the author of the book and make changes to it
$author =& $book->get('author');
$author->set('email', "jo********@NewPublisher.com");
$author->save();

// Or search for John Smith and retrieve all books he has written
$clsDes =& PntClassDescriptor::getInstance('Author');
$authors =& $clsDes->getPeanutsWith('name', 'John Smith');
forEach(array_keys($authors) as $key {
$books =& $authors[$key]->get('books');
}

// phpPeanuts allows quite complex navigational queries using
// the Query model, for example search for all books
// written by a Smith, and published by O'Reilly
includeClass('PntSqlJoinFilter', 'pnt/db/query');
$filter1 =& PntSqlFilter::getInstance('Book, 'publisher');
$filter1->set('comparatorId', '=');
$filter1->set('value1', "O'Reilly");
$filter2 =& PntSqlFilter::getInstance('Book, 'author.name');
$filter2->set('comparatorId', 'LIKE');
$filter2->set('value1', "%Smith");
$combiFilter =& new PntSqlCombiFilter(); // does AND
$combiFilter->addPart($filter1);
$combiFilter->addPart($filter2);

$clsDes =& PntClassDescriptor::getInstance('Book');
$queryHandler =& $clsDes->getSelectQueryHandler();
$queryHandler->addSqlFromSpec($combiFilter); //generates SQL
$books = $clsDes->_getPeanutsRunQueryHandler($queryHandler);

I hope you don't mind i do not go into details about the code under the
hood, you can download it from www.phpPeanuts.org or browse it directly
at http://www.phppeanuts.org/site/index.../121/Code.html

Greetings,

Henk Verhoeven.

BTW, the above code has not been tested, it may contain typo's and bugs
Jun 14 '06 #3

P: n/a
Thanks for the detailed post. I will give phpPeanuts a look :-)

Jul 1 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.