Database

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 http://php.net/manual/en/pdo.drivers.php 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' )
                ->from('Album')
                ->where('artist_id >= :id', array('id' => 1))
                ->orderBy('title')
                ->all();
  // 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()
                ->from('Album')
                ->where('artist_id >= :id', array('id' => 1))
                ->orderBy('title')
                ->all('Album);

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

Where

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')

Limit

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

  ->limit(20)

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()
                ->from('Album')
                ->where('id >= :id', array('id' => 1))
                ->first('Album');

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

Grouping

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')
                ->from('Album')
                ->groupBy('year')
                ->having('COUNT(id)>:count', array('count'=>1))
                ->all();

  // 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.

Inserting

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 )
      ->into('Album')    
      ->exec();

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

Updating

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))
      ->into('Album')
      ->where('id=:id', array('id'=>20))    
      ->exec();

Note: Returns number of rows affected.

Deleting

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
  Db::delete()
      ->from('album')
      ->where('id = :id', array('id' => 35))
      ->exec();

Note: Returns the number of rows affected.

Raw SQL

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..

Query

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

Sql

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

  // raw SQL statement
  $sql = 'CREATE TABLE topic (
          id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(255)
          ) DEFAULT CHARACTER SET utf8;';
  $result = Db::sql( $sql);