Active Record or query builder in CodeIgniter

What is the Active record in CodeIgniter?

If you are not familiar with active records don’t worry I am going to tell you about active records or query builders in  CodeIgniter.

 Active record or query builder is a pattern, you can insert, update, delete from your database OR in other words you can make a query/queries using this pattern with minimal scripting. In some cases only a single line code is enough to perform a database action like $this->db->get_where(‘yourtable’,array(‘id’=>10); . So that means you can just call a method/function and pass some necessary parameters and CodeIgniter generate a query whenever you call the method/function. CodeIgniter providing built-in security when you use active record pattern.

I am using a MySQL database.

You can perform a simple query.

$this->db->query("SELECT * FROM std");

 

when you want to get all records from your table.

$query = $this->db->get('yourtable')
// Produces: SELECT * FROM yourtable
You can pass second and third parameters enable you to set a limit and offset clause:
$query = $this->db->get('yourtable', 10, 20);
// Produces: SELECT * FROM yourtable LIMIT 20, 10 (in MySQL)

 

when you filter your result using where clause.

$query = $this->db->get_where('yourtable', array('id' =>$id));
you can also pass second and third parameter to enable limit and offset.

 

when you selecting your columns from your table.

$this->db->select('title, content, date');
$query = $this->db->get('yourtable');
// Produces: SELECT title, content, date FROM yourtable

 

when you get maximum/minimum or other aggregate functions.

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

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

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

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

 

When you selecting something from your table.

$this->db->select('title, content, date');
$this->db->from('student');$query = $this->db->get();
// Produces: SELECT title, content, date FROM student 

$this->db->select('*')$this->db->from('student');$this->db->get();
// Produces: SELECT * FROM student

 

When you want to use join in Codeigniter to fetch the data from two OR multiple tables, either it’s a left join, right join, full join in Codeigniter.

$this->db->select('*');
$this->db->from('courses');
$this->db->join('courses', 'courses.st_id = student.id');
$query = $this->db->get();
// Produces:  SELECT * FROM courses JOIN courses ON courses.st_id = student.id
if you want a spacific type of join like left, right, outer, inner, left outer, and right outer you can just pass third parameter.
$this->db->join('courses', 'courses.st_id = student.id','left');
$this->db->join('courses', 'courses.st_id = student.id','right');

 

From function/method.

$this->db->select('*');
$this->db->from('student');
$this->db->where('std_name', $name);//$name is a variable i.e $name="shakzee" 
$this->db->get();
//Produces: SELECT * FROM student WHERE std_name = 'shakzee'

 

When you filter you recorded using where clause in Codeigniter

$this->db->where(std_name,'shakzee');
//'shakzee' is a string
$this->db->where('age', $title);
$this->db->where('status', $status); 
//Produces: WHERE std_name = 'shakzee' AND 'age', $title = 20 AND status = 'active';

You can include an operator in the first parameter  
$this->db->where('age > ',19);$this->db->where('age !=',19);
//Produces: WHERE age > 19
//Produces: WHERE age != 19you can also pass an Associative array in where.

$array = array('std_name' => $name, 'age' => $age, 'status' => $status);
$this->db->where($array);
// Produces: WHERE std_name = 'shakzee' AND age = 20 AND status = 'active'you can alos pass a custom string to make a query like

$where="WHERE std_name='shakzee' AND age=20 AND status='active'";
$this->db->where('std_name !=', $name);
$this->db->or_where('id >', $id); 
// Produces: WHERE std_name != 'shakzee' OR id > 50

 

where_in OR or_where_in clause

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

$names = array('shakzee', 'shehzad', 'ahmed'); 
$this->db->or_where_in('username', $names); // 
Produces: OR std_name IN ('shakzee', 'shehzad', 'ahmed');

 

like when you match something from your table.

$this->db->like('std_name', 'match'); 
// Produces: WHERE std_name LIKE '%match%'$this->db->like('std_name', 'match');
$this->db->like('age', 'match');

// WHERE std_name LIKE '%match%' AND age LIKE '%match%$this->db->like('std_name', 'match');
$this->db->or_like('age', $match); 
// WHERE 'std_name', 'match' LIKE '%match%' OR age LIKE '%match%'

$this->db->not_like('std_name', 'match');
// WHERE std_name NOT LIKE '%match%

 

When you add Group BY clause.

 

$this->db->select('*');
$this->db->from('student');
$this->db->group_by("age"); // Produces: SELECT * FROM student GROUP BY age;

 

When you add having a clause in your query.

$this->db->having('admin_id = 45'); 
// Produces: HAVING admin_id = 45

$this->db->having('admin_id', 45);
// Produces: HAVING admin_id = 45

 

When you want to remove duplication results.

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

 

When you show your results order by ascending or descending or random from your table.

the first parameter contain the column name and second parameter is you direction of the result option asc/desc/random. 
$this->db->order_by("age", "desc");
 // Produces: ORDER BY "age", "desc" DESC

 

When you apply a limit clause in your query

$this->db->limit(10);// Produces: LIMIT 10

 

you can insert a record using the insert method/function.

$data = array(   'std_name' => 'std name' ,   'age' => 20 ,   'date' => 'std date');
$this->db->insert('student', $data); 
// Produces: INSERT INTO student (std_name, age, date) VALUES ('std name', 20, 'std date');
$data = array(  array(   'std_name' => 'std name' ,   'age' => 20 ,   'date' => 'std date'),  array(   'std_name' => 'another std name' ,   'age' => 21 ,   'date' => 'another std date'));
$this->db->insert_batch('student', $data); 

// Produces: INSERT INTO student (title, name, date) VALUES ('std name', 20, 'std date'),('another std name', 21, 'another std date');

 

When you updating your rows from your table.

$data = array(
'std_name' => $stdname,
'age' => $age,
'date' => $date
);
$this->db->where('id', $id);
$this->db->update('student', $data); 

// Produces:// UPDATE student // SET std_name = '{$title}', age = '{$name}', date = '{$date}'// WHERE id = $id

 

When you deleting something from your tables.

$this->db->delete('student', array('id' => $id)); 
// Produces:// DELETE FROM student // WHERE id = $id$this->db->where('id', $id);
$this->db->delete('student'); 
// Produces:DELETE FROM student  WHERE id = $id

 

When you remove all the data from your table.

$this->db->empty_table(student); 
// Produces DELETE FROM student

 

When you truncate your table

$this->db->from('student');
$this->db->truncate();
// Produce:// TRUNCATE student  
OR

$this->db->truncate('student'); 
// Produce:// TRUNCATE student

 

shakzee
administrator
My Name is Shehzad Ahmed and I am a full-time developer with over 7+ years of experience. In that time, I’ve worked as a developer with many big and small firms and was teaching web design and development skills to many keen learners and by the time passes I’ve found a great passion and potential on myself as a teacher on this particular field and now I am a full-time teacher and working as a freelancer.

Leave a Reply

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

WhatsApp chat