Tag Archives: codeigniter tutorials
Codeigniter join table alias
Codeigniter join table alias – Codeigniter Database Class is used to join tables in coddiegniter. $this->db->join(); is used to join tables in codeigniter. It is very simple to join tables using alias name. Here in this tutorial, we are going to explain how you can use table alias to join tables in CI.
Codeigniter join table alias
Here is simple example of join tables using alias name. We have taken alias name for users table as u, profile_image as pi, city as c, post as p and friends as f.
$this->db->select('u.id,u.name,pi.image,p.post,f.id as fid');
$this->db->from('users as u');
$this->db->join('profile_image as pi ', 'pi.user_id = u.id');
$this->db->join('city as c', 'c.user_id = u.id','left');
$this->db->join('post as p ', 'p.user_id = u.id','left');
$this->db->join('friends as f ', 'f.user_id = u.id','left');
$this->db->where('u.id', $id);
$query = $this->db->get();
The above example will produce output something like this-
// Select u.id,u.name,pi.image,p.post,f.id as fid from users
join profile_image as pi on profile_image.user_id = users.id
left join city as c on c.user_id = u.id
left join post as p on p.user_id = u.id
left join friends as f on f.user_id = u.id
where u.id = $id
Codeigniter join multiple tables
$this->db->join(); is used to join tables in codeigniter.
Codeigniter join Multiple tables
$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$this->db->join('city', 'city.user_id = users.id','left');
$this->db->join('post', 'post.user_id = users.id','left');
$this->db->join('friends', 'friends.user_id = users.id','left');
$this->db->where('users.id', $id);
$query = $this->db->get();
Will produce
// Select *from users
join profile_image on profile_image.user_id = users.id
left join city on city.user_id = users.id
left join post on post.user_id = users.id
left join friends on friends.user_id = users.id
where users.id = $id
Codeigniter join with multiple conditions
$this->db->join(); is used to join tables in codeigniter.
Codeigniter join with multiple conditions
$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', ' ( profile_image.user_id = users.id AND profile_image.is_active="1" )');
$this->db->join('city', 'city.user_id = users.id','left');
$this->db->where('users.id', $id);
$query = $this->db->get();
Will produce
// Select *from users join profile_image
on ( profile_image.user_id = users.id AND profile_image.is_active=”1″ )
left join city on city.user_id = users.id where users.id = $id
Codeigniter join 3 tables
Codeigniter join 3 tables It is very simple to join multiple tables in codeigniter using query builder class, $this->db->join(); is used to join two or more than two tables in codeigniter. Here in this article we are going to explain how you can join three tables in Codeigniter.
Codeigniter join 3 tables
Here is an example to join three tables – users, profile_image and city.
$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$this->db->join('city', 'city.user_id = users.id','left');
$this->db->where('users.id', $id);
$query = $this->db->get();
On the same way you can join two more than two table in codeigniter.
You can check more details about the query builder class in codeigniter here – Database Query Builder Class Or Main Documentation
The above example will generate the below query-
// Select *from users join profile_image on profile_image.user_id = users.id left join city on city.user_id = users.id where users.id = $id
Codeigniter left join query | Active Records
Codeigniter left join query : $this->db->join(table_name, condition, ‘left’); is used for left join in codeigniter. We can left join as many tables using the same syntax. Here in this article, we are going to explain how you can use left join using Codeigniter Database standard.
Codeigniter left join query | Active Records example
Here in this we have joined two tables users and profile_image and we have performed join to get data collectively.
Example 1
$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id','left');
$this->db->where('users.id', $id);
$query = $this->db->get();
The above query will produce the following Query-
// Select *from users left join profile_image on profile_image.user_id = users.id where users.id = $id
Codeigniter left join query
Example 2
Here is another example of left join in Active Records.
$this->db->select('o.id, o.grand_total, o.sub_total, u.name');
$this->db->from('users u');
$this->db->join('sales_order o', 'o.user_id = u.id', 'left');
$this->db->where('o.user_id', 100);
$query = $this->db->get();
Codeigniter joins example
$this->db->join(); is used to join tables in codeigniter.
Codeigniter joins example
$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$query = $this->db->get();
Will produce
// Select *from users join profile_image on profile_image.user_id = users.id
More Codeigniter joins example
$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$this->db->where('users.id', $id);
$query = $this->db->get();
Will produce the query
// Select *from users join profile_image on profile_image.user_id = users.id where users.id = $id
Codeigniter get base url
base_url() returns the base url in codeigniter.
Codeigniter get base url
Helper required for base_url() in php.
Load Helper as following.
$this->load->helper('url');
or You can autoload helper url in autoload.php
as :
$autoload['helper'] = array('url');
Now Get the base_url() as :
Codeigniter get base url – Example
echo base_url(); |
Codeigniter Delete Query Example
Codeigniter delete query example : Codeigniter inbult query bulder $this->db->delete() is used to delete the data from table. Codeigniter also provides $this->db->empty_table() and $this->db->truncate() method to delete all data from table. $this->db->delete() deletes data based on where condition, it will delete the rows where condition is fullfilled. Let us understand how we can use these query builders to delete data from tables.
Delete data in Codeigniter | Truncate Table | Remove All Data
$this->db->delete() is used to delete data in codeigniter table.
Codeigniter delete query example
Let us go one by one to understand the delete functionality in Codeigniter.
Delete With Where Condition
You can delete the data based on condition using the below example-
$where_array = array(
'id'=>$id
);
$this->db->delete('table_name', $where_array);
or You can also use this :
$where_array = array(
'id'=>$id
);
$this->db->where($where_array);
$this->db->delete('table_name');
This will generate the following Query :
// Delete from table_name WHERE id = $id
Using where condition you can delete Multiple Rows from table.
Empty Table | Delete All Rows
You can use the following syntax to empty any table. It will delete all rows from table.
$this->db->empty_table('users_table');
It will produce query like this – DELETE from users_table
Note: – It will delete all records from table so before running the above query make sure you really want to delete all rows.
Truncate Table
$this->db->truncate('users_table');
It will produce query like this – TRUNCATE users_table;
Note: – It will truncate table & remove all indexes as well so make sure you really want to truncate table.
Codeigniter insert Query example
Codeigniter Insert Query Example :
Insert data in Codeigniter
$this->db->insert() is used to insert data in codeigniter table.
Codeigniter Insert Query Example
$data = array(
'id' => $id,
'name' => $name,
'email' => $email,
'phone' => $phone
);
$this->db->insert('table_name', $data);
This will generate the following Query :
// INSERT INTO table_name (id,name,email,phone)values($id,$name,$email,$phone);
