SPDO-PHP

From EvWiki

Jump to: navigation, search

« Back to SPDO

···

Go to SPDO-PY»

SPDO PHP

Requirements

To use spdo-php your PHP must have been compiled with the following:

with PostgreSQL: postgres
with MySQL: mysql (this does not use mysqli)
with sqlite: sqlite

Setup

Set up of SPDO is quite simple. Copy the files in the spdo-php folder to your project. Edit the config.example.php file and save it as config.php. You are ready to go!

# PostgreSQL example
config = {
    'dbtype'=>'postgres',
    'dbname'=>'test',
    'dbhost'=>'localhost',
    'dbport'=>5432,
    'username'=>'myuserame',
    'password'=>'mypassword',
    'uuid_built_in'=>1
    }
# MySQL example
config = {
    'dbtype'=>'mysql',
    'dbname'=>'test',
    'dbhost'=>'localhost',
    'dbport'=>None,
    'username'=>'myuserame',
    'password'=>'mypassword',
    'uuid_built_in'=>0
    }
 
# sqlite example
config = {
    'dbtype'=>'sqlite',
    'dbname'=>'test.sqlite',
    'dbhost'=>None,
    'dbport'=>None,
    'username'=>None,
    'password'=>None,
    'uuid_built_in'=>0
    }

Usage

Examples:

Simplest usage example:

<?php
require_once('spdo.php');
$db = new SPDO('mydatabase');
// add a row to the test table
$newid = $db->insert('INSERT INTO test (name, value) VALUES (?,?)',array('foo','bar'));
// let's see the new id we generated
print $newid
// update the row
$numupdated = $db->update('UPDATE test SET name=?, value=? WHERE id=?',array('spam','eggs',$newid));
// let's see how many rows were updated
print $numupdated
// select everything
$rows = $db->select('SELECT * FROM test');
foreach($rows as $row)
{
    foreach($row as $field=>$value)
    {
        print "\t$field \t{$row[$field]}";
    }
}
// delete our spam -> eggs row
$numdeleted = $db->delete('DELETE FROM test WHERE id=?',array($newid));
// let's see how many rows were deleted
print $numdeleted
?>

Source Files

config.example.php

<?php
/**
 * Example configuration file for SPDO-PHP
 * contains default connection values
 * Copy this file to config.php and adjust as needed for your environment
 * @version 0.9
 * @author Sjan Evardsson
 * @info: http://www.evardsson.com/wiki/SPDO
 */
 
/**
 * CONFIG array holds configuration data
 * This is a sample PostgreSQL Config
 */
$CONFIG = array(
    'dbtype'=>'postgres',
    'dbname'=>'test',
    'dbuser'=>'user',
    'dbpass'=>'pass',
    'dbhost'=>'localhost',
    'dbport'=>5432
);
 
/**
 * CONFIG array holds configuration data
 * This is a sample MySQL Config
 */
/*
$CONFIG = array(
    'dbtype'=>'mysql',
    'dbname'=>'test',
    'dbuser'=>'user',
    'dbpass'=>'pass',
    'dbhost'=>'localhost',
    'dbport'=>NULL
);
*/
/**
 * CONFIG array holds configuration data
 * This is a sample sqlite Config
 */
/*
$CONFIG = array(
    'dbtype'=>'sqlite',
    'dbname'=>'test',
    'dbuser'=>NULL,
    'dbpass'=>NULL,
    'dbhost'=>NULL,
    'dbport'=>NULL
);
*/
?>

spdo.php

<?php
/*
FILE:
spdo.php (http://www.evardsson.com/files/spdo-php.tar.bz2)
 
LICENSE:
The MIT License
 
Copyright (c) 2009 Sjan Evardsson
 
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
 
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
 
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
*/
require_once('config.php');
/**
 * SPDO class provides a quick class
 * for databse connection/querying.
 * Based on settings in config.php this will use PostgreSQL
 * MySQL or sqlite.
 *
 * USAGE:
 *
 *  require_once('spdo.php')
 *  $db = new SPDO('mydatabase');
 *  $new_id = $db->insert("INSERT INTO mytable (name, email) VALUES (?, ?)",array('Sjan Evardsson','sjan@evardsson.com'));
 *  // The following looks scary, but all string values are escaped with either pg_escape_string, mysql_real_escape_string or sqlite_escape_string
 *  $num_updated_rows = $db->update("UPDATE mytable SET description = ? WHERE id = ?",array("Say what? Say';DROP TABLE mytable;",$new_id));
 *  $num_deleted_rows = $db->delete("DELETE FROM mytable WHERE id = ?", array($new_id));
 *  $myarray = $db->select("SELECT * FROM mytable WHERE id < ?",array($id));
 *  foreach($myarray as $row)
 *  {
 *      print_r($row);
 *  }
 *
 * Notes on Date Values (DATE, DATETIME, TIME, TIMESTAMP)
 * When using dates, datetimes, timestamps, etc your choices are:
 *  1. Use the PHP DateTime object to pass all dates, times, datetimes, etc. THIS IS THE PREFERRED METHOD!
 *  2. Pass them as formatted strings (which will be run through a xx_escape_string() and passed as strings). This will
 *          work fine for MySQL and sqlite but for PostgreSQL you will need to do an explicit cast in the query.
 *  3. Declare your field types as INTEGER (for timestamps) or a VARCHAR for date, datetime, time, etc and do all your own
 *          date parsing/adding/arithmetic. Speaking from painful experience, this is not suggested! (Unfortunately, if you insist on
 *          using sqlite you are stuck with this. Objects passed as DateTime will be parsed like 2009-01-28 15:11:47-08:00)
 * @version 0.9
 * @author Sjan Evardsson
 * @info: http://www.evardsson.com/wiki/SPDO
 */
 
switch ($CONFIG['dbtype']){
    case 'postgres':
        require_once('db_pg.php');
        break;
    case 'mysql':
        require_once('db_my.php');
        break;
    case 'sqlite':
        require_once('db_sl.php');
        break;
}
 
/**
 * Generate a UUID (GUID)
 * @return string UUID
 */
function uuid()
{
    return sprintf( '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
        mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),
        mt_rand( 0, 0x0fff ) | 0x4000,
        mt_rand( 0, 0x3fff ) | 0x8000,
        mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ) );
}
/* constants definitions */
define('NON_ASSOC',0,true);
define('ASSOC',1,true);
define('MULTI_ASSOC',2,true);
define('SERIAL',3,true);
define('GUID',4,true);
 
 
?>

db_pg.php

<?php
/*
FILE:
db_pg.php (http://www.evardsson.com/files/spdo-php.tar.bz2)
 
LICENSE:
The MIT License
 
Copyright (c) 2009 Sjan Evardsson
 
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
 
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
 
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
*/
require_once('config.php');
require_once('spdoerror.php');
/**
 * SPDO class provides a quick class
 * for databse connection/querying.
 * This class makes use of PostgreSQL Prepared Statements. However, it does not rely on PHP's pg_prepare() for the following reasons:
 *      1. pg_prepare() requires that you enumerate the values in the query ("SELECT * FROM foo WHERE bar = $1 or bar = $2"). This is minor but that's my beef.
 *      2. pg_prepare() has difficulties with data types in functions like date_trunc('day', $1) [http://www.php.net/manual/en/function.pg-prepare.php]
 *      3. pg_prepare() does not create an object that can be held on to, requiring you to keep track on your own of statement names and signatures.
 *      4. I wanted to duplicate the functionality of my Python DBO in PHP. So I did.
 * @version 0.9
 * @author Sjan Evardsson
 * @info: http://www.evardsson.com/wiki/SPDO
 */
 
// prepared statement field types
define('STR','text');
define('INT','int');
define('FLOAT','numeric');
define('BOOL','bool');
define('DATETIME','timestamp with time zone');
define('LONG','bigint');
 
class SPDO
{
 
    /* db name */
    protected $dbname;
    /* user name */
    protected $dbuser;
    /* password */
    protected $dbpass;
    /* db host */
    protected $dbhost;
    /* db port */
    protected $dbport;
    /* connection: each instance holds its own */
    protected $connection;
    /* prepared statments */
    protected $statements;
 
    /**
     * Constructor: creates a new DBObject
     * @param dbname string database name
     * @param dbuser string database username
     * @param dbpass string database password
     * @param dbhost string database host
     * @param dbport int database port
     * Parameters can be omitted, right to left for default values
     * which are set in config.php
     */
    public function __construct($dbname=null, $dbuser=null, $dbpass=null, $dbhost=null, $dbport=null)
    {
        global $CONFIG;
        $this->dbname = (is_null($dbname))?$CONFIG['dbname']:$dbname;
        $this->dbuser = (is_null($dbuser))?$CONFIG['dbuser']:$dbuser;
        $this->dbpass = (is_null($dbpass))?$CONFIG['dbpass']:$dbpass;
        $this->dbhost = (is_null($dbhost))?$CONFIG['dbhost']:$dbhost;
        $this->dbport = (is_null($dbport))?$CONFIG['dbport']:$dbport;
        $this->connection = $this->connect();
        if (!$this->connection) { //
            throw new ConnectionFailedError("Unable to connect to host={$this->dbhost} port={$this->dbport} dbname={$this->dbname} user={$this->dbuser} password={$this->dbpass}");
        }
        $this->statements = array();
    }
 
    /**
     * Destructor closes the connection this instance holds
     */
    public function __destruct()
    {
        // first let's clean up all our prepared statements
        @pg_query("DEALLOCATE ALL");
        // now close the connection
        @pg_close($this->connection);
    }
 
    /**
     * Connect returns a connection object based on the values
     * contained in this object.
     * @return connection object or false on failure
     */
    public function connect()
    {
        if (!$this->connection) {
            $this->connection = pg_connect("host={$this->dbhost} port={$this->dbport} dbname={$this->dbname} user={$this->dbuser} password={$this->dbpass}");
        }
        return $this->connection;
    }
 
    /**
     * Retrieve data from the database (SELECT)
     * This function ALWAYS returns an array, even if there is no data, meaning it is safe for immediately calling foreach() on the results
     * @param $query string SQL query "SELECT * FROM foo WHERE foodate > ? or bar = ?"
     * @param data array of data to fill in the blanks (?) array(new DateTime('now'), 'zob') DEFAULT NULL
     * @param $key string field name to use as associative array key for each row
     * @param $type NON_ASSOC (default, use no field as key) ASSOC (use the value of a field as a key for each row) 
     *      MULTI_ASSOC (use the value of a field as a key for a non-associative array of rows which all have the same field value)
     * @return array of rows formatted as in one of the examples below:
     * Example 1: non-associative (default):
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC", array(new DateTime('2008-12-31'), 'zob'));
     *      returns: array (
     *          0=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30'),
     *          1=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30'),
     *          2=>array('id'=>'3','bar'=>'zob','foodate'=>'2008-12-31')
     *          3=>array('id'=>'4','bar'=>'zob','foodate'=>'2009-01-01')
     *      )
     * Example 2: associative array: (notice that last value returned overwrites the row if more than one row with the same key value exist)
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC", array(new DateTime('2008-12-31'), 'zob'),"bar");
     *      returns: array (
     *          'baz=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30'),
     *          'bat'=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30'),
     *          'zob'=>array('id'=>'4','bar'=>'zob','foodate'=>'2009-01-01')
     *      )
     * Example 2: multi-associative array:
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC",
     *              array(new DateTime('2008-12-31'), 'zob'),"bar",MULTI_ASSOC);
     *      returns: array (
     *          'baz'=>array(
     *              0=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30')
     *          ),
     *          'bat'=>array(
     *              0=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30')
     *          ),
     *          'zob'=>array(
     *              0=>array('id'=>'3','bar'=>'zob','foodate'=>'2008-12-31')
     *              1=>array('id'=>'3','bar'=>'zob','foodate'=>'2009-01-01')
     *          )
     *      )
     */
    public function select($sql, $data=NULL, $key=NULL, $type=NON_ASSOC)
    {
        $result = array();
        if (is_null($data))
            $data = array(1);
        $types = $this->buildtypes($data);
        $ps = $this->prepare_statement($sql, $types);
        // fix up type for ASSOC:
        if (!is_null($key) && $type == NON_ASSOC)
            $type = ASSOC;
        $res = $ps->execute($data);
        if ($res) while ($row = pg_fetch_assoc($res))
        {
            switch($type)
            {
                case ASSOC:
                    $result[$row[$key]] = $row;
                    break;
                case MULTI_ASSOC:
                    $result[$row[$key]][] = $row;
                    break;
                case NON_ASSOC:
                default:
                    $result[] = $row;
                    break;
            }
        }
        pg_free_result($res);
        return $result;
    }
 
    /**
     * Insert a new row in the database (INSERT)
     * This function assumes the following:
     *      If using a UUID and the database does not have a uuid() function installed the INSERT statement MUST be written using id with '?UUID?' 
     *          for the data in that position
   *        Example:
   *            $newid = $db->insert("INSERT INTO foo (id, field1) VALUES (?, ?)", array('?UUID?','bar'));
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @param $id_field string id field name
     * @return mixed row id
     */
    public function insert($sql, $data, $id_field='id')
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to insert without data!");
        }
        $result = 0;
        $data = $this->fixuuid($data);
        if (strpos($sql, 'RETURNING') === false) {
            $sql .= " RETURNING $id_field";
        }
        $ps = $this->prepare_statement($sql, $data);
        $res = $ps->execute($data);
        if ($res) {
            $row = pg_fetch_assoc($res);
            $result = $row[$id_field];
        } else {
			$ermsg = pg_last_error();
			if (strpos($ermsg, 'violates foreign key constraint') !== false) {
				throw new ForeignKeyViolationError($ermsg);
			} else {
				throw new SPDOError($ermsg);
			}
		}
        return $result;
    }
 
    /**
     * Update a row in the database (UPDATE)
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return int number of rows affected
     */
    public function update($sql, $data)
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to update without data!");
        }
        $result = 0;
        $data = $this->fixuuid($data);
        $ps = $this->prepare_statement($sql, $data);
        if ($res) {
			$result = pg_affected_rows($res);
		} else {
			$ermsg = pg_last_error();
			if (strpos($ermsg, 'violates foreign key constraint') !== false) {
				throw new ForeignKeyViolationError($ermsg);
			} else {
				throw new SPDOError($ermsg);
			}
		}
        return $result;
    }
 
    /**
     * Delete a row in the database (DELETE)
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return int number of rows affected
     */
    public function delete($sql, $data)
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to delete without data! Doing so would empty the entire table!");
        }
        return $this->update($sql, $data);
    }
 
    /**
     * Perform a raw query. This is NOT created as a PreparedStatement, so has no setup overhead involved,
     * however, subsequent calls to the same query string will take longer than subsequent calls using a PreparedStatement
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return ResultSet
     */
    public function query($sql, $data=NULL)
    {
        if (!is_null($data)) {
            $q = '';
            $parts = explode('?', $sql);
            for ($i = 0; $i < count($data); $i++)
            {
                $q .= $parts[$i];
                $q .= $this->smartquote($data[$i]);
            }
            for ($i = count($data); $i < count($parts); $i++)
            {
                $q .= $parts[$i];
            }
            $sql = $q;
        }
        return pg_query($this->connection, $sql);
    }
 
    /**
     * Perform a raw query. This IS created as a PreparedStatement, so has some setup overhead involved,
     * however, subsequent calls to the same query string will take less time than subsequent calls NOT using a PreparedStatement
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return ResultSet
     */
    public function ps_query($sql, $data=NULL)
    {
        if (is_null($data))
            $data = array(1);
        $data = $this->fixuuid($data);
        $ps = $this->prepare_statement($sql, $data);
        return $ps->execute($data);
    }
 
    /**
     * Return a PreparedStatement object: if this object is already in the statements dict return that, otherwise create a new one
     * and place it in the statements dict. The key for prepared statements in the dict is the SQL, to lower case with all whitespaces removed.
     * Therefore the following are all equivalent:
     *      "INSERT INTO mytable (foo, bar, foodate) VALUES (?,?,?)"
     *      "insert into
     *              mytable
     *                  (foo, bar, foodate)
     *              values
     *                  (?,?,?)"
     *      "Insert Into mytable ( foo , bar , foodate ) Values ( ? , ? , ?)"
     * @param sql string SQL query
     * @param data array of data
     * @return PreparedStatement object
     */
    public function prepare_statement($sql, $data)
    {
        $types = $this->buildtypes($data);
        $pname = strtolower(preg_replace('/\s+/','',$sql));
        if (!isset($this->statments[$pname])) {
            $ps = new PreparedStatement($this->connection, $sql, $types);
            $this->statements[$pname] = $ps;
        }
        return $this->statements[$pname];
    }
 
    /**
     * Private convenience function to properly build typelist for prepare_statement based on data list passed
     * @param data array of data to build types for
     * @return array of types
     */
    private function buildtypes($data)
    {
        $dtstr = 'DateTime';
        $types = array();
        foreach($data as $d)
        {
            if (is_array($d)) {
                $tmp = $this->buildtypes($d);
                $types[] = $tmp[0].'[]';
            } else if (is_int($d) || is_long($d)) {
                $types[] = LONG;
            } else if (is_float($d)) {
                $types[] = FLOAT;
            } else if (is_bool($d)) {
                $types[] = BOOL;
            } else if ($d instanceof $dtstr) {
                $types[] = DATETIME;
            } else {
                $types[] = STR;
            }
        }
        return $types;
    }
 
    /**
     * Private convenience function to replace '?UUID?' with an actual UUID in the data list.
     * @param data array of data
     * @return array of data with all occurences of '?UUID?' replaced with an actual UUID
     */
    private function fixuuid($data)
    {
        $retdata = array();
        foreach($data as $d)
        {
            if ($d == '?UUID?') {
                $retdata[] = $uuid();
            } else if (is_array($d)) {
                $di = array();
                foreach($d as $dx)
                {
                    if ($dx == '?UUID?') {
                        $di[] = $uuid();
                    } else {
                        $di[] = $dx;
                    }
                }
                $retdata[] = $di;
            } else { 
                $retdata[] = $d;
            }
        }
        return $retdata;
    }
 
    /**
     * Private convenience function to replace '?UUID?' with an actual UUID and safely escape strings
     * @param datum mixed item to be escaped & quoted
     * @return mixed escaped datum
     */
    private function smartquote($datum)
    {
        $dtstr = 'DateTime';
        $retdata = $datum;
        if ($datum == '?UUID?') {
            $retdata = "'".uuid()."'";
        } else if (is_array($datum)) {
            $di = array();
            foreach($datum as $dx)
            {
                $di[] = $this->smartquote($dx);
            }
            $retdata = $di;
        } else if ($datum instanceof $dtstr) {
            $retdata = "'".$datum->format('Y-m-d H:i:sP')."'";
        } else if (is_string($datum)) {
            $retdata = "'".pg_escape_string($datum)."'";
        }
        return $retdata;
    }
 
    /**
     * Test functionality
     * Requires a table named test. See the create_pgsql_testtable.sql file.        
     */
    public function test()
    {
        print "<pre>\n";
        var_dump($this);
        print "<br />\n";
        print "<br />\nTesting insert(\"INSERT INTO test (name, value) VALUES (?,?)\",array('testrun','This is only a test'))";
        $x = $this->insert("INSERT INTO test (name, value) VALUES (?,?)",array('testrun','This is only a test'));
        print "<br />\nLast inserted id: $x";
        print "<br />\nTesting for(\$i = 0; \$i < 10; \$i++) { insert(\"INSERT INTO test (name, value) VALUES (?,?)\",array('testrun'.\$i,'This is only a test')); }";
        $loopids = array();
        for($i = 0; $i < 10; $i++) { 
            $lid = $this->insert("INSERT INTO test (name, value) VALUES (?,?)",array('testrun'.$i,'This is only a test')); 
            $loopids[] = $lid;
            print "<br />\nLast inserted id: $lid";
        }
        print "<br />\nAll inserted ids from loop (\$loopids):";
        print_r($loopids);
        print "<br />\n<br />\nTesting update(\"UPDATE test SET value=? WHERE id=?\",array('This is still only a test','$x'))";
        $y = $this->update("UPDATE test SET value=? WHERE id=?",array('This is still only a test',$x));
        print "<br />\nUpdated $y rows";
        print "<br />\n<br />\nTesting select(\"SELECT * FROM test\")";
        $z = $this->select("SELECT * FROM test");
        foreach($z as $i)
        {
            foreach($i as $key=>$val)
            {
                print "<br />\n\t$key\t$val";
            }
        }
        print "<br />\n<br />\nTesting delete(\"DELETE FROM test WHERE id=?\",array('$x'))";
        $l = $this->delete("DELETE FROM test WHERE id=?",array($x));
        print "<br />\nDeleted $l rows";
        print "<br />\nTesting for(\$i = 0; \$i < 10; \$i++) { delete(\"DELETE FROM test where id=?\",array(\$loopids[\$i])); }";
        $lc = 0;
        for($i = 0; $i < 10; $i++) { 
            $lc += $this->delete("DELETE FROM test WHERE id=?",array($loopids[$i])); 
        }
        print "<br />\nDeleted $lc rows";
        print "<br />\nDone";
        var_dump($this);
    }
 
}
 
/**
 * PreparedStatement class holds a prepared statement and provides a direct method for executing it.
 */
 
class PreparedStatement
{
    /* the db connection */
    protected $connection;
    /* the query passed to PREPARE */
    protected $sql;
    /* an array of field types passed to PREPARE */
    protected $types;
    /* a key that is guaranteed to belong to only one prepared statement on a given connection */
    protected $skey;
    /* the name of this prepared statement: the sql to lower case minus all white space */
    protected $name;
 
    /**
     * Create a new PreparedStatement object.
     * This also runs the PREPARE query on the server so that this statement is ready to use.
     * @param connection database connection object
     * @param sql string SQL query to prepare
     * @param types array of types (see prepared statement field types)
     * @return PreparedStatement object
     */
    public function __construct($connection, $sql, $types)
    {
        $this->name = strtolower(preg_replace('/\s+/','',$sql));
        $this->skey = 'ps'.md5($this->name);
        $this->connection = $connection;
        $this->types = $types;
        $oldparts = explode('?',$sql);
        $sql = $oldparts[0];
        unset($oldparts[0]);
        $count = 1;
        foreach ($oldparts as $p)
        {
            $sql .= '$'.$count.$p;
            $count++;
        }
        $this->sql = $sql;
        $query = "PREPARE {$this->skey} (";
        $comma = '';
        foreach ($this->types as $t)
        {
            $query .= "$comma $t";
            $comma = ',';
        }
        $query .= ") AS {$this->sql}";
        $this->statement = $query;
        pg_query($this->connection, $this->statement);
    }
 
    /**
     * Execute this prepared statement.
     * @param args array of values to be set to the EXECUTE statement
     * @return resource object or boolean (depending on query type)
     */
    public function execute($args)
    {
        $query = "EXECUTE {$this->skey} (";
        $comma = '';
        for ($i = 0; $i < count($args); $i++)
        {
            $query .= $comma;
            if ($this->types[$i] == STR) {
                $query .= "'".pg_escape_string($args[$i])."'";
            } else {
                $query .= $args[$i];
            }
            $comma = ',';
        }
        $query .= ")";
        return pg_query($this->connection, $query);
    }
}
 
/**
 * DbBuilder provides a way to create all the tables and foreign key triggers for a database.
 * Usage:
 * $structure = array(
 * 		'authors'=>array(
 * 			'id'=>array('primary_auto'),
 * 			'first_name'=>array('varchar(50)'),
 * 			'last_name'=>array('varchar(50)', 'NONULL', 'INDEX,)
 * 		),
 * 		'books'=>array(
 * 	   		'id'=>array('primary_auto'),
 * 			'author_id'=>array('int', "FK_CASCADE('authors','id')", 'INDEX'),
 * 			'title'=>array('varchar(100)')
 * 		)
 * );
 * $dbb = DbBuilder($structure)
 * $str = $dbb.inspect() // if you want to see the create sql
 * print $str;
 * $dbb.create()
 * 
 * The query used for this structure would be:
 * CREATE TABLE authors (
 *      id SERIAL PRIMARY KEY,
 *      first_name VARCHAR(50),
 *      last_name VARCHAR(50) NOT NULL
 *  );
 *  CREATE TABLE books (
 *      id SERIAL PRIMARY KEY,
 *      author_id INTEGER NOT NULL,
 *      title VARCHAR(100)
 *  );
 *  CREATE INDEX books_author_id_idx ON books(author_id);
 *  ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE;
 *  If FK('authors','id) had been used in the books table then the following trigger would have been generated:
 *  ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE RESTRICT;
 */
class DbBuilder {
    protected $tables = array();
    protected $indices = array();
    protected $fks = array();
    protected $_boolean = 'BOOL';
    protected $_bool = 'BOOL';
    protected $_text = 'TEXT';
    protected $_tinyint = 'SMALLINT';
    protected $_int1 = 'SMALLINT';
    protected $_smallint = 'SMALLINT';
    protected $_int2 = 'SMALLINT';
    protected $_mediumint = 'INTEGER';
    protected $_int3 = 'INTEGER';
    protected $_integer = 'INTEGER';
    protected $_int4 = 'INTEGER';
    protected $_int = 'INTEGER';
    protected $_bigint = 'BIGINT';
    protected $_int8 = 'BIGINT';
    protected $_real = 'REAL';
    protected $_float4 = 'REAL';
    protected $_double = 'FLOAT';
    protected $_float8 = 'FLOAT';
    protected $_float = 'FLOAT';
    protected $_date = 'DATE';
    protected $_time = 'TIME';
    protected $_time_with_time_zone = 'TIME WITH TIMEZONE';
    protected $_datetime = 'DATETIME';
    protected $_timestamp = 'DATETIME';
    protected $_uuid = 'CHAR(36)';
    protected $_primary_auto = 'SERIAL PRIMARY KEY';
    protected $_serial = 'SERIAL PRIMARY KEY';
    protected $_auto_increment = 'SERIAL PRIMARY KEY';
    protected $_primary = 'PRIMARY KEY';
    protected $_nonull = 'NOT NULL';
    protected $_unique = 'UNIQUE';
    protected $_nonull_unique = 'NOT NULL UNIQUE';
 
    /**
     *  Create a new DbBuilder object. The structure is a dict of tables/fields laid out as:
     *  a dict {table_name:{field_name:[type,constraint(optional),index(optional)],field_name2[type,constraint(optional),index(optional)]}
     *   Types and constraints are case insensitive.
     *   
     *   If constraint is a Foreign Key it must be written as "fk('parent_table_name','parent_table_column')" or
     *   "fk_cascade('parent_table_name','parent_table_column')" (note that the parent table and column names must be
     *   in quotes). FK creates a Foreign Key with ON DELETE RESTRICT while FK_CASCADE creates a Foreign Key with ON DELETE CASCADE.
     *
     *   Note that for types primary_auto, serial, auto_increment constraints and index are ignored, while for other columns declared as
     *   PRIMARY index is ignored.
     *   
     *   
     *   Example with auto generated integer ids:
     *       structure = {
     *           'authors':{
     *               'id':['primary_auto'],
     *               'first_name':['varchar(50)'],
     *               'last_name':['varchar(50)', 'nonull', 'index']
     *           },
     *           'books':{
     *               'id':['primary_auto'],
     *               'author_id':['int', "fk_cascade('authors','id')", 'index'],
     *               'title':['varchar(100)']
     *           }
     *       }
	 *
     *   Example with UUID ids:
     *       structure = {
     *           'authors':{
     *               'id':['uuid', 'primary'],
     *               'first_name':['varchar(50)'],
     *               'last_name':['varchar(50)', 'nonull', 'index']
     *           },
     *           'books':{
     *               'id':['uuid', 'primary'],
     *               'author_id':['uuid', "fk_cascade('authors','id')", 'index'],
     *               'title':['varchar(100)']
     *           }
     *       }
	 *
     *   Acceptible values for type and how they are parsed for PostgreSQL (case does not matter, bool == BOOL == Bool == bOoL):
     *   boolean, bool	                TINYINT
     *   char(n)	                    CHAR(n)
     *   varchar(n)	                	VARCHAR(n)
     *   text	                        TEXT
     *   tinyint, int1	                SMALLINT
     *   smallint, int2	                SMALLINT
     *   mediumint, int3	            INTEGER
     *   integer, int, int4	        	INTEGER
     *   bigint, int8	                BIGINT
     *   real, float4	                REAL
     *   double, float8, float	        FLOAT
     *   numeric(p,s), decimal(p,s)		NUMERIC(p,s)
     *   date	                        DATE
     *   time	                        TIME
     *   time_with_time_zone	        TIME WITH TIMEZONE
     *   datetime 	                	DATETIME
     *   timestamp	                	DATETIME
     *   uuid	                        CHAR(36)
     *   primary_auto	                SERIAL PRIMARY KEY
     *   serial	                        SERIAL PRIMARY KEY
     *   auto_increment	                SERIAL PRIMARY KEY
     *   
     *   
     *   Acceptible values for constraint are (case does not matter, nonull == NONULL == NoNull == Nonull):
     *   primary : PRIMARY KEY
     *   nonull : NOT NULL
     *   unique : UNIQUE
     *   nonull_unique : NOT NULL UNIQUE
	 *
     *   Acceptible value for index (case does not matter, index == INDEX == Index == iNdEx):
     *   index
	 */
	public function __construct($structure, $dbname=NULL)
	{
		$this->dbname = $dbname;
        foreach ($structure as $t=>$arr1)
		{
		    $comma = '';
            $this->table_name = $t;
            $csql = "CREATE TABLE $t (";
            foreach ($arr1 as $f=>$arr2);
			{
                $csql .= "$comma\n\t";
                $csql .= $this->field($f, $arr2);
                $comma = ',';
			}
            $csql .= "\n);";
            $this->tables[] = $csql;
		}
    }
    protected function _char($n)
	{
        return "CHAR($n)";
	}
 
    protected function _varchar($n)
	{
        return "VARCHAR($n)";
	}
 
    protected function _numeric($p, $s)
	{
        return "NUMERIC($p, $s)";
	}
 
    protected function _decimal($p, $s)
	{
        return "NUMERIC($pd, $s)";
	}
 
    protected function _fk($parent_table, $parent_field)
	{
        $this->fks[] = "ALTER TABLE {$this->table_name} ADD FOREIGN KEY ({$this->field_name}) REFERENCES $parent_table($parent_field) ON DELETE RESTRICT;";
        return '';
	}
 
    protected function _fk_cascade($parent_table, $parent_field)
	{
        $this->fks[] = "ALTER TABLE {$this->table_name} ADD FOREIGN KEY ({$this->field_name}) REFERENCES $parent_table($parent_field) ON DELETE CASCADE;";
        return '';
	}
 
    protected function index()
	{
        $this->indices[] = "CREATE INDEX {$this->table_name}_{$this->field_name}_idx ON {$this->table_name}({$this->field_name});";
	}
 
    protected function field($field_name, $type_def)
	{
		$this->field_name = $field_name;
        $type_str = strtolower($type_def[0]);
		eval('$my_type = $this->_'.$type_str.';');
		$constraint = '';
        $index = false;
        if (count($type_def) > 1) {
            eval('$constraint = $this->_'.strtolower($type_def[1]).';');
		}
        if (count($type_def) > 2) {
            $index = (strtoupper($type_def[2]) == 'INDEX');
		}
        if (in_array($type_str, array('primary_auto','auto_increment','serial'))) {
            $constraint = '';
            $index = false;
		}
        if ($constraint == 'PRIMARY') {
            $index = false;
		}
        if ($index) {
            $this->index();
		}
        $fieldline = "$field_name $my_type $constraint";
        return $fieldline;
	}
 
    public function create()
	{
        $spdo = new SPDO($this->dbname);
        foreach ($this->tables as $sql)
		{
            $spdo->query($sql);
		}
        foreach ($this->indices as $sql)
		{
            $spdo->query($sql);
		}
        foreach ($this->fks as $sql)
		{
            $spdo->query($sql);
		}
	}
 
    public function inspect()
	{
        $statements = array_merge($this->tables,$this->indices,$this->fks);
		$result = '';
		foreach ($statements as $st)
		{
			$result .= $st."\n";
		}
        return $result;
	}
 
}
?>

db_my.php

<?php
/*
FILE:
db_my.php (http://www.evardsson.com/files/spdo-php.tar.bz2)
 
LICENSE:
The MIT License
 
Copyright (c) 2009 Sjan Evardsson
 
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
 
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
 
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
*/
require_once('config.php');
require_once('spdoerror.php');
/**
 * SPDO class provides a quick class
 * for databse connection/querying.
 * This class makes use of MySQL Prepared Statements. This class does not make use of mysqli_prepare().
 * The reasons for this are:
 *      1. Not every installation has the mysqli extension. This avoids having to code the same thing 2 ways.
 *      2. The mysqli_prepare() -> mysqli_bind_parameters() -> mysqli_bind_results() process is overly complex for 
 *              what should be a simple task.
 *      3. I wanted to code this to act as much like the Python DBO as possible to make switching between the two simpler. 
 * @version 0.9
 * @author Sjan Evardsson
 * @info: http://www.evardsson.com/wiki/SPDO
 */
 
// prepared statement field types
define('STR','text');
define('INT','int');
define('FLOAT','numeric');
define('BOOL','int');
define('DATETIME','text');
define('LONG','bigint');
 
class SPDO
{
 
    /* db name */
    protected $dbname;
    /* user name */
    protected $dbuser;
    /* password */
    protected $dbpass;
    /* db host */
    protected $dbhost;
    /* db port */
    protected $dbport;
    /* connection: each instance holds its own */
    protected $connection;
    /* prepared statments */
    protected $statements;
 
    /**
     * Constructor: creates a new DBObject
     * @param dbname string database name
     * @param dbuser string database username
     * @param dbpass string database password
     * @param dbhost string database host
     * @param dbport int database port
     * Parameters can be omitted, right to left for default values
     * which are set in config.php
     */
    public function __construct($dbname=null, $dbuser=null, $dbpass=null, $dbhost=null, $dbport=null)
    {
        global $CONFIG;
        $this->dbname = (is_null($dbname))?$CONFIG['dbname']:$dbname;
        $this->dbuser = (is_null($dbuser))?$CONFIG['dbuser']:$dbuser;
        $this->dbpass = (is_null($dbpass))?$CONFIG['dbpass']:$dbpass;
        $this->dbhost = (is_null($dbhost))?$CONFIG['dbhost']:$dbhost;
        $this->dbport = (is_null($dbport))?$CONFIG['dbport']:$dbport;
        $this->connection = $this->connect();
        if (!$this->connection) { //
            throw new ConnectionFailedError("Unable to connect to host={$this->dbhost} port={$this->dbport} dbname={$this->dbname} user={$this->dbuser} password={$this->dbpass}");
        }
        $this->statements = array();
    }
 
    /**
     * Destructor closes the connection this instance holds
     */
    public function __destruct()
    {
        // first let's clean up all our prepared statements
        foreach ($this->statements as $name=>$s) {
            @mysql_query("DEALLOCATE PREPARE {$s->skey}");
        }
        // now close the connection
        @mysql_close($this->connection);
    }
 
    /**
     * Connect returns a connection object based on the values
     * contained in this object.
     * @return connection object or false on failure
     */
    public function connect()
    {
        if (!$this->connection) {
            $port = '';
            if (!is_null($this->dbport)) {
                $port = ':'.$this->dbport;
            }
            $this->connection = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass);
            mysql_select_db($this->dbname, $this->connection);
        }
        return $this->connection;
    }
 
    /**
     * Retrieve data from the database (SELECT)
     * This function ALWAYS returns an array, even if there is no data, meaning it is safe for immediately calling foreach() on the results
     * @param $query string SQL query "SELECT * FROM foo WHERE foodate > ? or bar = ?"
     * @param data array of data to fill in the blanks (?) array(new DateTime('now'), 'zob') DEFAULT NULL
     * @param $key string field name to use as associative array key for each row
     * @param $type NON_ASSOC (default, use no field as key) ASSOC (use the value of a field as a key for each row) 
     *      MULTI_ASSOC (use the value of a field as a key for a non-associative array of rows which all have the same field value)
     * @return array of rows formatted as in one of the examples below:
     * Example 1: non-associative (default):
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC", array(new DateTime('2008-12-31'), 'zob'));
     *      returns: array (
     *          0=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30'),
     *          1=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30'),
     *          2=>array('id'=>'3','bar'=>'zob','foodate'=>'2008-12-31')
     *          3=>array('id'=>'4','bar'=>'zob','foodate'=>'2009-01-01')
     *      )
     * Example 2: associative array: (notice that last value returned overwrites the row if more than one row with the same key value exist)
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC", array(new DateTime('2008-12-31'), 'zob'),"bar");
     *      returns: array (
     *          'baz=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30'),
     *          'bat'=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30'),
     *          'zob'=>array('id'=>'4','bar'=>'zob','foodate'=>'2009-01-01')
     *      )
     * Example 2: multi-associative array:
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC",
     *              array(new DateTime('2008-12-31'), 'zob'),"bar",MULTI_ASSOC);
     *      returns: array (
     *          'baz'=>array(
     *              0=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30')
     *          ),
     *          'bat'=>array(
     *              0=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30')
     *          ),
     *          'zob'=>array(
     *              0=>array('id'=>'3','bar'=>'zob','foodate'=>'2008-12-31')
     *              1=>array('id'=>'3','bar'=>'zob','foodate'=>'2009-01-01')
     *          )
     *      )
     */
    public function select($sql, $data=NULL, $key=NULL, $type=NON_ASSOC)
    {
        $result = array();
        if (!is_null($data)) {
            $tmp = $this->fixuuid($data);
            $data = $tmp['data'];
        }
        $ps = $this->prepare_statement($sql, $data);
        // fix up type for ASSOC:
        if (!is_null($key) && $type == NON_ASSOC)
            $type = ASSOC;
        $res = $ps->execute($data);
        if ($res) while ($row = mysql_fetch_assoc($res))
        {
            switch($type)
            {
                case ASSOC:
                    $result[$row[$key]] = $row;
                    break;
                case MULTI_ASSOC:
                    $result[$row[$key]][] = $row;
                    break;
                case NON_ASSOC:
                default:
                    $result[] = $row;
                    break;
            }
        }
        mysql_free_result($res);
        return $result;
    }
 
    /**
     * Insert a new row in the database (INSERT)
     * This function assumes the following:
     *      If using a UUID and the database does not have a uuid() function installed the INSERT statement MUST be written using id with '?UUID?' 
     *          for the data in that position
   *        Example:
   *            $newid = $db->insert("INSERT INTO foo (id, field1) VALUES (?, ?)", array('?UUID?','bar'));
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @param $id_field string id field name
     * @return mixed row id
     */
    public function insert($sql, $data, $id_field='id')
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to insert without data!");
        }
        $result = 0;
        $tmp = $this->fixuuid($data);
        $data = $tmp['data'];
        $ps = $this->prepare_statement($sql, $data);
        $res = $ps->execute($data);
		if (!$res) {
			$ermsg = mysql_error();
			if (strpos($ermsg, 'Foreign Key Constraint Violated!') !== false) {
				throw new ForeignKeyViolationError($ermsg);
			} else {
				throw new SPDOError($ermsg);
			}
		}
        $nid = mysql_insert_id($res);
        if (mysql_affected_rows($this->connection) < 1) return 0;
        return ($nid < 1)?$tmp['uuid']:$nid;
    }
 
    /**
     * Update a row in the database (UPDATE)
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return int number of rows affected
     */
    public function update($sql, $data)
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to update without data!");
        }
        $result = 0;
        $tmp = $this->fixuuid($data);
        $data = $tmp['data'];
        $ps = $this->prepare_statement($sql, $data);
        $res = $ps->execute($data);
        if ($res) {
			$result = mysql_affected_rows($this->connection);
		} else {
			$ermsg = mysql_error();
			if (strpos($ermsg, 'Foreign Key Constraint Violated!') !== false) {
				throw new ForeignKeyViolationError($ermsg);
			} else {
				throw new SPDOError($ermsg);
			}
		}
        return $result;
    }
 
    /**
     * Delete a row in the database (DELETE)
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return int number of rows affected
     */
    public function delete($sql, $data)
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to delete without data! Doing so would empty the entire table!");
        }
        return $this->update($sql, $data);
    }
 
    /**
     * Perform a raw query. This is NOT created as a PreparedStatement, so has no setup overhead involved,
     * however, subsequent calls to the same query string will take longer than subsequent calls using a PreparedStatement
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return ResultSet
     */
    public function query($sql, $data=NULL)
    {
        if (!is_null($data)) {
            $q = '';
            $parts = explode('?', $sql);
            for ($i = 0; $i < count($data); $i++)
            {
                $q .= $parts[$i];
                $q .= $this->smartquote($data[$i]);
            }
            for ($i = count($data); $i < count($parts); $i++)
            {
                $q .= $parts[$i];
            }
            $sql = $q;;
        }
        return mysql_query($sql,$this->connection);
    }
 
    /**
     * Perform a raw query. This IS created as a PreparedStatement, so has some setup overhead involved,
     * however, subsequent calls to the same query string will take less time than subsequent calls NOT using a PreparedStatement
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return ResultSet
     */
    public function ps_query($sql, $data=NULL)
    {
        if (!is_null($data)) {
            $tmp = $this->fixuuid($data);
            $data = $tmp['data'];
        }
        $ps = $this->prepare_statement($sql, $data);
        return $ps->execute($data);
    }
 
    /**
     * Return a PreparedStatement object: if this object is already in the statements dict return that, otherwise create a new one
     * and place it in the statements dict. The key for prepared statements in the dict is the SQL, to lower case with all whitespaces removed.
     * Therefore the following are all equivalent:
     *      "INSERT INTO mytable (foo, bar, foodate) VALUES (?,?,?)"
     *      "insert into
     *              mytable
     *                  (foo, bar, foodate)
     *              values
     *                  (?,?,?)"
     *      "Insert Into mytable ( foo , bar , foodate ) Values ( ? , ? , ?)"
     * @param sql string SQL query
     * @param data array of data
     * @return PreparedStatement object
     */
    public function prepare_statement($sql, $data)
    {
        $pname = strtolower(preg_replace('/\s+/','',$sql));
        if (!isset($this->statements[$pname])) {
            $types = $this->buildtypes($data);
            $ps = new PreparedStatement($this->connection, $sql, $types);
            $this->statements[$pname] = $ps;
        }
        return $this->statements[$pname];
    }
 
    /**
     * Private convenience function to properly build typelist for prepare_statement based on data list passed
     * @param data array of data to build types for
     * @return array of types
     */
    private function buildtypes($data)
    {
        if (is_null($data)) return $data;
        $dtstr = 'DateTime';
        $types = array();
        foreach($data as $d)
        {
            if (is_array($d)) {
                $tmp = $this->buildtypes($d);
                $types[] = $tmp[0].'[]';
            } else if (is_int($d) || is_long($d)) {
                $types[] = LONG;
            } else if (is_float($d)) {
                $types[] = FLOAT;
            } else if (is_bool($d)) {
                $types[] = BOOL;
            } else if ($d instanceof $dtstr) {
                $types[] = DATETIME;
            } else {
                $types[] = STR;
            }
        }
        return $types;
    }
 
    /**
     * Private convenience function to replace '?UUID?' with an actual UUID in the data list.
     * @param data array of data
     * @return array of like array('data'=>$data, 'uuid'=>$uuid) data with all occurences of '?UUID?' replaced with an actual UUID
     */
    private function fixuuid($data)
    {
        $returndata = array('data'=>array(),'uuid'=>NULL);
        foreach($data as $d)
        {
            if ($d == '?UUID?') {
                $u = uuid();
                $retdata[] = $u;
                $returndata['uuid'] = $u;
            } else if (is_array($d)) {
                $di = array();
                foreach($d as $dx)
                {
                    if ($dx == '?UUID?') {
                        $di[] = uuid();
                    } else {
                        $di[] = $dx;
                    }
                }
                $retdata[] = $di;
            } else { 
                $retdata[] = $d;
            }
        }
        $returndata['data'] = $retdata;
        return $returndata;
    }
 
    /**
     * Private convenience function to replace '?UUID?' with an actual UUID and safely escape strings
     * @param datum mixed item to be escaped & quoted
     * @return mixed escaped datum
     */
    private function smartquote($datum)
    {
        $dtstr = 'DateTime';
        $retdata = $datum;
        if (is_null($datum)) {
            $retdata = 'NULL';
        } else if ($datum == '?UUID?') {
            $retdata = "'".uuid()."'";
        } else if (is_array($datum)) {
            $di = array();
            foreach($datum as $dx)
            {
                $di[] = $this->smartquote($dx);
            }
            $retdata = $di;
        } else if ($datum instanceof $dtstr) {
            $retdata = "'".$datum->format('Y-m-d H:i:sP')."'";
        } else if (is_string($datum)) {
            $retdata = "'".mysql_real_escape_string($datum)."'";
        }
        return $retdata;
    }
 
    /**
     * Test functionality
     * Requires a table named test. See the create_mysql_testtable.sql file.        
     */
    public function test()
    {
        print "<pre>\n";
        var_dump($this);
        print "<br />\n";
        print "<br />\nTesting insert(\"INSERT INTO test (id, name, value) VALUES (?,?,?)\",array('?UUID?','testrun','This is only a test'))";
        $x = $this->insert("INSERT INTO test (id, name, value) VALUES (?,?,?)",array('?UUID?','testrun','This is only a test'));
        print "<br />\nLast inserted id: $x";
        print "<br />\nTesting for(\$i = 0; \$i < 10; \$i++) { insert(\"INSERT INTO test (id, name, value) VALUES (?,?,?)\",array('?UUID?','testrun'.\$i,'This is only a test')); }";
        $loopids = array();
        for($i = 0; $i < 10; $i++) { 
            $lid = $this->insert("INSERT INTO test (id, name, value) VALUES (?,?,?)",array('?UUID?','testrun'.$i,'This is only a test')); 
            $loopids[] = $lid;
            print "<br />\nLast inserted id: $lid";
        }
        print "<br />\nAll inserted ids from loop (\$loopids):";
        print_r($loopids);
        print "<br />\n<br />\nTesting update(\"UPDATE test SET value=? WHERE id=?\",array('This is still only a test','$x'))";
        $y = $this->update("UPDATE test SET value=? WHERE id=?",array('This is still only a test',$x));
        print "<br />\nUpdated $y rows";
        print "<br />\n<br />\nTesting select(\"SELECT * FROM test\")";
        $z = $this->select("SELECT * FROM test");
        foreach($z as $i)
        {
            foreach($i as $key=>$val)
            {
                print "<br />\n\t$key\t$val";
            }
        }
        print "<br />\n<br />\nTesting delete(\"DELETE FROM test WHERE id=?\",array('$x'))";
        $l = $this->delete("DELETE FROM test WHERE id=?",array($x));
        print "<br />\nDeleted $l rows";
        print "<br />\nTesting for(\$i = 0; \$i < 10; \$i++) { delete(\"DELETE FROM test where id=?\",array(\$loopids[\$i])); }";
        $lc = 0;
        for($i = 0; $i < 10; $i++) { 
            $lc += $this->delete("DELETE FROM test WHERE id=?",array($loopids[$i])); 
        }
        print "<br />\nDeleted $lc rows";
        print "<br />\nDone";
    }
 
}
 
/**
 * PreparedStatement class holds a prepared statement and provides a direct method for executing it.
 */
 
class PreparedStatement
{
 
    /* the db connection */
    protected $connection;
    /* the query passed to PREPARE */
    protected $sql;
    /* an array of field types passed to PREPARE */
    protected $types;
    /* a key that is guaranteed to belong to only one prepared statement on a given connection */
    protected $skey;
    /* the name of this prepared statement: the sql to lower case minus all white space */
    protected $name;
    /* array of parameter names for use in execution of this statement */
    protected $params;
 
    /**
     * Create a new PreparedStatement object.
     * This also runs the PREPARE query on the server so that this statement is ready to use.
     * @param connection database connection object
     * @param sql string SQL query to prepare
     * @param types array of types (see prepared statement field types)
     * @return PreparedStatement object
     */
    public function __construct($connection, $sql, $types)
    {
        $this->name = strtolower(preg_replace('/\s+/','',$sql));
        $this->skey = 'ps'.md5($this->name);
        for ($i = 0; $i < count($types); $i++)
        {
            $this->params[$i] = '@var'.$i;
        }
        $this->connection = $connection;
        $this->types = $types;
 
        $this->sql = $sql;
        $query = "PREPARE {$this->skey} FROM \"{$this->sql}\"";
        $this->statement = $query;
        $tmp = mysql_query($this->statement, $this->connection);
    }
 
    /**
     * Execute this prepared statement.
     * @param args array of values to be set to the EXECUTE statement
     * @return resource object or boolean (depending on query type)
     */
    public function execute($args)
    {
        $query = '';
        if (!is_null($args)) {
            $query .= "SET ";
            $comma = '';
            for ($i = 0; $i < count($args); $i++)
            {
                $query .= $comma.$this->params[$i].'=';
                if ($this->types[$i] == STR) {
                    $query .= "'".mysql_real_escape_string($args[$i])."'";
                } else {
                    $query .= $args[$i];
                }
                $comma = ' , ';
            }
            mysql_query($query, $this->connection);
        }
        $query = "EXECUTE {$this->skey}";
        if (!is_null($args)) $query .= " USING ".implode(', ',$this->params);
        return mysql_query($query, $this->connection);
    }
}
 
/**
 * DbBuilder provides a way to create all the tables and foreign key triggers for a database.
 * Usage:
 * $structure = array(
 * 		'authors'=>array(
 * 			'id'=>array('primary_auto'),
 * 			'first_name'=>array('varchar(50)'),
 * 			'last_name'=>array('varchar(50)', 'NONULL', 'INDEX,)
 * 		),
 * 		'books'=>array(
 * 	   		'id'=>array('primary_auto'),
 * 			'author_id'=>array('int', "FK_CASCADE('authors','id')", 'INDEX'),
 * 			'title'=>array('varchar(100)')
 * 		)
 * );
 * $dbb = DbBuilder($structure)
 * $str = $dbb.inspect() // if you want to see the create sql
 * print $str;
 * $dbb.create()
 * 
 * The query used for this structure would be:
 * CREATE TABLE authors (
 *      id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
 *      first_name VARCHAR(50),
 *      last_name VARCHAR(50) NOT NULL
 *  );
 *  CREATE TABLE books (
 *      id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
 *      author_id INTEGER NOT NULL,
 *      title VARCHAR(100)
 *  );
 *  ALTER TABLE books ADD INDEX(author_id);
 *  CREATE TRIGGER fki_books_author_id
 *    BEFORE INSERT
 *    ON books
 *    FOR EACH ROW
 *    BEGIN
 *      IF 0 = (SELECT COUNT(*) FROM authors WHERE id=new.author_id)
 *      THEN
 *        INSERT error_msg VALUES ('Foreign Key Constraint Violated!');
 *      END IF;
 *    END;
 *  CREATE TRIGGER fku_books_author_id
 *  BEFORE UPDATE 
 *    ON books 
 *    FOR EACH ROW 
 *    BEGIN 
 *      IF
 *        0 = ( SELECT COUNT(*) FROM authors WHERE id = new.author_id )
 *      THEN
 *        INSERT INTO error_msg VALUES ('Foreign Key Constraint Violated!');
 *      END IF ;
 *    END;
 *  CREATE TRIGGER fkdc_books_author_id
 *    BEFORE DELETE
 *    ON authors
 *    FOR EACH ROW
 *    BEGIN
 *      DELETE FROM books WHERE author_id=old.id;
 *    END
 *  If FK('authors','id) had been used in the books table then the following trigger would have been generated:
 *  CREATE TRIGGER fkd_books_author_id
 *  BEFORE UPDATE 
 *    ON books 
 *    FOR EACH ROW 
 *    BEGIN 
 *      IF
 *        0 = ( SELECT COUNT(*) FROM authors WHERE id = new.author_id )
 *      THEN
 *        INSERT INTO error_msg VALUES ('Foreign Key Constraint Violated!');
 *      END IF ;
 *    END;
 */
class DbBuilder {
    protected $tables = array();
    protected $indices = array();
    protected $fks = array();
    protected $_boolean = 'TINYINT';
    protected $_bool = 'TINYINT';
    protected $_text = 'TEXT';
    protected $_tinyint = 'TINYINT';
    protected $_int1 = 'TINYINT';
    protected $_smallint = 'SMALLINT';
    protected $_int2 = 'SMALLINT';
    protected $_mediumint = 'MEDIUMINT';
    protected $_int3 = 'MEDIUMINT';
    protected $_integer = 'INTEGER';
    protected $_int4 = 'INTEGER';
    protected $_int = 'INTEGER';
    protected $_bigint = 'BIGINT';
    protected $_int8 = 'BIGINT';
    protected $_real = 'FLOAT';
    protected $_float4 = 'FLOAT';
    protected $_double = 'DOUBLE';
    protected $_float8 = 'DOUBLE';
    protected $_float = 'DOUBLE';
    protected $_date = 'DATE';
    protected $_time = 'TIME';
    protected $_time_with_time_zone = 'TIME';
    protected $_datetime = 'DATETIME';
    protected $_timestamp = 'TIMESTAMP';
    protected $_uuid = 'CHAR(36)';
    protected $_primary_auto = 'INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY';
    protected $_serial = 'INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY';
    protected $_auto_increment = 'INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY';
    protected $_primary = 'PRIMARY KEY';
    protected $_nonull = 'NOT NULL';
    protected $_unique = 'UNIQUE';
    protected $_nonull_unique = 'NOT NULL UNIQUE';
 
    /**
     *  Create a new DbBuilder object. The structure is a dict of tables/fields laid out as:
     *  a multi-dimensional array (table_name=>(field_name=>(type,constraint(optional),index(optional)),field_name2=>(type,constraint(optional),index(optional)))
     *   Types and constraints are case insensitive.
     *   
     *   If constraint is a Foreign Key it must be written as "fk('parent_table_name','parent_table_column')" or
     *   "fk_cascade('parent_table_name','parent_table_column')" (note that the parent table and column names must be
     *   in quotes). FK creates a Foreign Key with ON DELETE RESTRICT while FK_CASCADE creates a Foreign Key with ON DELETE CASCADE.
     *
     *   Note that for types primary_auto, serial, auto_increment constraints and index are ignored, while for other columns declared as
     *   PRIMARY index is ignored.
     *   
     *   
     *   Example with auto generated integer ids:
     *       $structure = array(
     *           'authors'=>array(
     *               'id'=>array('primary_auto'),
     *               'first_name'=>array('varchar(50)'),
     *               'last_name'=>array('varchar(50)', 'nonull', 'index')
     *           ),
     *           'books'=>array(
     *               'id'=>array('primary_auto'),
     *               'author_id'=>array('int', "fk_cascade('authors','id')", 'index'),
     *               'title'=>array('varchar(100)')
     *           )
     *       );
	 *
     *   Example with UUID ids:
     *       $structure = array(
     *           'authors'=>array(
     *               'id'=>array('uuid', 'primary'),
     *               'first_name'=>array('varchar(50)'),
     *               'last_name'=>array('varchar(50)', 'nonull', 'index')
     *           ),
     *           'books'=>array(
     *               'id'=>array('uuid', 'primary'),
     *               'author_id'=>array('uuid', "fk_cascade('authors','id')", 'index'),
     *               'title'=>array('varchar(100)')
     *           )
     *       );
	 *
     *   Acceptible values for type and how they are parsed for MySQL (case does not matter, bool == BOOL == Bool == bOoL):
     *   boolean, bool	                TINYINT
     *   char(n)	                    CHAR(n)
     *   varchar(n)	 		            VARCHAR(n)
     *   text	                        TEXT
     *   tinyint, int1	                TINYINT
     *   smallint, int2	                SMALLINT
     *   mediumint, int3	            MEDIUMINT
     *   integer, int, int4	        	INTEGER
     *   bigint, int8	                BIGINT
     *   real, float4	                FLOAT
     *   double, float8, float	        DOUBLE
     *   numeric(p,s), decimal(p,s)		NUMERIC(p,s)
     *   date	                        DATE
     *   time	                        TIME
     *   time_with_time_zone	        TIME
     *   datetime 	                	DATETIME
     *   timestamp	                	TIMESTAMP
     *   uuid	                        CHAR(36)
     *   primary_auto	                INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
     *   serial	                        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
     *   auto_increment	                INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
     *   
     *   
     *   Acceptible values for constraint are (case does not matter, nonull == NONULL == NoNull == Nonull):
     *   primary : PRIMARY KEY
     *   nonull : NOT NULL
     *   unique : UNIQUE
     *   nonull_unique : NOT NULL UNIQUE
	 *
     *   Acceptible value for index (case does not matter, index == INDEX == Index == iNdEx):
     *   index
	 */
	public function __construct($structure, $dbname=NULL)
	{
		$this->dbname = $dbname;
        foreach ($structure as $t=>$arr1)
		{
		    $comma = '';
            $this->table_name = $t;
            $csql = "CREATE TABLE $t (";
            foreach ($arr1 as $f=>$arr2)
			{
                $csql .= "$comma\n\t";
                $csql .= $this->field($f, $arr2);
                $comma = ',';
			}
            $csql .= "\n);";
            $this->tables[] = $csql;
		}
    }
    protected function _char($n)
	{
        return "CHAR($n)";
	}
 
    protected function _varchar($n)
	{
        return "VARCHAR($n)";
	}
 
    protected function _numeric($p, $s)
	{
        return "NUMERIC($p, $s)";
	}
 
    protected function _decimal($p, $s)
	{
        return "NUMERIC($pd, $s)";
	}
 
    protected function _triggers_fk($parent_table, $parent_field)
	{
        $this->tables[] = "CREATE TABLE fk_error_msg (error_msg VARCHAR(50) PRIMARY KEY);";
        $this->tables[] = "INSERT INTO fk_error_msg VALUES ('Foreign Key Constraint Violated!');";
        $this->fks[] = "CREATE TRIGGER fki_{$this->table_name}_{$this->field_name}
    	BEFORE INSERT ON {$this->table_name}
    	FOR EACH ROW BEGIN
        	IF
          		0 = (SELECT COUNT(*) FROM $parent_table WHERE $parent_field=new.{$this->field_name})
        	THEN
          		INSERT fk_error_msg VALUES ('Foreign Key Constraint Violated!');
        	END IF;
    	END;";
        $this->fks[] = "CREATE TRIGGER fku_{$this->table_name}_{$this->field_name}
    	BEFORE UPDATE ON {$this->table_name}
    	FOR EACH ROW BEGIN
        	IF
          		0 = (SELECT COUNT(*) FROM $parent_table WHERE $parent_field=new.{$this->field_name})
        	THEN
          		INSERT fk_error_msg VALUES ('Foreign Key Constraint Violated!');
        	END IF;
    	END;";
	}
 
    protected function _fk($parent_table, $parent_field)
	{
        $this->_triggers_fk($parent_table, $parent_field);
        $this->fks[] = "CREATE TRIGGER fkd_{$this->table_name}_{$this->field_name}
    	BEFORE DELETE ON {$parent_table}
    	FOR EACH ROW BEGIN
        	IF
          		0 < (SELECT COUNT(*) FROM {$this->table_name} WHERE {$this->field_name}=old.$parent_field)
        	THEN
          		INSERT fk_error_msg VALUES ('Foreign Key Constraint Violated!');
        	END IF;
    	END;";
        return '';
	}
 
    protected function _fk_cascade($parent_table, $parent_field)
	{
        $this->_triggers_fk($parent_table, $parent_field);
        $this->fks[] = "CREATE TRIGGER fkdc_{$this->table_name}_{$this->field_name}
    	BEFORE DELETE ON $parent_table
    	FOR EACH ROW BEGIN
        	DELETE FROM {$this->table_name} WHERE {$this->field_name}=old.$parent_field;
    	END;";
        return '';
	}
 
    protected function index()
	{
        $this->indices[] = "ALTER TABLE {$this->table_name} ADD INDEX({$this->field_name});";
	}
 
    protected function field($field_name, $type_def)
	{
		$this->field_name = $field_name;
        $type_str = strtolower($type_def[0]);
		eval('$my_type = $this->_'.$type_str.';');
		$constraint = '';
        $index = false;
        if (count($type_def) > 1) {
            eval('$constraint = $this->_'.strtolower($type_def[1]).';');
		}
        if (count($type_def) > 2) {
            $index = (strtoupper($type_def[2]) == 'INDEX');
		}
        if (in_array($type_str, array('primary_auto','auto_increment','serial'))) {
            $constraint = '';
            $index = false;
		}
        if ($constraint == 'PRIMARY') {
            $index = false;
		}
        if ($index) {
            $this->index();
		}
        $fieldline = "$field_name $my_type $constraint";
        return $fieldline;
	}
 
    public function create()
	{
        $spdo = new SPDO($this->dbname);
        foreach ($this->tables as $sql)
		{
            $spdo->query($sql);
		}
        foreach ($this->indices as $sql)
		{
            $spdo->query($sql);
		}
        foreach ($this->fks as $sql)
		{
            $spdo->query($sql);
		}
	}
 
    public function inspect()
	{
        $statements = array_merge($this->tables,$this->indices,$this->fks);
		$result = '';
		foreach ($statements as $st)
		{
			$result .= $st."\n";
		}
        return $result;
	}
 
}
 
?>

db_sl.php

<?php
/*
FILE:
db_sl.php (http://www.evardsson.com/files/spdo-php.tar.bz2)
 
LICENSE:
The MIT License
 
Copyright (c) 2009 Sjan Evardsson
 
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
 
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
 
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
*/
require_once('config.php');
require_once('spdoerror.php');
/**
 * SPDO class provides a quick class
 * for databse connection/querying.
 * This class does not make use of sqlite Prepared Statements. This is because I have not yet found suitable documentation
 * on creating and executing prepared statements in sqlite. That said, sqlite is probably not the best choice if you are planning
 * on building something large and complex and requiring lots of consecutive reads/writes. This does follow the signature of
 * the PostgreSQL and MySQL implementations of this DBO, including having a PreparedStatement class. That class is, however, empty.
 * You have been warned. 
 * @version 0.9
 * @author Sjan Evardsson
 * @info: http://www.evardsson.com/wiki/SPDO
 */
 
// prepared statement field types
define('STR','text');
define('INT','int');
define('FLOAT','numeric');
define('BOOL','int');
define('DATETIME','text');
define('LONG','int');
 
class SPDO
{
 
    /* db name */
    protected $dbname;
    /* user name */
    protected $dbuser;
    /* password */
    protected $dbpass;
    /* db host */
    protected $dbhost;
    /* db port */
    protected $dbport;
    /* connection: each instance holds its own */
    protected $connection;
    /* prepared statments */
    protected $statements;
 
    /**
     * Constructor: creates a new DBObject
     * @param dbname string database name
     * @param dbuser string database username
     * @param dbpass string database password
     * @param dbhost string database host
     * @param dbport int database port
     * Parameters can be omitted, right to left for default values
     * which are set in config.php
     */
    public function __construct($dbname=null, $dbuser=null, $dbpass=null, $dbhost=null, $dbport=null)
    {
        global $CONFIG;
        $this->dbname = (is_null($dbname))?$CONFIG['dbname']:$dbname;
        $this->dbuser = (is_null($dbuser))?$CONFIG['dbuser']:$dbuser;
        $this->dbpass = (is_null($dbpass))?$CONFIG['dbpass']:$dbpass;
        $this->dbhost = (is_null($dbhost))?$CONFIG['dbhost']:$dbhost;
        $this->dbport = (is_null($dbport))?$CONFIG['dbport']:$dbport;
        $this->connection = $this->connect();
        if (!$this->connection) { //
            throw new ConnectionFailedError("Unable to connect to host={$this->dbhost} port={$this->dbport} dbname={$this->dbname} user={$this->dbuser} password={$this->dbpass}");
        }
        $this->statements = array();
    }
 
    /**
     * Destructor closes the connection this instance holds
     */
    public function __destruct()
    {
        // now close the connection
        @sqlite_close($this->connection);
    }
 
    /**
     * Connect returns a connection object based on the values
     * contained in this object.
     * @return connection object or false on failure
     */
    public function connect()
    {
        if (!$this->connection) {
            $this->connection = sqlite_open($this->dbname);
        }
        return $this->connection;
    }
 
    /**
     * Retrieve data from the database (SELECT)
     * This function ALWAYS returns an array, even if there is no data, meaning it is safe for immediately calling foreach() on the results
     * @param $query string SQL query "SELECT * FROM foo WHERE foodate > ? or bar = ?"
     * @param data array of data to fill in the blanks (?) array(new DateTime('now'), 'zob') DEFAULT NULL
     * @param $key string field name to use as associative array key for each row
     * @param $type NON_ASSOC (default, use no field as key) ASSOC (use the value of a field as a key for each row) 
     *      MULTI_ASSOC (use the value of a field as a key for a non-associative array of rows which all have the same field value)
     * @return array of rows formatted as in one of the examples below:
     * Example 1: non-associative (default):
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC", array(new DateTime('2008-12-31'), 'zob'));
     *      returns: array (
     *          0=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30'),
     *          1=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30'),
     *          2=>array('id'=>'3','bar'=>'zob','foodate'=>'2008-12-31')
     *          3=>array('id'=>'4','bar'=>'zob','foodate'=>'2009-01-01')
     *      )
     * Example 2: associative array: (notice that last value returned overwrites the row if more than one row with the same key value exist)
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC", array(new DateTime('2008-12-31'), 'zob'),"bar");
     *      returns: array (
     *          'baz=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30'),
     *          'bat'=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30'),
     *          'zob'=>array('id'=>'4','bar'=>'zob','foodate'=>'2009-01-01')
     *      )
     * Example 2: multi-associative array:
     *      $result = $db->select("SELECT * from foo WHERE foodate < ? or bar = ? ORDER BY foodate ASC",
     *              array(new DateTime('2008-12-31'), 'zob'),"bar",MULTI_ASSOC);
     *      returns: array (
     *          'baz'=>array(
     *              0=>array('id'=>'1','bar'=>'baz','foodate'=>'2008-12-30')
     *          ),
     *          'bat'=>array(
     *              0=>array('id'=>'2','bar'=>'bat','foodate'=>'2008-12-30')
     *          ),
     *          'zob'=>array(
     *              0=>array('id'=>'3','bar'=>'zob','foodate'=>'2008-12-31')
     *              1=>array('id'=>'3','bar'=>'zob','foodate'=>'2009-01-01')
     *          )
     *      )
     */
    public function select($sql, $data=NULL, $key=NULL, $type=NON_ASSOC)
    {
        $result = array();
        if (!is_null($data)) {
            $tmp = $this->fixuuid($data);
            $data = $tmp['data'];
        }
        $sql = $this->buildquery($sql, $data);
        // fix up type for ASSOC:
        if (!is_null($key) && $type == NON_ASSOC)
            $type = ASSOC;
        $res = sqlite_unbuffered_query($this->connection, $sql);
        if ($res) while ($row = sqlite_fetch_array($res, SQLITE_ASSOC))
        {
            switch($type)
            {
                case ASSOC:
                    $result[$row[$key]] = $row;
                    break;
                case MULTI_ASSOC:
                    $result[$row[$key]][] = $row;
                    break;
                case NON_ASSOC:
                default:
                    $result[] = $row;
                    break;
            }
        }
        return $result;
    }
 
    /**
     * Insert a new row in the database (INSERT)
     * This function assumes the following:
     *    If using an INTEGER id you have created the database with id INTEGER PRIMARY KEY: Note that this is THE ONLY way to create an id field
     *      in sqlite that uses the internal OID. Any fields that are NOT the PRIMARY KEY but use UUIDs should have values passed.
     *    If using a UUID the INSERT statement MUST be written using id with '?UUID?' 
     *      for the data in that position
   *        Example:
     *      $myuuid = uuid(); // this one will not be returned by the function
     *      // --------------------------------------------------- The UUID created to fill in here \/ will be returned ---|
   *      $newid = $db->insert("INSERT INTO foo (id, field1, otheruuid) VALUES (?, ?, ?)", array('?UUID?','bar', $myuuid));
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @param $id_field string id field name
     * @return mixed row id
     */
    public function insert($sql, $data, $id_field='id')
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to insert without data!");
        }
        $result = 0;
        $tmp = $this->fixuuid($data);
        $data = $tmp['data'];
        $sql = $this->buildquery($sql, $data);
        $ermsg = false;
		$res = sqlite_unbuffered_query($this->connection, $sql, SQLITE_ASSOC, $ermsg);
		if ($ermsg) {
			if (strpos($ermsg, 'foreign key constraint violated') !== false) {
				throw new ForeignKeyViolationError($ermsg);
			} else {
				throw new SPDOError($ermsg);
			}
		}
        $nid = sqlite_last_insert_rowid($this->connection);
        if (sqlite_changes($this->connection) < 1) return 0;
        return ($tmp['uuid'])?$tmp['uuid']:$nid;
    }
 
    /**
     * Update a row in the database (UPDATE)
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return int number of rows affected
     */
    public function update($sql, $data)
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to update without data!");
        }
        $result = 0;
        $tmp = $this->fixuuid($data);
        $data = $tmp['data'];
        $sql = $this->buildquery($sql, $data);
		$ermsg = false;
        $res = sqlite_unbuffered_query($this->connection, $sql, SQLITE_ASSOC, $ermsg);
		if ($ermsg) {
			if (strpos($ermsg, 'foreign key constraint violated') !== false) {
				throw new ForeignKeyViolationError($ermsg);
			} else {
				throw new SPDOError($ermsg);
			}
		}
        if ($res) $result = sqlite_changes($this->connection);
        return $result;
    }
 
    /**
     * Delete a row in the database (DELETE)
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return int number of rows affected
     */
    public function delete($sql, $data)
    {
        if (is_null($data)) {
            throw new SPDOError("Unable to delete without data! Doing so would empty the entire table!");
        }
        return $this->update($sql, $data);
    }
 
    /**
     * Perform a raw query. This is NOT created as a PreparedStatement.
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return ResultSet
     */
    public function query($sql, $data=NULL)
    {
        $sql = $this->buildquery($sql, $data);
        return sqlite_unbuffered_query($this->connection, $sql);
    }
 
    /**
     * Perform a raw query. This version does not use PreparedStatements so this function is an alias to query()
     * @param $sql string SQL query
     * @param data array of data to fill in the blanks
     * @return ResultSet
     */
    public function ps_query($sql, $data=NULL)
    {
        return $this->query($sql, $data);
    }
 
    /**
     * Does nothing. Added only to keep identical signatures between different versions
     * @param sql string SQL query
     * @param data array of data
     * @return NULL
     */
    public function prepare_statement($sql, $data)
    {
        return NULL;
    }
 
    /**
     * Private convenience function to properly build query
     * @param sql string SQL query
     * @param data array of data to fill in
     * @return string formatted SQL
     */
    private function buildquery($sql, $data)
    {
        if (!is_null($data)) {
            $q = '';
            $parts = explode('?', $sql);
            for ($i = 0; $i < count($data); $i++)
            {
                $q .= $parts[$i];
                $q .= $this->smartquote($data[$i]);
            }
            for ($i = count($data); $i < count($parts); $i++)
            {
                $q .= $parts[$i];
            }
            $sql = $q;
        }
        return $sql;
    }
 
    /**
     * Private convenience function to replace '?UUID?' with an actual UUID in the data list.
     * @param data array of data
     * @return array of like array('data'=>$data, 'uuid'=>$uuid) data with all occurences of '?UUID?' replaced with an actual UUID
     */
    private function fixuuid($data)
    {
        $returndata = array('data'=>array(),'uuid'=>NULL);
        foreach($data as $d)
        {
            if ($d == '?UUID?') {
                $u = uuid();
                $retdata[] = $u;
                $returndata['uuid'] = $u;
            } else if (is_array($d)) {
                $di = array();
                foreach($d as $dx)
                {
                    if ($dx == '?UUID?') {
                        $di[] = uuid();
                    } else {
                        $di[] = $dx;
                    }
                }
                $retdata[] = $di;
            } else { 
                $retdata[] = $d;
            }
        }
        $returndata['data'] = $retdata;
        return $returndata;
    }
 
    /**
     * Private convenience function to replace '?UUID?' with an actual UUID and safely escape strings
     * @param datum mixed item to be escaped & quoted
     * @return mixed escaped datum
     */
    private function smartquote($datum)
    {
        $dtstr = 'DateTime';
        $retdata = $datum;
        if (is_null($datum)) {
            $retdata = 'NULL';
        } else if ($datum == '?UUID?') {
            $retdata = "'".uuid()."'";
        } else if (is_array($datum)) {
            $di = array();
            foreach($datum as $dx)
            {
                $di[] = $this->smartquote($dx);
            }
            $retdata = $di;
        } else if ($datum instanceof $dtstr) {
            $retdata = "'".$datum->format('Y-m-d H:i:sP')."'";
        } else if (is_string($datum)) {
            $retdata = "'".sqlite_escape_string($datum)."'";
        }
        return $retdata;
    }
 
    /**
     * Test functionality
     */
    public function test()
    {
        print "<pre>\n";
        var_dump($this);
        print "<br />\n";
        print "<br />\nTesting udpate(\"CREATE TABLE test (id CHAR(36), name VARCHAR(50), value TEXT)\")";
        $c = $this->query("CREATE TABLE test (id CHAR(36) NOT NULL UNIQUE PRIMARY KEY, name VARCHAR(50), value TEXT)");
        print "<br />\nCreated ".sqlite_changes($this->connection)." table";
        print "<br />\nTesting insert(\"INSERT INTO test (id, name, value) VALUES (?,?,?)\",array('?UUID?','testrun','This is only a test'))";
        $x = $this->insert("INSERT INTO test (id, name, value) VALUES (?,?,?)",array('?UUID?','testrun','This is only a test'));
        print "<br />\nLast inserted id: $x";
        print "<br />\nTesting for(\$i = 0; \$i < 10; \$i++) { insert(\"INSERT INTO test (id, name, value) VALUES (?,?,?)\",array('?UUID?','testrun'.\$i,'This is only a test')); }";
        $loopids = array();
        for($i = 0; $i < 10; $i++) { 
            $lid = $this->insert("INSERT INTO test (id, name, value) VALUES (?,?,?)",array('?UUID?','testrun'.$i,'This is only a test')); 
            $loopids[] = $lid;
            print "<br />\nLast inserted id: $lid";
        }
        print "<br />\nAll inserted ids from loop (\$loopids):";
        print_r($loopids);
        print "<br />\n<br />\nTesting update(\"UPDATE test SET value=? WHERE id=?\",array('This is still only a test','$x'))";
        $y = $this->update("UPDATE test SET value=? WHERE id=?",array('This is still only a test',$x));
        print "<br />\nUpdated $y rows";
        print "<br />\n<br />\nTesting select(\"SELECT * FROM test\")";
        $z = $this->select("SELECT * FROM test");
        foreach($z as $i)
        {
            foreach($i as $key=>$val)
            {
                print "<br />\n\t$key\t$val";
            }
        }
        print "<br />\n<br />\nTesting delete(\"DELETE FROM test WHERE id=?\",array('$x'))";
        $l = $this->delete("DELETE FROM test WHERE id=?",array($x));
        print "<br />\nDeleted $l rows";
        print "<br />\nTesting for(\$i = 0; \$i < 10; \$i++) { delete(\"DELETE FROM test where id=?\",array(\$loopids[\$i])); }";
        $lc = 0;
        for($i = 0; $i < 10; $i++) { 
            $lc += $this->delete("DELETE FROM test WHERE id=?",array($loopids[$i])); 
        }
        print "<br />\nDeleted $lc rows";
        print "<br />\nDone";
    }
 
}
/**
 * PreparedStatement class is not used for sqlite. This is here only for signature matching
 */
class PreparedStatement
{
 
    /**
     * Signature matched constructor. Does nothing
     */
    public function __construct($connection, $sql, $types)
    {
        // do nothing
    }
 
    /**
     * Signature matched execute function. Does nothing.
     * @return NULL
     */
    public function execute($args)
    {
        return NULL;
    }
}
 
/**
 * DbBuilder provides a way to create all the tables and foreign key triggers for a database.
 * Usage:
 * $structure = array(
 * 		'authors'=>array(
 * 			'id'=>array('primary_auto'),
 * 			'first_name'=>array('varchar(50)'),
 * 			'last_name'=>array('varchar(50)', 'NONULL', 'INDEX,)
 * 		),
 * 		'books'=>array(
 * 	   		'id'=>array('primary_auto'),
 * 			'author_id'=>array('int', "FK_CASCADE('authors','id')", 'INDEX'),
 * 			'title'=>array('varchar(100)')
 * 		)
 * );
 * $dbb = DbBuilder($structure)
 * $str = $dbb.inspect() // if you want to see the create sql
 * print $str;
 * $dbb.create()
 * 
 * The query used for this structure would be:
 * CREATE TABLE authors (
 *      id INTEGER NOT NULL PRIMARY KEY,
 *      first_name VARCHAR(50),
 *      last_name VARCHAR(50) NOT NULL
 *  );
 *  CREATE TABLE books (
 *      id INTEGER NOT NULL PRIMARY KEY,
 *      author_id INTEGER NOT NULL,
 *      title VARCHAR(100)
 *  );
 * CREATE INDEX books_author_id_idx ON books(author_id);
 * CREATE TRIGGER fki_books_author_id
 * BEFORE INSERT ON [books]
 *     FOR EACH ROW BEGIN
 *       SELECT RAISE(ROLLBACK, 'insert on table "books" violates foreign key constraint "fki_books_author_id"')
 *         WHERE NEW.author_id IS NOT NULL AND (SELECT id FROM authors WHERE id = NEW.author_id) IS NULL;
 *     END;
 * CREATE TRIGGER fku_books_author_id
 * BEFORE UPDATE ON [books]
 *     FOR EACH ROW BEGIN
 *         SELECT RAISE(ROLLBACK, 'update on table "books" violates foreign key constraint "fku_books_author_id"')
 *           WHERE NEW.author_id IS NOT NULL AND (SELECT id FROM authors WHERE id = NEW.author_id) IS NULL;
 *     END;
 * CREATE TRIGGER fkdc_books_author_id
 * BEFORE DELETE ON authors
 *     FOR EACH ROW BEGIN
 *         DELETE FROM books WHERE books.author_id = OLD.id;
 *     END;
 * 
 * If FK('authors','id) had been used in the books table then the following trigger would have been generated:
 * CREATE TRIGGER fkd_books_author_id
 * BEFORE DELETE ON authors
 *     FOR EACH ROW BEGIN
 *       SELECT RAISE(ROLLBACK, 'delete on table "authors" violates foreign key constraint "fkd_books_author_id"')
 *         WHERE (SELECT author_id FROM books WHERE author_id = OLD.id) IS NOT NULL;
 *     END;
 */
class DbBuilder {
    protected $tables = array();
    protected $indices = array();
    protected $fks = array();
    protected $_boolean = 'BOOL';
    protected $_bool = 'BOOL';
    protected $_text = 'TEXT';
    protected $_tinyint = 'INTEGER';
    protected $_int1 = 'INTEGER';
    protected $_smallint = 'INTEGER';
    protected $_int2 = 'INTEGER';
    protected $_mediumint = 'INTEGER';
    protected $_int3 = 'INTEGER';
    protected $_integer = 'INTEGER';
    protected $_int4 = 'INTEGER';
    protected $_int = 'INTEGER';
    protected $_bigint = 'INTEGER';
    protected $_int8 = 'INTEGER';
    protected $_real = 'FLOAT';
    protected $_float4 = 'FLOAT';
    protected $_double = 'FLOAT';
    protected $_float8 = 'FLOAT';
    protected $_float = 'FLOAT';
    protected $_date = 'DATE';
    protected $_time = 'TIME';
    protected $_time_with_time_zone = 'TIME';
    protected $_datetime = 'DATETIME';
    protected $_timestamp = 'DATETIME';
    protected $_uuid = 'CHAR(36)';
    protected $_primary_auto = 'INTEGER NOT NULL PRIMARY KEY';
    protected $_serial = 'INTEGER NOT NULL PRIMARY KEY';
    protected $_auto_increment = 'INTEGER NOT NULL PRIMARY KEY';
    protected $_primary = 'PRIMARY KEY';
    protected $_nonull = 'NOT NULL';
    protected $_unique = 'UNIQUE';
    protected $_nonull_unique = 'NOT NULL UNIQUE';
 
    /**
     *  Create a new DbBuilder object. The structure is a dict of tables/fields laid out as:
     *  a dict {table_name:{field_name:[type,constraint(optional),index(optional)],field_name2[type,constraint(optional),index(optional)]}
     *   Types and constraints are case insensitive.
     *   
     *   If constraint is a Foreign Key it must be written as "fk('parent_table_name','parent_table_column')" or
     *   "fk_cascade('parent_table_name','parent_table_column')" (note that the parent table and column names must be
     *   in quotes). FK creates a Foreign Key with ON DELETE RESTRICT while FK_CASCADE creates a Foreign Key with ON DELETE CASCADE.
     *
     *  !!!! WARNING !!!!:
     *      In sqlite3 the behavior of FK_CASCADE can not be guaranteed (it works for me on Linux, but not in Mac, not tested in Windows yet).
     *
	 *   Note that for types primary_auto, serial, auto_increment constraints and index are ignored, while for other columns declared as
     *   PRIMARY index is ignored.
     *   
     *   
     *   Example with auto generated integer ids:
     *       structure = {
     *           'authors':{
     *               'id':['primary_auto'],
     *               'first_name':['varchar(50)'],
     *               'last_name':['varchar(50)', 'nonull', 'index']
     *           },
     *           'books':{
     *               'id':['primary_auto'],
     *               'author_id':['int', "fk_cascade('authors','id')", 'index'],
     *               'title':['varchar(100)']
     *           }
     *       }
	 *
     *   Example with UUID ids:
     *       structure = {
     *           'authors':{
     *               'id':['uuid', 'primary'],
     *               'first_name':['varchar(50)'],
     *               'last_name':['varchar(50)', 'nonull', 'index']
     *           },
     *           'books':{
     *               'id':['uuid', 'primary'],
     *               'author_id':['uuid', "fk_cascade('authors','id')", 'index'],
     *               'title':['varchar(100)']
     *           }
     *       }
	 *
     *   Acceptible values for type and how they are parsed for SQLite3 (case does not matter, bool == BOOL == Bool == bOoL):
     *   boolean, bool	                TINYINT
     *   char(n)                        CHAR(n)
     *   varchar(n)		                VARCHAR(n)
     *   text	                        TEXT
     *   tinyint, int1	                INTEGER
     *   smallint, int2	                INTEGER
     *   mediumint, int3	            INTEGER
     *   integer, int, int4	        	INTEGER
     *   bigint, int8	                INTEGER
     *   real, float4	                FLOAT
     *   double, float8, float	        FLOAT
     *   numeric(p,s), decimal(p,s)		NUMERIC(p,s)
     *   date	                        DATE
     *   time	                        TIME
     *   time_with_time_zone	        TIME
     *   datetime 	                	DATETIME
     *   timestamp	                	DATETIME
     *   uuid	                        CHAR(36)
     *   primary_auto	                INTEGER NOT NULL PRIMARY KEY
     *   serial	                        INTEGER NOT NULL PRIMARY KEY
     *   auto_increment	                INTEGER NOT NULL PRIMARY KEY
     *   
     *   
     *   Acceptible values for constraint are (case does not matter, nonull == NONULL == NoNull == Nonull):
     *   primary : PRIMARY KEY
     *   nonull : NOT NULL
     *   unique : UNIQUE
     *   nonull_unique : NOT NULL UNIQUE
	 *
     *   Acceptible value for index (case does not matter, index == INDEX == Index == iNdEx):
     *   index
	 */
	public function __construct($structure, $dbname=NULL)
	{
		$this->dbname = $dbname;
        foreach ($structure as $t=>$arr1)
		{
		    $comma = '';
            $this->table_name = $t;
            $csql = "CREATE TABLE $t (";
            foreach ($arr1 as $f=>$arr2)
			{
                $csql .= "$comma\n\t";
                $csql .= $this->field($f, $arr2);
                $comma = ',';
			}
            $csql .= "\n);";
            $this->tables[] = $csql;
		}
    }
    protected function _char($n)
	{
        return "CHAR($n)";
	}
 
    protected function _varchar($n)
	{
        return "VARCHAR($n)";
	}
 
    protected function _numeric($p, $s)
	{
        return "NUMERIC($p, $s)";
	}
 
    protected function _decimal($p, $s)
	{
        return "NUMERIC($pd, $s)";
	}
 
    protected function _triggers_fk($parent_table, $parent_field)
	{
        $this->fks[] = "CREATE TRIGGER fki_{$this->table_name}_{$this->field_name}
    	BEFORE INSERT ON [{$this->table_name}]
			FOR EACH ROW BEGIN
				SELECT RAISE(ROLLBACK, 'insert on table \"{$this->table_name}\" violates foreign key constraint \"fki_{$this->table_name}_{$this->field_name}\"')
			WHERE NEW.{$this->field_name} IS NOT NULL AND (SELECT $parent_field FROM $parent_table WHERE $parent_field = NEW.{$this->field_name}) IS NULL;
			END;";
        $this->fks[] = "CREATE TRIGGER fku_{$this->table_name}_{$this->field_name}
    	BEFORE UPDATE ON [{$this->table_name}]
			FOR EACH ROW BEGIN
				SELECT RAISE(ROLLBACK, 'insert on table \"{$this->table_name}\" violates foreign key constraint \"fku_{$this->table_name}_{$this->field_name}\"')
			WHERE NEW.{$this->field_name} IS NOT NULL AND (SELECT $parent_field FROM $parent_table WHERE $parent_field = NEW.{$this->field_name}) IS NULL;
			END;";
	}
 
    protected function _fk($parent_table, $parent_field)
	{
        $this->_triggers_fk($parent_table, $parent_field);
        $this->fks[] = "CREATE TRIGGER fkd_{$this->table_name}_{$this->field_name}
    	BEFORE DELETE ON $parent_table
			FOR EACH ROW BEGIN
				SELECT RAISE(ROLLBACK, 'delete on table \"$parent_table\" violates foreign key constraint \"fkd_{$this->table_name}_{$this->field_name}\"')
				WHERE (SELECT {$this->field_name} FROM {$this->table_name} WHERE {$this->field_name} = OLD.$parent_field) IS NOT NULL;
			END;";
        return '';
	}
 
    protected function _fk_cascade($parent_table, $parent_field)
	{
        $this->_triggers_fk($parent_table, $parent_field);
        $this->fks[] = "CREATE TRIGGER fkdc_{$this->table_name}_{$this->field_name}
    	BEFORE DELETE ON $parent_table
    	FOR EACH ROW BEGIN
        	DELETE FROM {$this->table_name} WHERE {$this->table_name}.{$this->field_name}=old.$parent_field;
    	END;";
        return '';
	}
 
    protected function index()
	{
        $this->indices[] = "CREATE INDEX {$this->table_name}_{$this->field_name}_idx ON {$this->table_name}({$this->field_name});";
	}
 
    protected function field($field_name, $type_def)
	{
		$this->field_name = $field_name;
        $type_str = strtolower($type_def[0]);
		eval('$my_type = $this->_'.$type_str.';');
		$constraint = '';
        $index = false;
        if (count($type_def) > 1) {
            eval('$constraint = $this->_'.strtolower($type_def[1]).';');
		}
        if (count($type_def) > 2) {
            $index = (strtoupper($type_def[2]) == 'INDEX');
		}
        if (in_array($type_str, array('primary_auto','auto_increment','serial'))) {
            $constraint = '';
            $index = false;
		}
        if ($constraint == 'PRIMARY') {
            $index = false;
		}
        if ($index) {
            $this->index();
		}
        $fieldline = "$field_name $my_type $constraint";
        return $fieldline;
	}
 
    public function create()
	{
        $spdo = new SPDO($this->dbname);
        foreach ($this->tables as $sql)
		{
            $spdo->query($sql);
		}
        foreach ($this->indices as $sql)
		{
            $spdo->query($sql);
		}
        foreach ($this->fks as $sql)
		{
            $spdo->query($sql);
		}
	}
 
    public function inspect()
	{
        $statements = array_merge($this->tables,$this->indices,$this->fks);
		$result = '';
		foreach ($statements as $st)
		{
			$result .= $st."\n";
		}
        return $result;
	}
 
}
 
?>

test.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Test File for SPDO</title>
</head>
<?php
require_once('spdo.php');
$structure = array(
	'authors'=>array(
		'id'=>array('serial'),
		'first_name'=>array('varchar(50)'),
		'last_name'=>array('varchar(50)','nonull','index')
	),
	'books'=>array(
		'id'=>array('serial'),
		'author_id'=>array('int',"fk_cascade('authors','id')",'index'),
		'title'=>array('varchar(100)','nonull','index')
	)
);
$dbb = new DbBuilder($structure);
$str = $dbb->inspect();
print $str;
$dbb->create();
$db = new SPDO('test');
$auth1 = $db->insert("INSERT INTO authors (first_name, last_name) VALUES (?,?)", array('Sam','Smith'));
$auth1 = (int)$auth1;
$auth2 = $db->insert("INSERT INTO authors (first_name, last_name) VALUES (?,?)", array('Joe','Jones'));
$auth2 = (int)$auth2;
$db->insert("INSERT INTO books (author_id, title) VALUES (?,?)", array($auth1, 'Big Book of Foo'));
$db->insert("INSERT INTO books (author_id, title) VALUES (?,?)", array($auth1, 'Big Book of Bar'));
$db->insert("INSERT INTO books (author_id, title) VALUES (?,?)", array($auth1, 'Big Book of Baz'));
$db->insert("INSERT INTO books (author_id, title) VALUES (?,?)", array($auth2, 'Little Book of Foo'));
$db->insert("INSERT INTO books (author_id, title) VALUES (?,?)", array($auth2, 'Little Book of Bar'));
$db->insert("INSERT INTO books (author_id, title) VALUES (?,?)", array($auth2+1, 'Little Book of Baz'));
?>
<body>
</body>
</html>