Active Record or query builder in CodeIgniter

Active Record or query builder in CodeIgniter

What is the Active record in CodeIgniter?

If you are unfamiliar with active records, don't worry, I will tell you about active records or query builders in  CodeIgniter.

 An active record or query builder is a pattern; you can insert, update, or delete from your database. 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, you can call a method/function and pass some necessary parameters. Codeigniter generates a query whenever you call the method/function—Codeigniter provides built-in security when using an active record pattern.

I am using a MySQL database.

You can perform a simple query.

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

When do 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 using the where clause to filter your results.

$query = $this->db->get_where('yourtable', array('id' =>$id));

you can also pass second and third parameter to enable limit and offset.

When you are 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 in Codeigniter.

$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 are selecting something from your table in Codeigniter.

$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, 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 record using the 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 in Codeigniter.

$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 in Codeigniter.

$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 in Codeigniter.

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/insert_batch() 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 are updating your rows from your table in Codeigniter.

$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 are 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 in Codeigniter.

$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

https://www.youtube.com/watch?v=_JeIue3h0Lo