CodeIgniter Simply Database

CodeIgniter uses Active Record Class for database action.Active Record Class is based on minimal scripting. so in some cases only one or two lines of code are necessary to perform a database action. if you want write your own queries you can disable Active Record Class in your database config file.

Selecting Data

The following functions are used to build SELECT statements.

$this->db->get();

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

The second and third parameters are used to set a limit and offset clause:

$query = $this->db->get('employeetable', 10, 20); // Regular query: SELECT * FROM employeetable LIMIT 20, 10

$this->db->get_where();

get_where() was formerly known as getwhere(), which has been removed from lastest version of CodeIgniter.

$query = $this->db->get_where('employeetable', array('id' => $id), $limit, $offset); // Regular query: SELECT * FROM employeetable Where id='$id' LIMIT 20, 10

$this->db->select();

$this->db->select('name, gender, joiningdate'); $query = $this->db->get('employeetable'); // Regular query: SELECT name, gender, joiningdate FROM employeetable

If we are selecting all (*) from a table we do not need to use this function.

$this->db->select_max();

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

$this->db->select_min();

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

$this->db->select_avg();

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

$this->db->select_sum();

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

$this->db->from();

$this->db->select('name, gender, joiningdate'); $this->db->from('employeetable'); $query = $this->db->get(); // Regular query: SELECT name, gender, joiningdate FROM employeetable

As shown above, the FROM portion of our query can be specified in the $this->db->get() function.

$this->db->where();

$this->db->where('name', $name); $query = $this->db->get('employeetable'); // Regular query : SELECT * FROM employeetable WHERE name = 'Manish'

Now we use multiple function calls.

$this->db->where('name', $name); $this->db->where('gender', $gender); $query = $this->db->get('employeetable'); // Regular query : SELECT * FROM employeetable WHERE name = 'Manish' and gender ='Male'

$this->db->or_where();

$this->db->where('name !=', $name); $this->db->or_where('id >', $id); $query = $this->db->get('employeetable'); // Regular query :SELECT * FROM employeetable WHERE name != 'Manish' OR id > 50

$this->db->where_in();

$names = array('Manish', 'Pawan'); $this->db->where_in('name', $names); $query = $this->db->get('employeetable'); // Regular query: SELECT * FROM employeetable WHERE name IN ('Manish','Pawan')

$this->db->where_not_in();

$names = array('Manish', 'Pawan'); $this->db->where_not_in('name', $names); $query = $this->db->get('employeetable'); // Regular query: SELECT * FROM employeetable WHERE name NOT IN ('Manish', 'Pawan')

$this->db->like();

This function used to generate LIKE clauses, which is useful for doing searches.

$this->db->like('name', 'manish'); $query = $this->db->get('employeetable'); // Regular query: SELECT * FROM employeetable WHERE name LIKE '%manish%'

Now we use multiple function calls.

$this->db->like('name', 'manish'); $this->db->like('gender','male'); $query = $this->db->get('employeetable'); // Regular query: SELECT * FROM employeetable WHERE name LIKE '%manish%' AND gender LIKE '%male%'

$this->db->group_by();

$this->db->group_by("name"); $query = $this->db->get('employeetable'); // Regular query: SELECT * FROM employeetable GROUP BY name

$this->db->distinct();

$this->db->distinct('name'); $this->db->get('employeetable'); // Regular query: SELECT DISTINCT name FROM employeetable

$this->db->order_by();

$this->db->order_by("name", "desc"); $this->db->get('employeetable'); // Regular query: SELECT * FROM employeetable ORDER BY name DESC

$this->db->limit();

With the help of this function we can set limit of rows return by query.

$this->db->limit(10); $this->db->get('employeetable'); // Regular query: SELECT * FROM employeetable LIMIT 10

Inserting Data

The following functions are used to build INSERT statements.

$this->db->insert();

$data = array( 'name' => 'Manish' , 'gender' => 'Male' , 'joiningdate' => '20-09-2011' ); $this->db->insert('employeetable', $data); // Regular query: INSERT INTO employeetable (name,gender,joiningdate) VALUES ('Manish','Male','20-09-2011')

Updating Data

$this->db->update();

This function used to generates an update string and runs the query based on the data you supply.

$data = array( 'name' => 'Shivani' , 'gender' => 'Female' , 'joiningdate' => '02-02-2011' ); $this->db->update('employeetable', $data); // Regular query: Update employeetable set name='Shivani',gender='Female', joiningdate='02-02-2011'

$this->db->set();

This function enables you to set values for inserts or updates.

$this->db->set('name', $name); $this->db->update('employeetable'); // Regular query: Update employeetable set name='Shivani';

Deleting Data

$this->db->delete();

This Function is uesd to generates a delete SQL string and runs the query.

$this->db->delete('employeetable', array('name' => $name)); // Regular query:DELETE FROM employeetable WHERE name ='$name';