In this tutorial you will learn about the CodeIgniter Select Query and its application with practical example.
In CodeIgniter, get() method is used to run select statement and return data from table. It can be used stand alone to retrieve all records from a table.
- CodeIgniter Select Query with $this->db->get()
- CodeIgniter Select Query with $this->db->get_where()
- CodeIgniter Select Query with $this->db->select()
- CodeIgniter Select Query with $this->db->from()
- CodeIgniter Select Query with $this->db->join()
- CodeIgniter Select Query with $this->db->where()
- CodeIgniter Select Query with $this->db->or_where()
- CodeIgniter Select Query with $this->db->where_in()
- CodeIgniter Select Query with $this->db->or_where_in()
- CodeIgniter Select Query with $this->db->where_not_in()
- CodeIgniter Select Query with $this->db->or_where_not_in()
- CodeIgniter Select Query with $this->db->like()
- CodeIgniter Select Query with $this->db->or_like()
- CodeIgniter Select Query with $this->db->not_like()
- CodeIgniter Select Query with $this->db->or_not_like()
- CodeIgniter Select Query with $this->db->group_by()
- CodeIgniter Select Query with $this->db->distinct()
- CodeIgniter Select Query with $this->db->having()
- CodeIgniter Select Query with $this->db->or_having()
- CodeIgniter Select Query with $this->db->order_by()
- CodeIgniter Select Query with $this->db->limit()
- CodeIgniter Select Query with $this->db->count_all_results()
- CodeIgniter Select Query with $this->db->count_all()
Syntax:-
|
1 |
get($table = '', $limit = NULL, $offset = NULL) |
Here,
$table(String):- Table Name
$limit(string):- It contains limit clause
$offset(string):- It contains offset clause
Returns(CI_DB_result):- It returns CI_DB_result.
In order to generate SQL SELECT statement, get() method is used along with various other functions.
Example:- Let’s say you have a MySQL table named ’employee_master’ with the following fields –
emp_ID, emp_name, emp_email, emp_phone, emp_address, emp_code, emp_salary and emp_dept
CodeIgniter Select Query with $this->db->get()
This is how you can use $this->db->get() function to retrieve records from ’employee_master’ table.
Example:-
|
1 2 3 |
$query = $this->db->get('employee_master'); // Produces: SELECT * FROM employee_master |
get() method has optional second and third parameter which allows you to set limit and offset.
Example:-
|
1 2 3 |
$query = $this->db->get('employee_master', 10, 20); // Produces: SELECT * FROM employee_master LIMIT 20, 10 |
Since $this->db->get() returns CI_DB_result instance which you can not directly use to access the result, thus you need to assign it to a variable and then use one of the built in result functions to fetch the result.You will usually use a for or foreach loop to iterate over results like –
Example:-
|
1 2 3 4 5 |
$query = $this->db->get('employee_master'); foreach ($query->result() as $row) { echo $row->emp_name; } |
CodeIgniter Select Query with $this->db->get_where()
The get_where() method is similar to get() method except that it allows you to add “WHERE” clause in second parameter. This is how you can select records from ’employee_master’ table using $this->db->get_where().
Example:-
|
1 2 3 4 5 |
$query = $this->db->get_where('employee_master', array('emp_dept' => 'sales')); // Produces: // SELECT * FROM employee_master WHERE emp_dept= "sales"; // or // $query = $this->db->get_where('employee_master', array('emp_dept' => 'sales'), $limit, $offset); |
CodeIgniter Select Query with $this->db->select()
The select() method allows you to write the “SELECT” portion of your query. This is how you can select records from ’employee_master’ table using $this->db->select() method.
Example:-
|
1 2 3 |
$this->db->select('emp_name, emp_code, emp_dept, emp_salary'); $query = $this->db->get('employee_master'); // Produces: SELECT emp_name, emp_code, emp_dept, emp_salary FROM employee_master |
By default it selects all (*) from given table.The select() method accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.
Example:-
|
1 2 |
$this->db->select('(SELECT SUM(expenses.amount) FROM expenses WHERE expenses.invoice_id=5') AS exp_paid', FALSE); $query = $this->db->get('employee_master'); |
CodeIgniter Select Query with $this->db->from()
The from() method allows you to write the “FROM” clause of your query. This is how you can select records from ’employee_master’ table using $this->db->from() method along with the $this->db->get() method.
Example:-
|
1 2 3 4 |
$this->db->select('emp_name, emp_code, emp_dept, emp_salary'); $this->db->from('employee_master'); $query = $this->db->get(); // Produces: SELECT emp_name, emp_code, emp_dept, emp_salary FROM employee_master |
CodeIgniter Select Query with $this->db->join()
The join() method allows you to write the “JOIN” clause for your select query. The “JOIN” clause makes easy to fetch records from multiple tables.
Example:-
|
1 2 3 4 5 6 7 8 9 |
$this->db->select('*'); $this->db->from('articles'); $this->db->join('comments', 'comments.id = articles.id'); $query = $this->db->get(); // Produces: // SELECT * FROM articles // JOIN comments ON comments.id = articles.id |
The $this->db->join(); method can be called more than once to have more than one JOIN in the query. The third parameter of the join() method is used to specify the type of JOIN (left, right, outer, inner, left outer, right outer).
Example:-
|
1 2 3 4 5 6 7 8 9 |
$this->db->select('*'); $this->db->from('articles'); $this->db->join('comments', 'comments.id = articles.id','left'); $query = $this->db->get(); // Produces: // SELECT * FROM articles // LEFT JOIN comments ON comments.id = articles.id |
CodeIgniter Select Query with $this->db->where()
The where() method allows you to set “WHERE” clauses for your select query. You are free to use where() method multiple times to prepare your select query, they all will be chained together with AND between them. Using where() function you can set “WHERE” clause in following four ways –
Simple key/value method :-
Example:-
|
1 2 3 |
$this->db->where('emp_name', $name); // Produces: WHERE emp_name = 'John' |
Custom key/value method :- You are free to use an operator in the first parameter in order to control the comparison.
Example:-
|
1 2 3 4 |
$this->db->where('emp_name !=', $name); $this->db->where('emp_ID >', $id); // Produces: WHERE emp_name != 'Joe' AND emp_ID > 10 |
Associative array method :-
Example:-
|
1 2 3 4 |
$array = array('emp_name' => $name, 'emp_dept' => $dept); $this->db->where($array); // Produces: WHERE emp_name= 'John' AND emp_dept = 'sales' |
Operators can also be included in this method.
Example:-
|
1 2 |
$array = array('emp_name !=' => $name, 'emp_ID >' => $id); $this->db->where($array); |
Custom string :- You can pass complete WHERE Clause string as following –
Example:-
|
1 2 |
$where = "emp_name='John' AND emp_dept='sales'"; $this->db->where($where); |
CodeIgniter Select Query with $this->db->or_where()
The or_where() function is similar as where() function, except that it joins multiple where() function calls with OR operator.
Example:-
|
1 2 3 4 |
$this->db->where('emp_name !=', $name); $this->db->or_where('emp_ID >', $id); // Produces: WHERE emp_name != 'John' OR id > 10 |
CodeIgniter Select Query with $this->db->where_in()
The where_in() function is used to generate WHERE field IN (‘item’, ‘item’) SQL query string joined with AND if appropriate.
Example:-
|
1 2 3 |
$names = array('John', 'Steve'); $this->db->where_in('emp_name', $names); // Produces: WHERE emp_name IN ('John', 'Steve') |
CodeIgniter Select Query with $this->db->or_where_in()
The or_where_in() function is used to generate WHERE field IN (‘item’, ‘item’) SQL query string joined with OR if appropriate.
Example:-
|
1 2 3 |
$names = array('John', 'Steve'); $this->db->or_where_in('emp_name', $names); // Produces: OR emp_name IN ('John', 'Steve') |
CodeIgniter Select Query with $this->db->where_not_in()
The where_not_in() function is used to generate WHERE field NOT IN (‘item’, ‘item’) SQL query string joined with AND if appropriate.
Example:-
|
1 2 3 |
$names = array('John', 'Steve'); $this->db->where_not_in('emp_name', $names); // Produces: WHERE emp_name NOT IN ('John', 'Steve') |
CodeIgniter Select Query with $this->db->or_where_not_in()
The or_where_not_in() function is used to generate WHERE field NOT IN (‘item’, ‘item’) SQL query string joined with OR if appropriate.
Example:-
|
1 2 3 |
$names = array('John', 'Steve'); $this->db->or_where_not_in('emp_name', $names); // Produces: OR emp_name NOT IN ('John', 'Steve') |
CodeIgniter Select Query with $this->db->like()
The like() function allows you to generate “LIKE” clauses for your query. You are free to use like() function multiple times to prepare your select query, they all will be chained together with AND between them. Using like() function you can set “LIKE” clause in following ways –
Simple key/value method:-
Example:-
|
1 2 3 4 |
$this->db->like('emp_name', 'pattern'); $this->db->like('emp_code', 'pattern'); // WHERE emp_name LIKE '%pattern%' AND emp_code LIKE '%pattern% |
An optional third argument helps you to set where the wildcard (%) character will be placed. Options are ‘before’, ‘after’, ‘both’ (default) and ‘none’ (no wildcard).
Example:-
|
1 2 3 4 5 6 7 8 9 10 11 |
$this->db->like('emp_code', 'pattern', 'before'); // Produces: WHERE emp_code LIKE '%pattern' $this->db->like('emp_code', 'pattern', 'after'); // Produces: WHERE emp_code LIKE 'pattern%' $this->db->like('emp_code', 'pattern', 'both'); // Produces: WHERE emp_code LIKE '%pattern%' $this->db->like('emp_code', 'pattern', 'none'); // Produces: WHERE emp_code LIKE 'pattern' |
Associative array method:-
Example:-
|
1 2 3 4 |
$array = array('emp_name' => $pattern1, 'emp_code' => $pattern2); $this->db->like($array); // WHERE emp_name LIKE '%pattern1%' AND emp_code LIKE '%pattern2%' |
CodeIgniter Select Query with $this->db->or_like()
The or_like() function is similar as like() function, except that it joins multiple like() function calls with OR operator.
Example:-
|
1 2 3 4 |
$this->db->like('emp_name', 'pattern'); $this->db->or_like('emp_code', $pattern); // WHERE emp_name LIKE '%pattern%' OR emp_code LIKE '%pattern%' |
CodeIgniter Select Query with $this->db->not_like()
The not_like() function allows you to generate “NOT LIKE” clauses for your query.
Example:-
|
1 2 3 |
$this->db->not_like('emp_name', 'pattern'); // WHERE emp_name NOT LIKE '%pattern% |
CodeIgniter Select Query with $this->db->or_not_like()
The or_not_like() function is similar as not_like() function, except that it joins multiple not_like() function calls with OR operator.
|
1 2 3 4 |
$this->db->like('emp_name', 'pattern1'); $this->db->or_not_like('emp_code', 'pattern2'); // WHERE emp_name LIKE '%pattern1% OR emp_code NOT LIKE '%pattern2%' |
CodeIgniter Select Query with $this->db->group_by()
The group_by() method allows you to set the “GROUP BY” clause for your query.You can group by multiple field values using an array.
Example:-
|
1 2 3 4 5 6 7 |
$this->db->group_by("emp_dept"); // Produces: GROUP BY emp_dept $this->db->group_by(array("emp_dept", "emp_salary")); // Produces: GROUP BY emp_dept, emp_salary |
CodeIgniter Select Query with $this->db->distinct()
The distinct() function is used to add “DISTINCT” keyword to your query.
Example:-
|
1 2 3 4 |
$this->db->distinct(); $this->db->get('employee_master'); // Produces: SELECT DISTINCT * FROM employee_master |
CodeIgniter Select Query with $this->db->having()
The having() function allows you to generate “HAVING” clause for your query. Using having() function you can set “HAVING” clause in following ways –
Example:-
|
1 2 3 4 5 6 7 8 |
$this->db->having('emp_ID = 11'); // Produces: HAVING emp_ID = 11 or $this->db->having('emp_ID', 11); // Produces: HAVING emp_ID = 11 $this->db->having(array('emp_name=' => 'John', 'emp_code' => 'EMP001')); // Produces: HAVING emp_name= 'Jogn', emp_code='EMP001' |
CodeIgniter Select Query with $this->db->or_having()
The or_having() function is similar as having() function, except that it joins multiple having() function calls with OR operator.
CodeIgniter Select Query with $this->db->order_by()
The order_by() function allows you to set “ORDER BY” clause for your query. The first parameter is used to pass column name you would like to order by. The second parameter allows you to set the direction of the order. Options are asc or desc, or random.The order_by() function can be used in following ways –
Example:-
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$this->db->order_by("emp_name", "desc"); // Produces: ORDER BY emp_name DESC $this->db->order_by('emp_ID desc, emp_name asc'); // Produces: ORDER BY emp_ID DESC, emp_name ASC $this->db->order_by("emp_name", "asc"); $this->db->order_by("emp_ID", "desc"); // Produces: ORDER BY emp_name ASC, emp_ID DESC |
CodeIgniter Select Query with $this->db->limit()
The limit() function allows you to set “LIMIT” clause for your query. The first parameter is used to set number of rows you would like to retrieve. The second parameter allows you to set the result offset. The limit() function can be used in following ways –
Example:-
|
1 2 3 4 5 |
$this->db->limit(10); // Produces: LIMIT 10 $this->db->limit(10, 20); // Produces: LIMIT 20, 10 |
CodeIgniter Select Query with $this->db->count_all_results()
The count_all_results() function allows you to you to determine the number of rows in a particular Active Record query resultset.
Example:-
|
1 2 3 4 5 6 7 8 9 |
$num_rows=$this->db->count_all_results('employee_master'); echo $num_rows; // Produces an integer, like 55 $this->db->where('emp_dept', 'sales'); $this->db->from('employee_master'); $num_rows=$this->db->count_all_results(); echo $num_rows; // Produces an integer, like 15 |
CodeIgniter Select Query with $this->db->count_all()
The count_all() function allows you to determine the number of rows in particular table.
Example:-
|
1 2 3 |
echo $this->db->count_all('employee_master'); // Produces an integer, like 55 |
