/QueryBuilder

A dead simple PHP 5 OO interface for building SQL queries. No manual string concatenation necessary.

Primary LanguagePHP

QueryBuilder

A dead simple PHP 5 OO interface for building SQL queries. No manual string concatenation necessary.

Developed by Justin Stayton while at Monk Development.

Requirements

  • PHP 5.1.0 or newer.

Getting Started

To start, make sure to add the class to your autoloader or require it directly:

require "QueryBuilder.php";

Composing a query with QueryBuilder is very similar to writing the SQL by hand, as many of the directives map directly to methods:

$QueryBuilder = new QueryBuilder();
$QueryBuilder->select('*')
             ->from('shows')
             ->innerJoin('episodes', 'show_id')
             ->where('shows.network_id', $networkId)
             ->orderBy('episodes.aired_on', QueryBuilder::ORDER_BY_DESC)
             ->limit(20);

Now that the query is built,

$QueryBuilder->getQueryString();

returns the full SQL query string with placeholders (?), and

$QueryBuilder->getPlaceholderValues();

returns the array of placeholder values that can then be passed to your database connection or abstraction layer of choice. Or, if you'd prefer it all at once, you can get the query string with values already safely quoted:

$QueryBuilder->getQueryString(false);

If you're using PDO, however, QueryBuilder makes executing the query even easier:

$PDOStatement = $QueryBuilder->query();

QueryBuilder works directly with your PDO connection, which can be passed during creation of the QueryBuilder object

$QueryBuilder = new QueryBuilder($PDO);

or after

$QueryBuilder->setPdoConnection($PDO);

Examples

SELECT

SELECT *
FROM shows
INNER JOIN episodes
  ON shows.show_id = episodes.show_id
WHERE shows.network_id = 12
ORDER BY episodes.aired_on DESC
LIMIT 20

As a QueryBuilder:

$QueryBuilder->select('*')
             ->from('shows')
             ->innerJoin('episodes', 'show_id')
             ->where('shows.network_id', $networkId)
             ->orderBy('episodes.aired_on', QueryBuilder::ORDER_BY_DESC)
             ->limit(20);

INSERT

INSERT HIGH_PRIORITY shows
SET network_id = 13,
    name = 'Freaks & Geeks',
    air_day = 'Tuesday'

As a QueryBuilder:

$QueryBuilder->insert('shows')
             ->option('HIGH_PRIORITY')
             ->set('network_id', 13)
             ->set('name', 'Freaks & Geeks')
             ->set('air_day', 'Tuesday');

REPLACE

REPLACE shows
SET network_id = 13,
    name = 'Freaks & Geeks',
    air_day = 'Monday'

As a QueryBuilder:

$QueryBuilder->replace('shows')
             ->set('network_id', 13)
             ->set('name', 'Freaks & Geeks')
             ->set('air_day', 'Monday');

UPDATE

UPDATE episodes
SET aired_on = '2012-06-25'
WHERE show_id = 12
  OR (name = 'Girlfriends and Boyfriends'
        AND air_day != 'Monday')

As a QueryBuilder:

$QueryBuilder->update('episodes')
             ->set('aired_on', '2012-06-25')
             ->where('show_id', 12)
             ->openWhere(QueryBuilder::LOGICAL_OR)
             ->where('name', 'Girlfriends and Boyfriends')
             ->where('air_day', 'Monday', QueryBuilder::NOT_EQUALS)
             ->closeWhere();

DELETE

DELETE
FROM shows
WHERE show_id IN (12, 15, 20)
LIMIT 3

As a QueryBuilder:

$QueryBuilder->delete()
             ->from('shows')
             ->whereIn('show_id', array(12, 15, 20))
             ->limit(3);

Methods

SELECT

INSERT

REPLACE

UPDATE

DELETE

OPTIONS

SET

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

Query

Utility

Feedback

Please open an issue to request a feature or submit a bug report. Or even if you just want to provide some feedback, I'd love to hear. I'm also available on Twitter as @jstayton.