Introduction

The Query Builder component is an excellent tool that will help you create and execute sql queries in a unique fashion way, by taking advantage of the new features that php 5.3 has to offer. This tool uses the Pdo library to execute queries, and adds place holders for the values automatically, making the task of running queries a lot safer. The component can also be used to prepare statements and execute them multiple times. All queries can be logged with the PtcDebug class. This class can also be used with the PtcEvent component to fire the "ptc.query" event, when queries are executed .

Main Features:

Simple and intuitive sintax for building queries
Prepared sql statements for multiple execution
Can handle sql joins in a very simple way
Uses The PtcDebug class to log sql queries and execution timing
Can be used with the PtcEvent component to observe executed queries
Protects against sql injection by adding place holders
Can be used with the PtcDb component to handle multiple instances
Adds backticks to the query automatically

Getting Started

The following example shows how we can initialize the Query Builder with Pdo support to execute queries:

// INITIALIZING A PDO OBJECT TO RUN QUERIES WITH THE QUERY BUILDER
$pdo_connection =  'mysql:host=localhost;dbname=some dbname;charset:uft8;';
$pdo = new PDO( $pdo_connection , 'some user' , 'some pass' );
// require the class
require_once( 'PtcQueryBuilder.php' );    
// initializing the query builder with pdo support
$qb = new PtcQueryBuilder( $pdo );    
// retrieve all records from given table
$qb->table( 'some_table' )->run( );               

The class can also be initializited without Pdo support, if you just want to prepare queries and run them some other way. In this case the method PtcQueryBuilder::prepare( ) should be used:

// including the query builder component
require_once( 'PtcQueryBuilder.php' );    
// initializing the class
$qb = new PtcQueryBuilder( );                 
// preparing a query
$query = $qb->table( 'test_table' )->prepare( );      

Selecting Data

The following sections, will demonstrate how to select data with the help of the component:

Retrieving Records From A Table

The method PtcQueryBuilder::run( ) can be used to retrieve records from a given table:

// retrieve all records
$qb->table( 'some_table' )->run( );
// retrieve records that match a statement
$qb->table( 'some_table' )
  ->where( 'some_field' , '=' , 'some value'  )
  ->run( );

Retrieving 1 Record From A Table

Use the method PtcQueryBuilder::row( ) to retrieve 1 single record from a given table:

$qb->table( 'some_table' )
  ->where( 'some_field' , '=' , 'some value' )
  ->row( );

We can also retrieve 1 single record by id, with the method PtcQueryBuilder::find( ) :

// retrieve record based on id

$qb->table( 'some_table' )->find( 2 );

Retrieving 1 Column From A Record

To retrieve 1 column from a record, specify the "$column" parameter for the PtcQueryBuilder::row( ) method:

$qb->table( 'some_table' )
  ->where( 'some_field' , '=' , 'some value' )
  ->row( 'some_column' );

Note: When using the "$column" parameter, any column added with the PtcQueryBuilder::select( ) method will be discarded.

Specifying Columns To Be Selected

To specify column names the method PtcQueryBuilder::select( ) can be used:

// Specifying 1 column to be selected
$qb->table( 'test_table' )
  ->select( 'some_column' )
  ->run( );
// Specifying multiple columns to be selected
$qb->table( 'test_table' )
  ->select( array( 'some_column' , 'some_other_column as i' ) )
  ->run( );

Note: If you are aliasing column names, always use "as" otherwise backticks will not be added properly.

Adding Multiple Tables

To add multiple tables to the query, use an array with the PtcQueryBuilder::table( ) method:

// adding multiple tables
$qb->table( array( 'some_table' , 'some_other_table as i' ) )
  ->run( );

Note: If you are aliasing table names, always use "as" otherwise backticks will not be added properly.

Where Operators

Where operators can be added to the query with the PtcQueryBuilder::where( ) method:

$qb->table( 'some_table' )
  ->where( 'some_field' , '=' , 'some value'  )
  ->where( 'some_other_field' , '=' , 'some other value'  ) // and
  ->run( );

All possible options for the "$operator" parameter are stored in the PtcQueryBuilder::$_operators property:

$_operators = array( '=' , '<' , '>' , '<=' , '>=' , '<>' , '!=' , 'like' , 'not like' , 'between' , 'ilike' );

The following sections will explain how to use the PtcQueryBuilder::where( ) method to add where operators in the query.

Where Or

To add "OR" to the where clause use "or_" like this:

$qb->table( 'some_table' )
    ->where( 'some_field' , '=' , 'some value'  )
    ->or_where( 'some_other_field' , '=' , 'some other value'  ) // or
    ->run( );

Where In

This is how "where in" operators can be added to the query:

$qb->table( 'test_table' )
    ->where_in( 'field3' , array( 1 , 2 ) )
    ->or_where_in( 'column' , array( 3 , 4 ) ) // or in
    ->run( );
// not in
$qb->table( 'test_table' )
    ->where_not_in( 'column' , array( 3 , 4 ) ) 
    ->run( );

Where Between

Adding a "where between" operator can be achieved this way:

$qb->table( 'test_table' )
    ->where_between( 'field3' , ':value1' , ':value2' )
    ->or_where_between( 'field3' , ':value1' , ':value2' ) // or between
    ->run( );
// not between
$qb->table( 'test_table' )  
    ->where_not_between(  'field3' , ':value1' , ':value2' )
    ->run( );

Using Closures With Where

For more complex where operators closures can be used. Take a look at the following example to get an idea of how this works:

$qb->table( 'test_table' )
    ->where( 'field1' , '!=' , 'some value' )
    ->where( function( $query ) // will generate AND ( field3 > 10 OR field3 < 100 )
    {
        $query->where( 'field3', '>' , 10 )
            ->or_where( 'field3' , '<' , 100 );
    } )->run( );

Joins

To create join statements the method PtcQueryBuilder::join( ) can be used. The following paragraphs will demonstrate how to use this method:

Simple Join

Simple joins can be created like shown in the following example:

$qb->table( 'test_table' )
  ->join( 'test_table1' , 'test_table.id' , '=' ,  'test_table1.id' )
  ->run( );

To specify the type of join, use "_" uderscore like in the following example:

$qb->table( 'test_table' )
  ->left_join( 'test_table1' , 'test_table.id' , '=' ,  'test_table1.id' ) // Specifying a left join
  ->run( );

Using Closures With Joins

To create more complex joins, closures with the method PtcQueryBuilder::on( ) can be used:

$qb->table( 'test_table' )
  ->left_join( 'test_table1' , function( $join )
  {
      $join->on(  'test_table.id' , '=' ,  'test_table1.id' )
          ->or_on(  'test_table.id' , '=' ,  'test_table1.id' ) // or on
  } )->run( );

Group, Order And Limit

To group the query results the method PtcQueryBuilder::group( ) can be used:

$qb->table( 'test_table' )->group( 'some_field' )->run( );

To order the query results the method PtcQueryBuilder::order( ) can be used:

$qb->table( 'test_table' )->order( 'some_field' , 'desc' )->run( );

To limit results the method PtcQueryBuilder::limit( ) can be used:

$qb->table( 'test_table' )->limit( 10 )->run( );
$qb->table( 'test_table' )->limit( 1 , 100 )->run( ); // limit with start and end

Manipulating Data

The following section will demonstrate how data can be manipulated with the query builder class:

Insert Recods

To insert new records, the method PtcQueryBuilder::insert( ) can be used:

$qb->table( 'test_table1' )
  ->insert( array( 'field4' => 'somevalue' ) ) 
  ->run( );

Update Records

To update records the method PtcQueryBuilder::update( ) can be used:

$qb->table( 'test_table' )
    ->where( 'some_column' , '!=' , 'some value' )
    ->update( array( 'column1' => 'some value' , 'column2' => 'some other value' ) )
    ->run( );

This method also accepts an "id" as second parameter instead of a where operator:

$qb->table( 'test_table' )
  ->update( array( 'column1' => 'some value' , 'column2' => 'some other value' ) , 2 ) // update record based on id
  ->run( );

Delete Records

To delete records the method PtcQueryBuilder::delete( ) can be used:

$qb->table( 'test_table' )
   ->where( 'some_column' , '=' , 'some value' )
  ->delete( )
  ->run( );

The "$id" parameter can replace the where operator:

// delete record based on id

$qb->table( 'test_table' )->delete( 2 )->run( );

Last Inserted Id

The method PtcQueryBuilder::lastId( ) can be used to retrieve the last inserted id:

// get the last id

$last_insert_id = $qb->lastId( );

Counting Rows

The method PtcQueryBuilder::countRows( ) can be used to see the number of affected rows by the last query:

// deleting some data
$qb->table( 'test_table' )
  ->where( 'some_column' , '=' , 'some value' )
  ->delete( )
  ->run( );
//counting affected rows by previous delete
$affected_rows = $qb->countRows(  );

Note: The number of affected rows is automatically returned for update,insert and delete quieries.

Raw Statements

Raw statements can also be added if you wish, using the method PtcQueryBuilder::rawSelect( ), like in the next example:

$qb->table( 'some_table' )
  ->where( 'some_field' , '!=' , 'some value' )
  ->rawSelect( ' ORDER BY some_field , DESC' ) // just an example
  ->run( );

Whatever you add with this method, will be attached to the end of the query, but before you add anything else.

Note: This method does not offer any protection against sql injection, it is adviced to use the PtcQueryBuilder::raw( ) method instead.

Raw Values

To add raw values to the query, use PtcQueryBuilder::raw( ) method:

$qb->table( 'test_table' )
  ->where( 'field1' , '!=' , $qb->raw( 'NOW()' ) )
  ->run( );

Note: The PtcQueryBuilder::raw( ) method can be passed to most of the component methods as argument, but should be used with caution, as it does not prevent against sql injection.

Preparing Queries

The query builder offers the possibility to prepare queries and store the query string in a variable for later usage:

// preparing place holders for an insert
$fields = array( 'some_column' => ':value1' , 'some_other_column' => ':value2' ); 
// storing the query in a variable for later usage
$query = $qb->table( 'test_table' )->insert( $fields )->prepare( );
// executing the query multiple times with the run method
for ( $i = 0; $i < 10; $i++ )
{
  // binding values to the place holders
  $fields = array( ':value1' => 'some value ' . $i , ':value2' => 'some other value' . $i );
  
  // using the "$query" and the "$bind" parameters with the run( ) method
  $qb->run( $query , $fields );
}

Note: The PtcQueryBuilder::prepare( ) method can only be used with "place holders", refer to the Pdo manual for understanding how "place holders" work.

Setting The Fetch Mode

To set the fetch mode of the Pdo object for the current query, use the method PtcQueryBuilder::setFetchMode( ).

// will set the Pdo fetch mode for the next query
$qb->setFetchMode( PDO::FETCH_ASSOC );
// this query will return an array
$result = $qb->table( 'test_table' )->limit( 10 )->run( );

Specifying The Return Type

When using the "$query" parameter with the method PtcQueryBuilder::run( ), the class tries to guess if the query passed is a select, if so it will return a result. Otherwise it will simply return the number of affected rows.

To control this behaviour the parameter "$type" can be used:

// force to return a result

$db->run( 'SHOW COLUMNS FROM `some_table`' , '' , 1 );

These are all possible values for the "$type" parameter:

1 Returns result if any
2 Returns only 1 row
3 Returns number of affected rows

Observer Events

This class can be used with the PtcEvent component to fire an event for every executed query:

// require the PtcEvent component
require_once( 'PtcEvent.php' );
// adding a listener to the "ptc.query" event
PtcEvent::listen( 'ptc.query' , function( $result , $query , $bindings )
{ 
  // we can log all queries here
} );
// running any query will trigger our listener
$qb->table( 'test_table' )->run( );

Note: Refer to the Event Dispatcher Component (PtcEvent) user manual, for detailed usage over event listeners.

Table of Contents