CodeIgniter Select Query Example

CodeIgniter Select Query Example: This tutorial shows you how to fetch data from database in codeigniter. SELECT operation is also called as READ and one among the database CRUD process. Codeigniter comes with a comprehensive query builder interface to build complex and database-independent queries. And its Active Records library offers a bunch of functions to form wide range of select queries to read data from database.

Also check for codeigniter insert query and codeigniter update query.

CodeIgniter Select Query Example:

This is the general format for writing select query in code igniter.

$this->db->select('column1, column2');
$this->db->from('table_name');
$this->db->where('column', 'value');
$query = $this->db->get();

// Produces SQL String:
// SELECT column1, column2 FROM table_name WHERE column = 'value';

• The function $this->db->select(); lets you write the select portion of the sql query i.e, to select the columns to be fetched from database.
• $this->db->from(); allows you to specify from which table you want to fetch data.
• $this->db->where(); helps to write the where clause of the select query. There are multiple variations of this where() function in codeigniter and let's see them later in this tutorial.
• $this->db->get(); executes the select query and returns the resultset.

Let's take this below mysql table as an example for writing select query in codeigniter.

MySQL Table – Employees:

Here is a select query with where clause that retrieves data of a specific record from the given table.

$this->db->select('EmpID, EmpName, Designation');
$this->db->from('Employees');
$this->db->where('EmpID', 2);
$query = $this->db->get();

// Produces Select Query:
// SELECT EmpID, EmpName, Designation FROM Employees WHERE EmpID = 2;

Alternatively you can ditch from() method and pass the 'table_name' as parameter to $this->db->get() function itself.

$this->db->select('*');
$this->db->where('Department', 'Finance');
$query = $this->db->get('Employees');

// Produces Select Query:
// SELECT * FROM Employees WHERE Department = 'Finance';

Please note that if you want to retrieve all columns in select(), then you can skip select statement.

Using $this->db->get_where() Method:

The method get_where() is the combination of get() and where() and takes up an array of where conditioning as second parameter.

$query = $this->db->get_where('Employees', array('EmpName' => 'Colleen Hurst'));

// Produces SQL:
// SELECT * FROM Employees WHERE EmpName = 'Colleen Hurst';

Codeigniter Select Query with Multiple Where Condition:

You can apply multiple where conditions in a single select query. Below code igniter select query example uses multiple where clause combined with AND keyword.

$this->db->where('DeptName', 'Sales');
$this->db->where('Salary >=', 100000);
$query = $this->db->get('Employees');

// Produces SQL:
// SELECT * FROM Employees WHERE DeptName = 'Sales' AND Salary >= 100000;

If you want to use OR keyword in-between where conditions, then use $this->db->or_where(); instead of where().

$this->db->where('DeptName', 'Sales');
$this->db->or_where('Designation', 'Manager');
$query = $this->db->get('Employees');

// Produces SQL:
// SELECT * FROM Employees WHERE DeptName = 'Sales' OR Designation = 'Manager';

Pass Array to Where Clause for Multiple Conditions:

$array = array('Department' => 'HQ', 'Designation !=' => 'Manager', 'Salary <' => 100000);
$this->db->where($array);
$query = $this->db->get('Employees');

// Produces SQL:
// SELECT * FROM Employees WHERE Department = 'HQ' AND Designation != 'Manager' AND Salary < 100000;

CodeIgniter Select Query with $this->db->where_in()

Using where_in() function allows you to all fetch records that have matching field value present in the provided list.

$department = array('HQ', 'Finance', 'Software');
$this->db->where_in('DeptName', $department);
$query = $this->db->get('Employees');

// Produces SQL:
// SELECT * FROM Employees WHERE DeptName IN ('HQ', 'Finance', 'Software');

Select Query with $this->db->where_not_in()

On the other hand where_not_in() function allows you to fetch records those don't have the matching field value present in the list provided.

$id = array(2, 3, 8);
$this->db->where_not_in('EmpID', $id);
$query = $this->db->get('Employees');

// Produces SQL:
// SELECT * FROM Employees WHERE EmpID NOT IN (2, 3, 8);

Codeigniter Select Query with Like:

You can form select query with like clause using $this->db->like(); function. Here is a query example with like clause.

$this->db->from('Employees');
$this->db->like('EmpName', 's', after);
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Employees WHERE EmpName LIKE 's%';

This query selects all employee records with 'EmpName' starting with the character 's'. The like() function has an optional third parameter which takes up 'before', 'after' or 'both' keys to control the placement of '%' wildcard character in the matching string.

Codeigniter Select Query with Limit:

Method $this->db->limit(); should be used to add limit clause in select query and controls the number of records returned by the query.

$this->db->select('*');
$this->db->from('Employees');
$this->db->limit(5, 10);
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Employees LIMIT 10, 5;

Codeigniter Select Query with Join:

Join queries are used to combine and retrieve data from two or more tables. Here is the example for codeigniter select query with join.

$this->db->select('EmpID, EmpName, DepartmentName, Designation');
$this->db->from('Employees');
$this->db->join('Department', 'Department.DeptID = Employees.DeptID');
$query = $this->db->get();

// Produces SQL Query:
// SELECT EmpID, EmpName, DepartmentName, Designation FROM Employees JOIN Department ON Department.DeptID = Employees.DeptID;

Check more about joins in all about joins in codeigniter, codeigniter left join query & codeigniter right join query.

Codeigniter Select Query with Order By:

Order_by() clause is used to sort the select query results in ascending or descending order based upon one or more column names.

$this->db->select('*');
$this->db->from('Employees');
$this->db->order_by('EmpName', 'desc');
$query = $this->db->get();

// Produces SQL
// SELECT * FROM Employees ORDER BY EmpName desc;

Look for codeigniter order by query example to learn more in detail.

CodeIgniter Method Chaining:

You can simply shorten code igniter active record queries using method chaining. It works with PHP v5+ and let you to connect multiple methods like select(), from(), where() etc. as a single statement.

$this->db->select('*')->from('Employees')->like('Designation', 'Executive')->order_by('EmpName', 'desc')->limit(10, 1);
$query = $this->db->get();

Related Read: Fetch Data from Database & Display in HTML Table using CodeIgniter & MySQL

That's all about writing select query in codeigniter. I hope you find all the above codeigniter select query examples useful. Please let me know if you have any queries via comments.