A dead simple PHP 5 OO interface for building SQL queries. No manual string concatenation necessary.
Developed by Justin Stayton while at Monk Development.
- PHP 5.1.0 or newer.
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);
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 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 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 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
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);
- from
- innerJoin
- leftJoin
- rightJoin
- join
- getFrom
- getFromAlias
- getFromString
- getJoinString
- mergeFromInto
- mergeJoinInto
- where
- andWhere
- orWhere
- whereIn
- whereNotIn
- whereBetween
- whereNotBetween
- openWhere
- closeWhere
- getWherePlaceholderValues
- getWhereString
- mergeWhereInto
- having
- andHaving
- orHaving
- havingIn
- havingNotIn
- havingBetween
- havingNotBetween
- openHaving
- closeHaving
- getHavingPlaceholderValues
- getHavingString
- mergeHavingInto
- query
- getQueryString
- getPlaceholderValues
- isSelect
- isInsert
- isReplace
- isUpdate
- isDelete
- __toString
- mergeInto
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.