CodeIgniter | Query Builder Class

CodeIgniter gives you access to a Query Builder class.

Query builder class allow data to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action.

Selecting Data:

The following functions allow you to build SQL SELECT statements.

$this->db->get()

Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:

$query = $this->db->get('tablename');  // Produces: SELECT * FROM tablename

The second and third parameters enable you to set a limit and offset clause:

$query = $this->db->get('tablename', 10, 20);

// Executes: SELECT * FROM tablename LIMIT 20, 10
// (in MySQL. Other databases have slightly different syntax)

Select Functions:

$this->db->select_max()

Writes a SELECT MAX(field) portion for your query. You can optionally include a second parameter to rename the resulting field.

$this->db->select_max('age');
$query = $this->db->get('members');  // Produces: SELECT MAX(age) as age FROM members

$this->db->select_max('age', 'member_age');
$query = $this->db->get('members'); // Produces: SELECT MAX(age) as member_age FROM members

 

$this->db->select_min()

Writes a “SELECT MIN(field)” portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

$this->db->select_min('age');
$query = $this->db->get('members'); // Produces: SELECT MIN(age) as age FROM members

$this->db->select_avg()

Writes a “SELECT AVG(field)” portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

$this->db->select_avg('age');
$query = $this->db->get('members'); // Produces: SELECT AVG(age) as age FROM members

$this->db->select_sum()

Writes a “SELECT SUM(field)” portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

$this->db->select_sum('age');
$query = $this->db->get('members'); // Produces: SELECT SUM(age) as age FROM members

JOINS:

$this->db->join()

Permits you to write the JOIN portion of your query:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');
$query = $this->db->get();

// Produces:
// SELECT * FROM blogs JOIN comments ON comments.id = blogs.id

Multiple function calls can be made if you need several joins in one query.

If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

$this->db->join('comments', 'comments.id = blogs.id', 'left');
// Produces: LEFT JOIN comments ON comments.id = blogs.id

 

WHERE Condition:

 

$this->db->where()

This function enables you to set WHERE clauses using one of four methods:

1. Simple key/value method:

   $this->db->where('name', $name); // Produces: WHERE name = 'Joe'
   

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

   $this->db->where('name', $name);
   $this->db->where('title', $title);
   $this->db->where('status', $status);
   // WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
   

2. Custom key/value method:

   You can include an operator in the first parameter in order to control the comparison:

   $this->db->where('name !=', $name);
   $this->db->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45
   

3. Associative array method:

   $array = array('name' => $name, 'title' => $title, 'status' => $status);
   $this->db->where($array);
   // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
   

   You can include your own operators using this method as well:

   $array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
   $this->db->where($array);
   

4. Custom string:

   You can write your own clauses manually:

   $where = "name='Joe' AND status='boss' OR status='active'";
   $this->db->where($where);
   

$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);

$this->db->or_where()

This function is identical to the one above, except that multiple instances are joined by OR:

$this->db->where('name !=', $name);
$this->db->or_where('id >', $id);  // Produces: WHERE name != 'Joe' OR id > 50

$this->db->where_in()

Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with AND if appropriate

$names = array('Frank', 'Todd', 'James');
$this->db->where_in('username', $names);
// Produces: WHERE username IN ('Frank', 'Todd', 'James')

$this->db->or_where_in()

Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with OR if appropriate

$names = array('Frank', 'Todd', 'James');
$this->db->or_where_in('username', $names);
// Produces: OR username IN ('Frank', 'Todd', 'James')

$this->db->where_not_in()

Generates a WHERE field NOT IN (‘item’, ‘item’) SQL query joined with AND if appropriate

$names = array('Frank', 'Todd', 'James');
$this->db->where_not_in('username', $names);
// Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')

$this->db->or_where_not_in()

Generates a WHERE field NOT IN (‘item’, ‘item’) SQL query joined with OR if appropriate

$names = array('Frank', 'Todd', 'James');
$this->db->or_where_not_in('username', $names);
// Produces: OR username NOT IN ('Frank', 'Todd', 'James')

 

Inserting Data:

 

$this->db->insert()

$data = array(
        'title' => 'My title',
        'name' => 'My Name',
        'date' => 'My date'
);

$this->db->insert('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

 

Updating Data:

 

$this->db->update()

 

$data = array(
        'title' => $title,
        'name' => $name,
        'date' => $date
);

$this->db->where('id', $id);
$this->db->update('mytable', $data);
// Produces:
//
//      UPDATE mytable
//      SET title = '{$title}', name = '{$name}', date = '{$date}'
//      WHERE id = $id

$this->db->update('mytable', $data, "id = 4");

Or as an array:

$this->db->update('mytable', $data, array('id' => $id));

Deleting Data

 

$this->db->delete()

Generates a delete SQL string and runs the query.

$this->db->delete('table_name', array('id' => $id));

or

$this->db->where('id', $id);
$this->db->delete('mytable');

// Produces:
// DELETE FROM mytable
// WHERE id = $id

An array of table names can be passed into delete() if you would like to delete data from more than 1 table.

$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);

$this->db->empty_table()

Generates a delete SQL string and runs the query.:

$this->db->empty_table('table_name'); // Produces: DELETE FROM table_name

$this->db->truncate()

Generates a truncate SQL string and runs the query.

$this->db->from('table_name');
$this->db->truncate();

// or

$this->db->truncate('table_name');