CodeIgniter Update Query Example

Codeigniter Update Query Example: This tutorial will show you how to update data in database in codeigniter. Update is one among the CRUD function and a primary process performed on any database system. It is used to update/edit the existing records (entity) in database. Database update can be performed on single or batch level which allows you to update single record one at a time or multiple records all-at-once in the database.

Also check for codeigniter insert query example and codeigniter delete query example.

CodeIgniter Update Query Example:

To write update query in codeigniter you have to use 'active record class' library which allows you to write database independent queries and later they are converted into query strings by the respective database adapter and executed.

In code igniter you should make of use the function $this->db->update() to update records/rows in database.

$this->db->update('table_name', data);

This function generates update string with the data provided through parameters and executes the query. It's first parameter holds the table name and the second one contains an array or object of values used for updating db.

MySQL Table: Employees

Here we'll see some of the query examples using this 'Employees' table that'll show you how to update data in database in codeigniter.

CodeIgniter Update Query with Where Clause Example:

This update query in codeigniter uses where condition/clause to edit only a specific record/row from the 'Employees' table.

$data = array(
    'Designation' => 'Senior Accountant',
    'Salary' => 192300
);
$this->db->where('EmpID', 3);
$this->db->update('Employees', $data);

// Produces Update Query:
// UPDATE Employees SET Designation = 'Senior Accountant', Salary = 192300 WHERE EmpID = 3;

Resultset:

While writing update query in codeigniter, you can either use where clause separately with where() function like above or just pass it as a string to the $this->db->update() function itself. Here is the example for the latter.

$data = array(
    'Designation' => 'Senior Accountant',
    'Salary' => 192300
);
$this->db->update('Employees', $data, 'EmpID = 3');

// Produces Update Query String:
// UPDATE Employees SET Designation = 'Senior Accountant', Salary = 192300 WHERE EmpID = 3;

Update Query with Multiple Conditions in Codeigniter:

This code igniter update query utilizes multiple where conditions to update a particular employee record.

$data = array(
    'Designation' => 'Office Manager'
);
$this->db->where('DeptName', 'HQ');
$this->db->where('Designation', 'Manager');
$this->db->update('Employees', $data);

// Produces SQL:
// UPDATE Employees SET Designation = 'Office Manager' WHERE DeptName = 'HQ' and Designation = 'Manager';

Update Multiple Rows in Codeigniter:

Unlike using multiple where conditions to update a single database row, you can also update multiple rows at once in codeigniter. You must use $this->db->update_batch() function instead of update() to do this.

The function update_batch() is more or like similar to update() except for the one additional parameter 'where_key' that tells which column should be used for where condition.

$data = array(
    array(
        'EmpID' => 6
        'Designation' => 'Sales Manager',
        'Salary' => 167260
    ),
    array(
        'EmpID' => 8
        'Designation' => 'Technical Lead',
        'Salary' => 382500
    )
);

$this->db->update_batch('Employees', $data, 'EmpID');

// Produces Update Query SQL:
// UPDATE Employees SET Designation = CASE
// WHEN EmpID = 6 THEN 'Sales Manager'
// WHEN EmpID = 8 THEN 'Technical Lead'
// ELSE Designation END,
// Salary = CASE
// WHEN EmpID = 6 THEN 167260
// WHEN EmpID = 8 THEN 382500
// ELSE Salary END
// WHERE EmpID IN (6, 8)

Resultset:

As you can see, the above query takes up 'EmpID' for where condition and updates multiple records at once based on it.

Related Read: Update Form Data in database with Codeigniter & MySQL

That explains about codeigniter update query example. With the help of these queries you can easily update data in database using php codeigniter and mysql.

Tweet