CodeIgniter 3 Database Query
CodeIgniter 3 is one of the most amazing PHP frameworks. If you using CodeIgniter 3 framework for developing a web project, then you are in the right place. Let’s get started for all database queries like – select, delete, update, where, and join SQL query.
Select Quries
In CodeIgniter 3, you can use the following syntax to select data from a database table using the query builder:
$this->db->select(‘*’);
$this->db->from(‘table_name’);
$query = $this->db->get();
$result = $query->result();
You can also use the select()
method to specify the columns you want to select, and the get()
method to execute the query and retrieve the result. Additionally you can use different methods to limit, join, order etc before calling get()
method
example:
$this->db->select(‘column1, column2, column3’);
$this->db->from(‘table_name’);
$this->db->where(‘column1’, ‘value1’);
$this->db->limit(10, 20);
$query = $this->db->get();
$result = $query->result();
Update Quries
In CodeIgniter 3, you can use the following syntax to update data in a database table using the query builder:
$data = array(
‘column1’ => ‘value1’,
‘column2’ => ‘value2’
);
$this->db->where(‘id’, $id);
$this->db->update(‘table_name’, $data);
The above example updates the values of column1
and column2
where the value of the id
column matches the value of the $id
variable.
You can also use the set()
method to set the data you want to update and chain with where method as well like:
$this->db->set(‘column1’, ‘value1’);
$this->db->set(‘column2’, ‘value2’);
$this->db->where(‘id’, $id);
$this->db->update(‘table_name’);
Delete Quries
In CodeIgniter 3, you can use the following syntax to delete data from a database table using the query builder:
$this->db->where(‘id’, $id);
$this->db->delete(‘table_name’);
This code will delete all rows where the value of the id
column matches the value of the $id
variable.
You can also use multiple where conditions in the same delete statement like:
$this->db->where(‘column1’, ‘value1’);
$this->db->where(‘column2’, ‘value2’);
$this->db->delete(‘table_name’);
Please be careful when running delete statements as it will delete all the matching records, make sure to have a backup or test on a test table before running on a production table. Also make sure you are properly validating inputs and avoid SQL injection.
Join Quries
In CodeIgniter 3, you can use the join()
method to join multiple tables together in a query. Here is an example of how to join two tables together:
$this->db->select(‘*’);
$this->db->from(‘table1’);
$this->db->join(‘table2’, ‘table1.id = table2.id’);
$query = $this->db->get();
$result = $query->result();
This will return all columns from both tables, and only the rows where the value of the id
column in table1
matches the value of the id
column in table2
.
You can also use different types of join using join()
method like LEFT JOIN
, RIGHT JOIN
and INNER JOIN
.
$this->db->select(‘*’);
$this->db->from(‘table1’);
$this->db->join(‘table2’, ‘table1.id = table2.id’, ‘left’);
$query = $this->db->get();
$result = $query->result();
You can chain multiple join statements together to join multiple tables. Also, you can use different type of join in different statement.
$this->db->select(‘*’);
$this->db->from(‘table1’);
$this->db->join(‘table2’, ‘table1.id = table2.id’, ‘left’);
$this->db->join(‘table3’, ‘table2.id = table3.id’, ‘inner’);
$query = $this->db->get();
$result = $query->result();
Please note that while joining multiple tables it could be important to select specific columns only or use aliases to avoid naming conflicts.
Where Quries
In CodeIgniter 3, you can use the where()
method to add conditions to a query. Here is an example of how to use the where()
method:
$this->db->from(‘table_name’);
$this->db->where(‘column_name’, $value);
$query = $this->db->get();
$result = $query->result();
This will return all rows from the table_name
where the value of the column_name
column is equal to the value of the $value
variable.
You can also use multiple where()
statements in the same query to specify multiple conditions like:
$this->db->from(‘table_name’);
$this->db->where(‘column1’, $value1);
$this->db->where(‘column2’, $value2);
$query = $this->db->get();
$result = $query->result();
You can use different comparison operators like where('column_name' <,>,<=,>=, !=)
and also you can use or_where
instead of where
if you want to use OR condition.
$this->db->select(‘*’);
$this->db->from(‘table_name’);
$this->db->where(‘column1’, $value1);
$this->db->or_where(‘column2’, $value2);
$query = $this->db->get();
$result = $query->result();
At the End...
If you want to know about database, then click this link of official website – click
If you like this blog, comment me. You can find my YouTube channel – www.youtube.com/@yesuus