Wiggum
a php framework

Database: Where Clauses

Simple Where Clauses

You may use the where method on a query builder instance to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.

For example, here is a query that verifies the value of the "votes" column is equal to 100:

$users = $this->db->table('users')->where('votes', '=', 100)->fetchRow();

For convenience, if you simply want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method:

$users = $this->db->table('users')->where('votes', '=', 100)->fetchRow();

Of course, you may use a variety of other operators when writing a where clause:

$users = $this->db->table('users')->where('votes', '>=', 100)->fetchRow();
							
$users = $this->db->table('users')->where('votes', '<>', 100)->fetchRow();
		
$users = $this->db->table('users')->where('name', 'like', 'T%')->fetchRow();

Or Statements

You may chain where constraints together as well as add or clauses to the query. The orWhere method accepts the same arguments as the where method:

$users = $this->db->table('users')
	->where('votes', '>', 100)
	->orWhere('name', '=', 'John')
	->fetchRow();

Use an Array

You may also pass an array of conditions to the where function:

$users = $this->db->table('users')->where([
	    'status' => '1',
	    'subscribed' => '1'
	])->fetchRow();
select * from `users` where `status` = 1 and `subscribed` = 1

Grouping

Sometimes you may need to create more advanced where clauses such as nested parameter groupings. The query builder can handle this as well. To get started, let's look at an example of grouping constraints within parenthesis:

$this->db->table('users')
	->where('name', '=', 'John')
	->orWhere(function ($query) {
		$query->where('votes', '>', 100)
			  ->where('title', '<>', 'Admin');
		})
	->fetchRow();

As you can see, passing a Closure into the where method instructs the query builder to begin a constraint group. The Closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

Additional Where Clauses

Where Between

The whereBetween and orWhereBetween method verifies that a column's value is between two values:

$users = $this->db->table('users')
	->whereBetween('votes', [1, 100])
	->fetchRow();
Where Not Between

The whereNotBetween and orWhereNotBetween method verifies that a column's value lies outside of two values:

$users = $this->db->table('users')
	->whereNotBetween('votes', [1, 100])
	->fetchRow();
Where In

The whereIn and orWhereIn method verifies that a given column's value is contained within the given array:

$users = $this->db->table('users')
	->whereIn('id', [1, 2, 3])
	->fetchRow();
Where Not In

The whereNotIn and orWhereNotIn method verifies that the given column's value is not contained in the given array:

$users = $this->db->table('users')
	->whereNotIn('id', [1, 2, 3])
	->fetchRow();
Where Null

The whereNull and orWhereNull method verifies that the value of the given column is NULL:

$users = $this->db->table('users')
	->whereNull('updated_at')
	->fetchRow();
Where Not Null

The whereNotNull and orWhereNotNull method verifies that the column's value is not NULL:

$users = $this->db->table('users')
	->whereNotNull('updated_at')
	->fetchRow();
Where Date

The whereDate method may be used to compare a column's value against a date:

$users = $this->db->table('users')
    ->whereDate('created_at',  '=','2016-12-31')
    ->fetchRow();
Where Month

The whereMonth method may be used to compare a column's value against a specific month of a year:

$users = $this->db->table('users')
    ->whereMonth('created_at',  '=','12')
    ->fetchRow();
Where Day

The whereDay method may be used to compare a column's value against a specific day of a month:

$users = $this->db->table('users')
    ->whereDay('created_at',  '=','31')
    ->fetchRow();
Where Year

The whereYear method may be used to compare a column's value against a specific year:

$users = $this->db->table('users')
    ->whereYear('created_at', '=', '2016')
    ->fetchRow();
Where Time

The whereTime method may be used to compare a column's value against a specific time:

$users = $this->db->table('users')
    ->whereTime('created_at', '=', '11:20:45')
    ->fetchRow();
Where Column

Coming Soon

Where JSON

Coming Soon