Codeigniter select query with multiple clause – Today we would love to share with how to use multiple clause with select query in codeigniter.
Codeigniter provides many functions for select data for database. You will learn most important and basic functions for geting a data on database using multiple clause.
Codeigniter Select Queries
query() Function
Syntax :
$this->db->query('YOUR QUERY HERE');
Example :
$query = $this->db->query("select * from users");
get() Function
Using the get() function, it will get all the rows with all the fields from the selected database table.
Syntax :
$this->db->get();
Example :
$query = $this-> db-> get('users');
select() Funciton
The select() function is used to select a sepefic column from database table.
Syntax :
$this->db->select('column1,column2,column3');
Example :
$this->db->select('name');
$query = $this->db->get('users');
limit() Function
The limit() function is used to specify the number of records to return from database.
Syntax :
$this->db->limit(int_value);
Example :
$this->db->select('id,name');
$this->db->limit(10);
$query = $this->db->get('users');
from() Function
Syntax :
$this->db->from(table_name);
Example :
$this->db->select('id,name');
$this->db->from('users');
$query = $this->db->get();
where() Function
Syntax :
$this->db->where(condition);
Example :
$this->db->select('id,name');
$this->db->from('users');
$this->db->where('id',5)
$query = $this->db->get();
where_in() Function
Syntax :
$this->db->where_in('condition');
Example :
$this->db->from('users');
$this->db->where_in('id', [1,2,3]);
$this->db->get();
or_where() Function
Syntax :
$this->db->or_where('condition');
Example :
$this->db->from('users');
$this->db->where('username !=', 'test');
$this->db->or_where('id >', $id);
or_where_in() Function
Syntax :
$this->db->or_where_in(condition);
Example :
$id = array('1', '3', '5');
$this->db->from('users');
$this->db->where('status !=', 'active');
$this->db->or_where_in('id', $id);
where_not_in() Function
Syntax :
$this->db->where_not_in(condition);
Example :
$id = array('1', '3', '5');
$this->db->from('users');
$this->db->where('status !=', 'active');
$this->db->where_not_in('id', $id);
or_where_not_in() Function
Syntax :
$this->db->or_where_not_in(condition);
Example :
$id = array('1', '3', '5');
$this->db->from('users');
$this->db->where('status !=', 'active');
$this->db->or_where_not_in('id', $id);
get_where() Function
Syntax :
$this->db->get_where(condition);
Example :
$id = array('1', '3', '5');
$query = $this->db->get_where('users', array('id' => $id));
like() Function
Syntax :
$this->db->like(condition);
Example :
$this->db->select('name');
$this->db->from('users');
$this->db->like("name","tutsmake");
$query=$this->db->get();
or_like() Function
Syntax :
$this->db->or_like(condition);
Example :
$this->db->select('name');
$this->db->from('users');
$this->db->like('name','tutsmake');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
not_like() Function
Syntax :
$this->db->not_like(condition);
Example :
$this->db->select('*');
$this->db->from('users');
$this->db->not_like('name','tutsmake');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
or_not_like() Function
Syntax :
$this->db->not_like(condition);
Example :
$this->db->select('*');
$this->db->from('users');
$this->db->or_not_like('name','tutsmake');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
group_by() Function
Syntax :
$this->db->group_by(column_name);
Example :
$this->db->select('*');
$this->db->from('users');
$this->db->group_by("city_id");
having() Function
Syntax :
$this->db->having(condition);
Example :
$this->db->select('*');
$this->db->from('users');
$this->db->having('id', 50);
order_by() Function
Syntax :
$this->db->order_by(column_name,optional);
Example :
$this->db->select('*');
$this->db->from('users');
$this->db->order_by('id',DESC);
$query=$this->db->get();
select_max() Function
Syntax :
$this->db->select_max('column');
Example :
$this->db->select_max('age');
$query = $this->db->get('users');
select_min() Function
Syntax :
$this->db->select_min('column');
Example :
$this->db->select_min('age');
$query = $this->db->get('users');
select_sum() Function
Syntax :
$this->db->select_sum('column');
Example :
$this->db->select_sum('age');
$query = $this->db->get('users');
count_all() Function
Syntax :
$this->db->count_all('table_name');
Example :
$this->db->count_all('users');
count_all_results() Function
Syntax :
$this->db->count_all_results();
Example :
$this->db->from('users');
$this->db->count_all_results();
join() Function
Syntax :
$this->db->join();
Example :
$this->db->select('*');
$this->db->from('product');
$this->db->join('category', 'category.id = product.category_id');
left join() Function
Syntax :
$this->db->join();
Example :
$this->db->select('*');
$this->db->from('product');
$this->db->join('category', 'category.id = product.category_id','left');
good job