PIP uses PDO to connect to a database and retreive, create, update and delete data.

PIP allows you to build SQL queries but does not provide any ORM facilities. If you need something more sophisticated take a look at Laravel's Eloquent, FluentPDO or one of the many other ORM tools available. See Models for more information.

Connecting to database

In the configuration file (application/config/config.php) enter your database connection information - and you re ready to go.

Note: see for driver information.

// Database configuration
$config['database'] = array(
    'driver'   => 'mysql',          // e.g. sqlite, sybase 
    'host'     => 'host',           // e.g. localhost
    'database' => 'database_name',
    'username' => 'username',
    'password' => 'password',
    'charset'  => 'utf8',

Running Queries

The PIP Model is based on the following PDO interface. Each query starts with a select statement which clears any previous settings.

PIP uses standard SQL syntax for a select statement and breaks the statement down into sections:

select( string ) column names (default * (all))
from( string ) table name
where( string, array() ) Where clause, data()
groupBy( string ) Group by clause
orderBy( string ) Order by clause
having( string ) Having clause
limit( int ) apply Limit
all( string ) fetch all matching records (into class)
first( string ) fetch first matching record (into class)
count() count number of rows

Note: Queries can return arrays or class objects (default is arrays).. See all() and first() for more information.

The Select statement

The Db::select() statement builds a select string. Pass a string of column names, or omit for '*' (all) columns.

Call all() (with no paramenter) to return the results as an array.

  // SELECT title, year FROM album WHERE artist_id = 1 ORDER By title
  $record = Db::select('title, year' )
                ->where('artist_id >= :id', array('id' => 1))
  // process array 
  foreach( $record as $row ) {
      var_dump( $row['title'] );

Returning data as objects

Call all('class') (with the class-name) to return the results as a class:

  $record = Db::select()
                ->where('artist_id >= :id', array('id' => 1))

  foreach( $record as $row ) {
      var_dump( $row->title . ' by ' . $row->getArtist() );


To select specific records call where($where, $bind) to pass a WHERE clause and an array of bind data.

  ->where('year=:year', array('year'=>2000))

Note: Do not include a colon (:) in the bind data array (ie. 'year' not ':year')

Order By

To select specific records call orderBy($order) to pass an ORDER BY clause.

  ->orderBy('title DESC')


To restrict the number of rows returned use the limit(20) function.


Retrieving one record

To retreive only the first matching record use first() after calling select(), from() and where() ...

  // SELECT * FROM album WHERE id = 1   (as class object)
  $record = Db::->select()
                ->where('id >= :id', array('id' => 1))

Pass a model class-name to retreive as object first('Album')


To group records together use the groupBy() and having() functions.
  // SELECT year, COUNT(id) AS releases FROM album
  // GROUP BY year HAVING COUNT(id) >1
  $results = Db::select( 'year, COUNT(id) AS releases')
                ->having('COUNT(id)>:count', array('count'=>1))

  // process array
  foreach( $results as $row ) {
      var_dump( $row['year'] . ' - ' . $row['releases'] );

NOTE: to use WHERE and HAVING together put the bind data for both clauses in the later function.


To insert a new record call Db::insert() passing an array of field-names and values. Use into() to provide the table name, and exec() to complete.

  // INSERT ( ... ) VALUES ( ... ) INTO album
  // will return ID of new record or FALSE
  $data = array(
            'title' => 'Pet Sounds',
            'year'  => '1968',
            'artist_id' => 7

  // insert onto database
  Db::insert( $data )

Note: Returns the ID of the inserted record (or FALSE if failed).


To update one or more records call Db::update() passing an array of field-names and values. To insert into a table other than the current model pass a second parameter of table-name.

  // UPDATE album SET ... WHERE ID=20
  DB::update( array('title'=>'new title', 'year'=>1969))
      ->where('id=:id', array('id'=>20))    

Note: Returns number of rows affected.


To delete one or more records call Db::delete() passing an array of field-names and values. To insert into a table other than the current model pass a second parameter of table-name.

  // Delete a record
      ->where('id = :id', array('id' => 35))

Note: Returns the number of rows affected.


There are two methods for executing raw SQL - query() and sql(). Query returns a set of data (using PDO Fetchall) and sql executes the statement and returns true (if successful) or false..


  // raw SQL select statement
  $sql = 'SELECT * FROM table WHERE age>=21 AND age <50 ORDER BY age';
  $data = Db::query( $sql);


To run a raw SQL statement just call sql() passing the SQL statement.

  // raw SQL statement
  $sql = 'CREATE TABLE topic (
          name VARCHAR(255)
          ) DEFAULT CHARACTER SET utf8;';
  $result = Db::sql( $sql);