How to use WHERE clauses in Codeigniter

CodeIgniter is a framework based on MVC principles. As a result, you would usually separate application logic, data abstraction and “output” into their respective areas for CodeIgniter use. In this case: controllers, models and views.

When we want to restrict the query results to a specified condition. The SQL WHERE clause comes in handy in such situations.

Codeiginter provides WHERE clauses using one of four methods.

Note: All values passed to this function are escaped automatically, producing safer queries.

Simple key/value method

$this->db->where('username', $username); // Query: WHERE username= 'alex'

If you use multiple function calls they will be chained together with AND between them.

$this->db->where('username', $username);
$this->db->where('password', $password);
$this->db->where('status', $status);
// WHERE username = 'alex' AND password = '123456' AND status = '1'

Custom key/value method

Include an operator in WHERE clauses

$this->db->where('username !=', $username);
$this->db->where('amount >=', $amount);
$this->db->where('amount <=', $amount);
$this->db->where('id <', $id); 

Associative array method

$array = array('name' => $name, 'title' => $title, 'status' => $status);
// Produces: WHERE name = 'alex' AND title = 'php' AND status = '1'

You can include your own operators using this method as well

$array = array('name !=' => $name, 'id >' => $id, 'date <' => $date);

Custom string

$where = "name='alex' AND title='php' OR status='1'";

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names.

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

Leave a Reply

Your email address will not be published. Required fields are marked *