PHP::SQL

Features

• Driver selection and database connection is done through a database url (driver://user:password@host:port/dbname),
• SQL errors are thrown as exceptions,
• SQL results are accessed using an Iterator implementation and can be iterated using a foreach loop,
• ability to create fetch factories and make result sets returns instances of a custom class instead of the stdclass,
• SQL Logger interface for debug.

Download

Download the Sql package

Use pear to install it.

pear install http://tech.motion-twin.com/zip/Sql-1.0.3.tar.gz

Documentation

• Example 1 : simple usage
• Example 2 : fetching array
• Example 3 : fetching hash
• Example 4 : custom class instantiation
• Example 5 : fetch factory
• Example 6 : logger interface

Example 1 : simple usage

The default fetch behaviour is to return StdClass instances.

require_once 'Sql.php';

try 
{
    $driver = Sql::GetDriver('mysql://user:pass@localhost/dbname');
    $result = $driver->execute('SELECT Foo.name, Foo.x FROM Foo');
    echo $result->length(), " rows to fetch\n";
    foreach ($result as $i => $foo)
    {
        echo $i, ': ', $foo->name, '=', $foo->x, "\n";
    }
}
catch (Exception $e)
{
    echo $e;
}

Example 2 : fetching array

try 
{
    $driver = Sql::GetDriver('mysql://user:pass@localhost/dbname');
    $result = $driver->execute('SELECT Foo.name, Foo.x FROM Foo');
    
    $result->setFetchType(Sql_ResultSet::FetchArray);
    
    echo $result->length(), " rows to fetch\n";
    foreach ($result as $i => $foo)
    {
        echo $i, ': ', $foo[0], '=', $foo[1], "\n";
    }
}
catch (Exception $e)
{
    echo $e;
}

Example 3: feching hash

try 
{
    $driver = Sql::GetDriver('mysql://user:pass@localhost/dbname');
    $result = $driver->execute('SELECT Foo.name, Foo.x FROM Foo');
    
    $result->setFetchType(Sql_ResultSet::FetchHash);
    
    echo $result->length(), " rows to fetch\n";
    foreach ($result as $i => $foo)
    {
        echo $i, ': ', $foo['name'], '=', $foo['x'], "\n";
    }
}
catch (Exception $e)
{
    echo $e;
}  

Example 4: custom class instantiation

A very basic usage of the Sql library, the Sql_ResultSet will create an instance of specified class and will map each database field to your class public attributes.

class MyFoo 
{
    public $name;
    public $x;

    function __toString()
    {
        return "$this->name=$this->x";
    }
}

require_once 'Sql.php';
try 
{
    $driver = Sql::GetDriver('mysql://user:pass@localhost/dbname');
    $result = $driver->execute('SELECT Foo.name, Foo.x FROM Foo');
    
    $result->setFetchInstance('MyFoo');

    echo $result->length(), " rows to fetch\n";
    foreach ($result as $i => $foo)
    {
        echo $i, ': ', $foo, "\n";
    }
}
catch (Exception $e)
{
    echo $e;
}

Example 5: fetch factory

Using a fetch factory allows you to add additional behaviour like instantiating different classes depending on some database field value...

class MyFoo 
{
    private $_name;
    private $_x;

    function __construct($name, $x)
    {
        $this->_name = $name;
        $this->_x = $x;
    }

    function __toString()
    {
        return "$this->_name=$this->_x";
    }
}

class MyFooFactory 
{
    public function createObject($hash)
    {
        return new MyFoo($hash['name'], $hash['x']);
    }
}

require_once 'Sql.php';
try 
{
    $driver = Sql::GetDriver('mysql://user:pass@localhost/dbname');
    $result = $driver->execute('SELECT Foo.name, Foo.x FROM Foo');

    $factory = new MyFooFactory();
    $result->setFetchFactory($factory);
    
    echo $result->length(), " rows to fetch\n";
    foreach ($result as $i => $foo)
    {
        echo $i, ': ', $foo, "\n";
    }
}
catch (Exception $e)
{
    echo $e;
}

Example 6: logger interface

For debug purpose, the Sql_Logger interface only requires a debug($str) method to be implemented.

class MyLogger 
{
    public function debug($msg)
    {
        echo $msg, "\n";
    }
}

$driver->setLogger(new MyLogger());