CodeIgniter – Select Query

CodeIgniter ‘SELECT’ query will execute using following functions. They are

$this->db->query()

To submit a query, use the following function:

$this->db->query('YOUR QUERY HERE');

place raw SQL in the query() method

$query = $this->db->query("select * from tbl_user");

Pass SQL query in a variable

$sql = "select * from tbl_user";
$query = $this->db->query($sql);

$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

$sql = "SELECT * FROM tbl_user WHERE name = ? AND type = ?"; 
$this->db->query($sql, array('code', 'php'));

Active Record Query

$this->db->get()

Use the built-in get() method

$query = $this-> db-> get('users'); 
$query = $this-> db-> get('employees');  

$this->db->get() Select The Fields

Format 1

$this->db->select('name'); 
$query = $this-> db-> get('tbl_user'); 

Format 2

$this->db->select('name')->from('tbl_user')->get();
//SELECT `name` FROM ('tbl_user')

$this->db->get() With Limit

Program 1

$limit=10;
$query = $this-> db-> get('tbl_user',$limit); 

Program 2

$this->db->select('id, name');
$this->db->from('tbl_user');
$this->db->limit(1);
$query = $this-> db-> get();
//select id,name from tbl_user limit 1;

$this->db->get() With Offset,Limit

$limit	=10;
$offset	=20;
$query = $this-> db-> get('tbl_user',$offset,$limit); 
///select * from tbl_user limit 10, 20

$this->db->get() With select, from

$this->db->select('id, name');
$this->db->from('tbl_user');
$query = $this->db->get();
//SELECT `id`, `name` FROM (`tbl_user`) LIMIT 10, 20;

$this->db->get() With Where, Or_Where, Where_In, or_where_in, where_not_in

Program 1

$this->db->select('username');
$this->db->from('tbl_user');
$this->db->where('userid',11);
$this->db->where("usertype","admin");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `userid` = 11 AND `usertype` = 'admin'

Program 2

$this->db->select('username');
$this->db->from('tbl_user');
$this->db->where('userid',11);
$this->db->where("usertype !=","admin");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `userid` = 11 AND `usertype` != 'admin'

Program 3:

$this->db->select('username');
$this->db->from('tbl_user');
$this->db->where("userid > 11 AND usertype='user'");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `userid` > 11 AND usertype='user'

Program 4:

$this->db->from('tbl_user');
$this->db->where('username !=', $name);
$this->db->or_where('userid >', $userid); 
//SELECT * FROM (`tbl_user`) WHERE username != 'admin' OR userid > 5

Program 5:

$names = array('name1', 'name2', 'name3');
$this->db->from('tbl_user');
$this->db->where('status !=', $status);
$this->db->or_where_in('username', $names);
//SELECT * FROM (`tbl_user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')

Program 6:

$names = array('name1', 'name2', 'name3');
$this->db->from('tbl_user');
$this->db->where_not_in('username', $names);
//SELECT * FROM (`tbl_user`) WHERE username NOT IN ('name1', 'name2', 'name3')

Program 7:

$names = array('name1', 'name2', 'name3');
$this->db->from('tbl_user');
$this->db->where('status !=', $status);
$this->db->or_where_not_in('username', $names);
//SELECT * FROM (`tbl_user`) WHERE status='active' OR username NOT IN ('name1', 'name2', 'name3')

$this->db->get_where()

Use the built-in get() method

$status='admin';
$options=array('usertype'=>$status);
$query =  $this->db->get_where('tbl_user',$options);
//get_where with limit
$limit=10;
$query =  $this->db->get_where('tbl_user',$options,$limit);

$this->db->get() With Like, or_like, not_like, or_not_like

Program 1:

$this->db->select('username');
$this->db->from('tbl_user');
$this->db->like("username","code");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `username` LIKE '%code%'

Program 2:

$this->db->select('username,userid');
$this->db->from('tbl_user');
$this->db->like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`tbl_user`) WHERE `username` LIKE '%code%' OR `usertype` LIKE '%admin%'

Program 3:

$this->db->select('username,userid');
$this->db->from('tbl_user');
$this->db->not_like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`tbl_user`) WHERE `username` NOT LIKE '%code%' OR `usertype` LIKE '%admin%'

Program 4:

$this->db->select('username,userid');
$this->db->from('tbl_user');
$this->db->where('status','active');
$this->db->or_not_like('username','code');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`tbl_user`) WHERE `status` = 'active' OR `username` NOT LIKE '%code%'

$this->db->get() With group_by

$this->db->select('*');
$this->db->from('tbl_user');
$this->db->group_by("states"); 
//SELECT * FROM (`tbl_user`) group by states

$this->db->get() With having

$this->db->select('*');
$this->db->from('tbl_user');
$this->db->having("states=1"); 
//SELECT * FROM (`tbl_user`) HAVING states=1

$this->db->get() With Order BY

Program 1:

$this->db->select('username');
$this->db->from('tbl_user');
$this->db->order_by('username');
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) ORDER BY `username`

Program 2:

$this->db->select('username');
$this->db->from('tbl_user');
$this->db->order_by('username','desc');
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) ORDER BY `username` desc

Mathematics Function

$this->db->select_max()

$this->db->select_max('age');
$query = $this->db->get('tbl_user');
//SELECT MAX(age) as age FROM tbl_user

$this->db->select_max('age', 'userage');
$query = $this->db->get('tbl_user');
//SELECT MAX(age) as userage FROM tbl_user

$this->db->select_min()

$this->db->select_min('age');
$query = $this->db->get('tbl_user');
//SELECT MIN(age) as age FROM tbl_user

$this->db->select_sum()

$this->db->select_sum('age');
$query = $this->db->get('tbl_user');
//SELECT SUM(age) as age FROM tbl_user

$this->db->join()

Available joins types in mysql: left, right, outer, inner, left outer, and right outer

$this->db->select('*');
$this->db->from('comments');
$this->db->join('tbl_user', 'tbl_user.id = comments.id');

$query = $this->db->get();
// SELECT * FROM comments
// JOIN tbl_user ON tbl_user.id = comments.id

$this->db->num_rows()

$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);
if(query->num_rows()){
	// work process
}

$this->db->num_fields()

The number of FIELDS (columns) returned by the query. Make sure to call the function using your query result object:

$query = $this->db->query('SELECT * FROM tbl_user');
echo $query->num_fields();

Query Result With result()

$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);
foreach ($query- >result() as $row)
{  
	echo $row- > name;  
	echo $row- > age;  
	echo $row- > groupname; 
}            

Query Result With result_array()

$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);

foreach ($query->result_array() as $row)
{  
	echo $row['name'];
	echo $row['gag'];
	echo $row['groupname'];
}

Query With Single Result()

$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);
// fetch one row data
$record=$query->row();
echo $record->name;
echo $record->age;
echo $record->groupname;
// alternative method 
$record=$query->row_array();
echo $record['name'];
echo $record['age'];
echo $record['groupname'];

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('tbl_user');
// Produces an integer, like 1522