CodeIgniter Select Query

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.

Syntax:-

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:-

get() method has optional second and third parameter which allows you to set limit and offset.

Example:-

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:-

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:-

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:-

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:-

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:-

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.

Recommended:-  Codeigniter Directory Structure

Example:-

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:-

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:-

Custom key/value method :- You are free to use an operator in the first parameter in order to control the comparison.

Example:-

Associative array method :-

Example:-

Operators can also be included in this method.

Example:-

Custom string :- You can pass complete WHERE Clause string as following –

Example:-

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:-

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:-

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.

Recommended:-  CodeIgniter Active Record Caching

Example:-

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:-

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:-

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:-

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:-

Associative array method:-

Example:-

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:-

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

The not_like() function allows you to generate “NOT LIKE” clauses for your query.

Example:-

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.

Recommended:-  Codeigniter Configuration

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:-

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

The distinct() function is used to add “DISTINCT” keyword to your query.

Example:-

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:-

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:-

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:-

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:-

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

The count_all() function allows you to determine the number of rows in particular table.

Example:-

 

 

 

In this tutorial we have learn about the CodeIgniter Select Query and its application with practical example. I hope you will like this tutorial.