473,288 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

PHP Dynamic Database Code

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
3 2903
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
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
Thanks for the detailed post. I will give phpPeanuts a look :-)

Jul 1 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Guinness Mann | last post by:
When you guys talk about "dynamic SQL," to what exactly are you referring? Is dynamic SQL anything that isn't a stored procedure? Specifically, I use ASP.NET to communicate with my SQL Server...
4
by: jrefactors | last post by:
I want to distinguish between static SQL, dynamic SQL, and embedded SQL, but couldn't find too much useful resources in the web. For example, if we put SQL statements (SELECT, INSERT, UPDATE,...
3
by: JDPope | last post by:
I have a situation which I cannot get a good lead on how to resolve. One of the applications I support uses the Hibernate software to generate SQL. The app is JAVA with JDBC. In testing the users...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
8
by: George Meng | last post by:
I got a tough question: The backgroud for this question is: I want to design an application works like a engine. After release, we can still customize a form by adding a button, and source code...
2
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic...
5
by: alingsjtu | last post by:
Hello, every body. When execute dynamic generated multiple OPENQUERY statements (which linkes to DB2) in SQLServer, I always got SQL1040N The maximum number of applications is already connected...
10
by: jflash | last post by:
Hello all, I feel dumb having to ask this question in the first place, but I just can not figure it out. I am wanting to set my site up using dynamic urls (I'm assuming that's what they're...
5
by: pittendrigh | last post by:
There must be millions of dynamically generated html pages out there now, built by on-the-fly php code (and jsp, perl cgi, asp, etc). Programatic page generation is transparently useful. But...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.