Database Query Builder

Use the query builder methods to make database agnostic queries and data manipulation.

First build your query, like:

//if your class extended from Model, $this->db is already declared
//so this row isn't necessary
$this->db = new Database();
 
//build the query:
$this->db->from('users');
$this->db->select('username');
$this->db->where('id',1);
//this will build the SQL: 'SELECT username FROM users WHERE id = 1'

Then execute the query with the get(), insert(), update(), delete() method:

$result = $this->db->get();
//$result the same as if you had written:
$result = $this->db->query('SELECT username FROM users WHERE id = 1');

All query builder methods return the database class, so they can be chained like this:

$result = $this->db->from('users')->select('username')->where('id',1)->get();

While the query builder portion of the database library is very powerful and easy to use, it does have some inherent limitations. This is mainly due to the fact it escapes everything you give it. Therefore the following things won't work as expected:

1. Database arithmetic: $db→update('pages', array('views' ⇒ 'views+1')). This will get escaped and not work as expected.
2. Parenthesis support: queries like SELECT * FROM pages WHERE (id = 5) OR (title = 'BLAH' AND id = 15) won't work properly, because the query builder doesn't yet have the capability to add parentheses. This is a planned addition in a future release.
3. Any database specific functions: things like MySQL's NOW() function will get escaped and not work properly. In future releases there may be an option to turn off automatic query escaping, which will solve this problem. The are a couple reasons we won't support this right now:

• The query builder is supposed to provide server agnostic database access. Putting DB specific functions in your query builder statement completely destroys this.
• There are entirely too many functions to support for every database server, and check every call against all these possible functions. We focus on speed, sometimes at the cost of functionality.
• Quoting is necessary to allow maximum flexibility for table/column names

The select() method set the table column names you want to SELECT with your query.

$db = new Database();
$db->select('id, title');
 
//OR
 
$db->select(array('id', 'title'));

This will set the SELECT part of your query to `id`, `title`. Note that the query builder automatically escapes column names for you, and if you leave this method out, your query will automatically be set to SELECT *. You can also specify cross table columns:

$db->select('users.id, pages.title');

And it will generate SELECT `users`.`id`, `pages`.`title`.

You can also specify column aliases:

$db->select('id as page_id', 'title as page_title');

Will generate SELECT `id` AS `page_id`, `title` AS `page_title`.

For getting DISTINCT data:

$db->select('DISTINCT user')

Will generate SELECT DISTINCT `user`.

The from() method sets the table(s) you want to SELECT from. You can use an array or comma separated list of table names.

$db->from('pages');
 
// OR
 
$db->from(array('pages', 'users'));

This will produce FROM `pages`, `users`. Note that the query builder automatically escapes your input.

You can also specify table aliases like with select() above. This way you can write less:

$db->from('pages as p', 'users as u')->select('u.id', 'p.title');

The where() method sets the where conditions of your query. You can pass it a key string along with a value string, or an array containing multiple key/value pairs. It will join them with AND

$db->where('id', 5);
$db->where(array('id' => 5));
$db->where(array('id' => 5, 'title' => 'Demo'));

The last line will generate WHERE `id` = 5 AND `title` = 'Demo'.

$db->where(array('id !=' => $some_id));

$db->where(array('id >=' => $some_id));
$db->where(array('id <' => $some_id));
// ... etc

$db->where('colname IS NULL');
// OR...
$db->where(array('colname' => null));

Identical to the above method, except it joins multiple parts with OR.

$db->orwhere(array('id' => 5, 'title' => 'Demo'));

The last line will generate WHERE `id` = 5 OR `title` = 'Demo'.

Identical to where(), except it makes a LIKE string:

$db->like('title', 'Demo');

This generates WHERE `title` LIKE '%Demo%'; You can manually specify %'s to do the query you want. For this don't forget to specify FALSE as a third parameter:

$db->like('title', '%Demo', FALSE);
$db->like('title', 'Demo%', FALSE);

You can also pass an array containing multiple key/value pairs. It will join them with AND:

$db->like(array('title' => 'Demo', 'subtitle' => 'Start'));

This generates WHERE `title` LIKE '%Demo%' AND `subtitle` LIKE '%Start%'.

Identical to like(), except it joins multiple parts with OR:

$db->orlike(array('title' => 'Demo', 'subtitle' => 'Start'));

This generates “WHERE `title` LIKE ”%Demo%” OR `subtitle` LIKE ”%Start%””.

Identical to like(), except it generates a NOT LIKE clause.

$db->notlike('title', 'Demo'); // generates "WHERE `title` NOT LIKE "%Demo%""
$db->notlike(array('title' => 'Demo', 'subtitle' => 'Start')); // generates "WHERE `title` NOT LIKE "%Demo%" AND `subtitle` NOT LIKE "%Start%""

Identical to like(), except it generates an OR NOT LIKE clause.

$db->ornotlike(array('title' => 'Demo', 'subtitle' => 'Start')); // generates "WHERE `title` NOT LIKE "%Demo%" OR `subtitle` NOT LIKE "%Start%""

Creates an IN portion of a query. It has three parameters:

1. the column to match
2. an array or string of values to match against
3. (boolean), to create a NOT clause instead

$db->in('title', array(1,2,3,4,5));

This generates “`title` IN ('1','2','3','4','5')”

Identical to in(), except it generates a NOT IN clause. You can either use this method (two parameters), or in() with the third parameter as TRUE.

$db->notin('title', array(1,2,3,4,5));
// or
$db->in('title', array(1,2,3,4,5), TRUE);

This generates “`title` NOT IN ('1','2','3','4','5')”

This method allows you to search based on a regular expression. It's syntax is identical to like().

$db->regex('title', 'Demo|Sample');
$db->regex(array('title' => 'Demo|Sample'));

Identical to regex() except it joins multiple statements with OR.

Identical to regex() expect it generates a NOT REGEX clause.

Identical to regex() except it generates an OR NOT REGEX clause.

Sets the GROUP BY part of a query. You can pass it a string, or an array.

$db->groupby('title');
$db->groupby(array('title', 'id'));

Generates a HAVING clause for a query. Syntax is similar to like().

$db->having('title', 'Demo');

Identical to having(), except it puts an OR in between multiples.

Executes the current query builder statement. This methods has 3 parameters:

1. the table to use
2. the limit to use
3. the offset to use if the limit is set

This returns a Database Result object that you can then work on the results of your query with.

$query = $db->from('pages')->where(array('id >=' => 5))->get();
 
// This is the same as
$query = $db->where(array('id >=' => 5))->get('pages');

Same as above, except the parameters are as follows:

1. the table to use
2. the where clause to use
3. the limit to use
4. the offset to use if the limit is set

So our query from above can be simplified into:

$query = $db->getwhere('pages', array('id >=' => 5));

Creates a SET portion of a query for your inserts. You can pass it either one parameter or two parameters:

$db->set('column', 'value');
 
// OR
 
$db->set(array('column' => 'value'));

Performs a merge query. The behavior of this method will change depending on which backend database you are using. In MySQL, for example, the database will search for an existing key, delete that row if it exists, then insert a new row. The syntax is similar to update.

Performs a database insert query. This method has 2 parameters:

1. The table to do the insert on
2. An associative array of columns and their values

$status = $db->insert('pages', array('title' => 'My new title'));
 
// count how many rows were inserted
$rows = count($status);

Returns a query result (the same object you get back from Database::query()).

You can also omit any parameter if you have set it with another query builder method:

$db->from('pages')->set(array('title' => 'My new title'))->insert();

Performs a database update query. This method has 3 parameters:

1. The table to do the update on
2. An associative array of columns and their new values
3. The where clause to update on. This can either be a string or an associative array. See the where() method for details.

$status = $db->update('pages', array('title' => 'My new title'), array('id' => 5));
 
// count how many rows were updated
$rows = count($status);

You can also omit any parameter if you have set it with another query builder method:

$db->from('pages')->set(array('title' => 'My new title'))->where(array('id' => 5))->update();

Performs a database delete query. This method has 2 parameters:

1. The table to do the delete on
2. The where clause to delete on. This can either be a string or an associative array. See the where() method for details.

$status = $db->delete('pages', array('id' => 5));
 
// count how many rows were deleted
$rows = count($status);

You can also set the table and where portions with query builder methods as described in insert() and update().

Creates the offset portion of a query. This causes your result set to start at a different starting point.

// Start the results at position 10
$db->offset(10);

Creates the limit section of a query. The first parameter is the number of results you want your query to limit to.

You can also set the offset with this method as well with the second parameter.

// Limit the query to 15 results
$db->limit(15);
 
// OR
 
// Limit the query to 15 results starting at position 10
$db->limit(15, 10);

Specifies the return order of your query. Has two parameters:

1. The column(s) to order by (use an array for multiple columns)
2. The direction to order the column by (defaults to ASC)

$db->orderby('title', 'ASC');

You can also return randomly ordered results in MySql for example using the following arguments.

$db->orderby(NULL, 'RAND()');

The orderby() method also supports ordering by multiple columns if you use an associative array as the argument.

$db->orderby( array( 'id' => 'ASC', 'date_created' => 'ASC') );

Joins two tables together.

1. string - table name to join
2. string/array - where key or array of key ⇒ value pairs
3. string - where value
4. string - type of join - LEFT, RIGHT, OUTER, INNER, LEFT OUTER, RIGHT OUTER

// Run query against the (user_roles) table.
$db->from('users_roles');
 
// Join the roles with (user_roles)
$db->join('roles', 'roles.id', 'users_roles.role_id');
 
// Execute the query.
$db->get();

The same join using array syntax:

$this->db->join('roles', array('roles.id' => 'users_roles.role_id'));

Count query records.

1. string - table name
2. array - where clause

// Count all the users
$count = $db->count_records('users');
 
// Count banned users
$banned_users_count = $db->count_records('users', array('banned' => 1));
 
// Or like this
$banned_users_count = $db->where('banned', 1)->count_records('users');

Continue to the next section: Database Query Result >>