My favorites | Sign in
Project Home Downloads Wiki Issues Source
READ-ONLY: This project has been archived. For more information see this post.
Search
for
BasicUsage  
Basic usage
Featured
Updated Aug 12, 2010 by dhaarbrink@gmail.com

Introduction

This first version is very preliminary and supports only a very basic syntax.

The class exposes a Fluent Interface of the following methods: select, from, where, join, limit, group, order. There are a couple of additional methods: create, addSelects, addFroms, setTable and addJoin. Furthermore there are a number of getters, used by the SQLConstructor.

Description of main methods

create

This is a static factory method to create a QueryBuilder instance.

select

This method takes an array or comma separated string of column names to select. The arguments are parsed using getAliasedNames.

from

This method takes an array or comma separated string of tables to select from. The arguments are parsed using getAliasedNames.

where

Specifies the conditions for the sql statement.

join

Specifies join conditions.

limit

This method takes either 1 or 2 arguments (n,m) (of type int). When only 1 argument is given the first n results are returned. When 2 arguments are given the first n results are returned with an offset of m.

group

Specifies group conditions.

order

Specifies sort order.

getAliasedNames

When specifying an array it can be a normal or an associative array. When it is an associative array, the keys are used as aliases for the columns. When specifying a string, aliases can be defined as you would in normal SQL.

Usage Examples

Before we can use the QueryBuilder we'll need to include it of course. Just include or require QueryBuilder.php in your script and you're good to go!

<?php
    require_once './QueryBuilder.php';
    
    // There are two ways you can create a QueryBuilder object
    // The first is to just instantiate it:
    $query_builder = new QueryBuilder();
    // The second is using the factory method `::create()`:
    $query_builder = QueryBuilder::create();
    
    // Both methods produce an equal QueryBuilder instance.
    // The difference being that the latter method can be used to create 
    // a query in just a single line like this:
    $query = (string)QueryBuilder::create()->select('*')->from('table');
    // which results in:
    // SELECT *
    //  FROM table

There are a couple of interesting things going on in that last line of code.

We use the create() method so we can instantly chain the following commands.

The line is also prefixed with a string cast, which triggers to toString() method so the sql query is generated and finally assigned to the variable $query.

Here's a complete example using joins, groups, orders etc..

<?php
    require_once './QueryBuilder.php';
    
    $qb = QueryBuilder::create()
        ->select(array('column1', 'alias' => 'column2'))
        ->from('mytable as mt')
        ->join('another_table at', 'on', 'at.id = mt.fk_at_id')
        ->where('mt.status = 1')
        ->group('mt.group_field')
        ->order('mt.date_field desc')
        ->limit(15); 
    // when printed, gives the following query:
    // SELECT column1,column2 as alias
    //  FROM mytable as mt
    //  JOIN another_table as at on at.id = mt.fk_at_id
    //  WHERE mt.status = 1
    //  GROUP BY mt.group_field
    //  ORDER BY mt.date_field as desc
    //  LIMIT 15
Powered by Google Project Hosting