Table of Contents
CodeIgniter Select Query – how to run Select Query in CodeIgniter
In this post we will show you how to run CodeIgniter Select Query, we give you differnet method for run Select Query in CodeIgniter.
how to run CodeIgniter Select Query, group_by, With Query Bindings, Select The Fields, With Limit, With Offset,Limit, With select, from, With Where, Or_Where, Where_In, or_where_in, where_not_in, With Like, or_like, not_like, or_not_like,With group_by, With having, With Order BY, select_max(), select_min(), select_sum(), join(), num_rows(), num_fields(), With result(), result_array(), Query With Single Result(), Count Of Query Result.
CodeIgniter ‘SELECT’ query will execute using following functions. They are
$this->db->query()
$this->db->query() With Query Bindings
$this->db->get()
$this->db->get() Select The Fields
$this->db->get() With Limit
$this->db->get() With Offset,Limit
$this->db->get() With select, from
$this->db->get() With Where, Or_Where, Where_In, or_where_in, where_not_in
$this->db->get_where()
$this->db->get() With Like, or_like, not_like, or_not_like
$this->db->get() With group_by
$this->db->get() With having
$this->db->get() With Order BY
$this->db->select_max()
$this->db->select_min()
$this->db->select_sum()
$this->db->join()
$this->db->num_rows()
$this->db->num_fields()
Query Result With result()
Query Result With result_array()
Query With Single Result()
Count Of Query Result
CodeIgniter Select Query :: $this->db->query()
To submit/run this query, we use the following function:
$this->db->query('ADD YOUR QUERY HERE');
place raw SQL in the query() method
$get_query = $this->db->query("select * from tb_all_user");
Pass SQL query in a variable
// simple select query $sql_query = "select * from tb_all_user"; $get_query = $this->db->query($sql_query);
CodeIgniter Select Query :: $this->db->query() With Query Bindings
Note:Use Of Query Bindings
Benefit of using binds is that the values are automatically escaped, producing safer queries
// SELECT query WHERE name AND type
$sql_query = "SELECT * FROM tb_all_user WHERE name = ? AND type = ?";
// execut query
$this->db->query($sql_query, array('code', 'php'));
CodeIgniter Select Query :: Active Record Query
CodeIgniter Select Query :: $this->db->get()
Use the built-in get() method
// get users data
$get_query = $this-> db-> get('users');
// get employees data
$get_query = $this-> db-> get('employees');
CodeIgniter Select Query :: $this->db->get() Select The Fields
Format 1
// get users data
$this->db->select('name');
// execut query
$get_query = $this-> db-> get('tb_all_user');
Format 2
// execut query
$this->db->select('name')->from('tb_all_user')->get();
//SELECT `name` FROM ('tb_all_user')
CodeIgniter Select Query :: $this->db->get() With Limit
Program 1
$limit = 15;
// execut query with limit
$get_query = $this-> db-> get('tb_all_user',$limit);
Program 2
// get id,name
$this->db->select('id, name');
$this->db->from('tb_all_user');
// limit is 1
$this->db->limit(1);
$get_query = $this-> db-> get();
// limit is 1 and get id,name
//select id, name from tb_all_user limit 1;
CodeIgniter Select Query :: $this->db->get() With Offset,Limit
// limit 15, 25
$limit = 15;
$offset = 25;
// set limit and offset - 15, 25
$get_query = $this-> db-> get('tb_all_user',$offset,$limit);
// query work somthing like this
// select * from tb_all_user limit 15, 25
$this->db->get() With select, from
// get id,name
$this->db->select('id, name');
// pass table name
$this->db->from('tb_all_user');
// execut query with limit
$get_query = $this->db->get();
// SELECT `id`, `name` FROM (`tb_all_user`) LIMIT 15, 25;
CodeIgniter Select Query :: $this->db->get() With Where, Or_Where, Where_In, or_where_in, where_not_in
Program 1
// get username data
$this->db->select('username');
$this->db->from('tb_all_user');
$this->db->where('userid',17);
// get username where is admin
$this->db->where("usertype","admin");
$get_query=$this->db->get();
// get username where is admin
//SELECT `username` FROM (`tb_all_user`) WHERE `userid` = 17 AND `usertype` = 'admin'
Program 2
$this->db->select('username');
$this->db->from('tb_all_user');
$this->db->where('userid',17);
$this->db->where("usertype !=","admin");
$get_query=$this->db->get();
//SELECT `username` FROM (`tb_all_user`) WHERE `userid` = 17 AND `usertype` != 'admin'
Program 3:
$this->db->select('username');
$this->db->from('tb_all_user');
$this->db->where("userid > 17 AND usertype='users'");
$get_query=$this->db->get();
//SELECT `username` FROM (`tb_all_user`) WHERE `userid` > 17 AND usertype='users'
Program 4:
$this->db->from('tb_all_user');
$this->db->where('username !=', $name);
$this->db->or_where('userid >', $userid);
//SELECT * FROM (`tb_all_user`) WHERE username != 'admin' OR userid > 6
Program 5:
$user_names = array('user_name1', 'user_name2', 'user_name3');
$this->db->from('tb_all_user');
$this->db->where('status !=', $status);
$this->db->or_where_in('username', $user_names);
//SELECT * FROM (`tb_all_user`) WHERE status='active' OR username IN ('user_name1', 'user_name2', 'user_name3')
Program 6:
$user_names = array('user_name1', 'user_name2', 'user_name3');
$this->db->from('tb_all_user');
$this->db->where_not_in('username', $user_names);
//SELECT * FROM (`tb_all_user`) WHERE username NOT IN ('user_name1', 'user_name2', 'user_name3')
Program 7:
$user_names = array('user_name1', 'user_name2', 'user_name3');
$this->db->from('tb_all_user');
$this->db->where('status !=', $status_user);
$this->db->or_where_not_in('username', $user_names);
//SELECT * FROM (`tb_all_user`) WHERE status = 'active' OR username NOT IN ('user_name1', 'user_name2', 'user_name3')
$this->db->get_where()
CodeIgniter Select Query :: Use the built-in get() method
$status_user = 'admin';
$options = array('usertype'=>$status_user);
$get_query = $this->db->get_where('tb_all_user',$options);
//get_where with limit
$limit = 15;
$get_query = $this->db->get_where('tb_all_user',$options,$limit);
$this->db->get() With Like, or_like, not_like, or_not_like
Program 1:
$this->db->select('username');
$this->db->from('tb_all_user');
$this->db->like("username","code");
$get_query=$this->db->get();
//SELECT `username` FROM (`tb_all_user`) WHERE `username` LIKE '%code%'
Program 2:
$this->db->select('username,userid');
$this->db->from('tb_all_user');
$this->db->like('username','code');
$this->db->or_like('usertype','admin');
$get_query=$this->db->get();
//SELECT `username`, `userid` FROM (`tb_all_user`) WHERE `username` LIKE '%code%' OR `usertype` LIKE '%admin%'
Program 3:
$this->db->select('username,userid');
$this->db->from('tb_all_user');
$this->db->not_like('username','code');
$this->db->or_like('usertype','admin');
$get_query=$this->db->get();
//SELECT `username`, `userid` FROM (`tb_all_user`) WHERE `username` NOT LIKE '%code%' OR `usertype` LIKE '%admin%'
Program 4:
$this->db->select('username,userid');
$this->db->from('tb_all_user');
$this->db->where('status','active');
$this->db->or_not_like('username','code');
$get_query=$this->db->get();
//SELECT `username`, `userid` FROM (`tb_all_user`) WHERE `status` = 'active' OR `username` NOT LIKE '%code%'
CodeIgniter Select Query :: $this->db->get() With group_by
$this->db->select('*');
$this->db->from('tb_all_user');
$this->db->group_by("states");
//SELECT * FROM (`tb_all_user`) group by states
CodeIgniter Select Query :: $this->db->get() With having
$this->db->select('*');
$this->db->from('tb_all_user');
$this->db->having("states=1");
//SELECT * FROM (`tb_all_user`) HAVING states=1
CodeIgniter Select Query :: $this->db->get() With Order BY
Program 1:
$this->db->select('username');
$this->db->from('tb_all_user');
$this->db->order_by('username');
$get_query = $this->db->get();
//SELECT `username` FROM (`tb_all_user`) ORDER BY `username`
Program 2:
$this->db->select('username');
$this->db->from('tb_all_user');
$this->db->order_by('username','desc');
$get_query=$this->db->get();
//SELECT `username` FROM (`tb_all_user`) ORDER BY `username` desc
Mathematics Function
CodeIgniter Select Query :: $this->db->select_max()
$this->db->select_max('age');
$get_query = $this->db->get('tb_all_user');
//SELECT MAX(age) as age FROM tb_all_user
$this->db->select_max('age', 'userage');
$get_query = $this->db->get('tb_all_user');
//SELECT MAX(age) as userage FROM tb_all_user
CodeIgniter Select Query :: $this->db->select_min()
$this->db->select_min('age');
$get_query = $this->db->get('tb_all_user');
//SELECT MIN(age) as age FROM tb_all_user
CodeIgniter Select Query :: $this->db->select_sum()
$this->db->select_sum('age');
$get_query = $this->db->get('tb_all_user');
// select query for SUM for age
//SELECT SUM(age) as age FROM tb_all_user
CodeIgniter Select Query ::$this->db->join()
hear Available joins types in mysql for left, right, outer, inner, left outer, and right outer in codeginiter.
$this->db->select('*');
$this->db->from('comments');
$this->db->join('tb_all_user', 'tb_all_user.id = comments.id');
$get_query = $this->db->get(); // select query // SELECT * FROM comments // for JOIN table // JOIN tb_all_user ON tb_all_user.id = comments.id
CodeIgniter Select Query ::$this->db->num_rows()
// select query
$select_query = "SELECT * FROM tb_all_user t limit 50";
$get_query = $this->db->query($select_query);
if(query->num_rows()){
// get data
}
CodeIgniter Select Query ::$this->db->num_fields()
The number of FIELDS (columns) returned by the query. Make sure to call the function using your query result object:
$get_query = $this->db->query('SELECT * FROM tb_all_user');
echo $get_query->num_fields();
CodeIgniter Select Query ::Query Result With result()
$select_query = "SELECT * FROM tb_all_user t limit 50";
$get_query = $this->db->query($select_query);
foreach($query- >result() as $row_val)
{
echo $row_val->name;
echo $row_val->age;
echo $row_val->groupname;
}
CodeIgniter Select Query ::Query Result With result_array()
$select_query = "SELECT * FROM tb_all_user t limit 50";
$get_query=$this->db->query($select_query);
foreach ($query->result_array() as $row_val)
{
echo $row_val['name'];
echo $row_val['gag'];
echo $row_val['groupname'];
}
CodeIgniter Select Query ::Query With Single Result()
$select_query = "SELECT * FROM tb_all_user t limit 50"; $get_query=$this->db->query($select_query); // fetch one row data $get_record = $query->row(); echo $get_record->name; echo $get_record->age; echo $get_record->groupname; // alternative method $get_record = $get_query->row_array(); echo $get_record['name']; echo $get_record['age']; echo $get_record['groupname'];
CodeIgniter Select Query ::Count Of Query Result
Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter.
echo $this->db->count_all('tb_all_user');
// Produces an integer, like 1523