The Codeigniter allows us to perform the database action like – select, insert, update, and delete with the minimal script.
The $this->db->update() method is used for update existing record. It takes table name and an array or object as the parameter.
In the demonstration, I am listing the user’s list with the edit option. Show the update form on edit option-click.
Contents
1. Table structure
I am using users table.
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `name` varchar(80) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Configuration
Navigate to application/config/database.php and define Database connection.
$db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'root', // Username 'password' => '', // Password 'database' => 'tutorial', // Database name 'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => FALSE, 'db_debug' => (ENVIRONMENT !== 'production'), 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE );
Default controller
Open application/config/routes.php and edit default_controller value to User.
$route['default_controller'] = 'User';
Load Database
To access the MySQL database require loading database library.
Open application/config/autoload.php and add the database in libraries array().
$autoload['libraries'] = array("database");
3. Model
Create a new application/models/Main_mode.php file.
Here, create 3 methods –
- getUsersList() – Fetch all users records and return response.
- getUserById() – Fetch record by user id and return response.
- updateUser() – Update user record using
update()method where pass Table name and an array of updation fields.
Completed Code
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Main_model extends CI_Model {
// Get all user list
function getUsersList(){
$this->db->select('*');
$this->db->order_by('name', 'asc');
$this->db->limit(10,0);
$q = $this->db->get('users');
$result = $q->result_array();
return $result;
}
// Get user by id
function getUserById($id){
$this->db->select('*');
$this->db->where('id', $id);
$q = $this->db->get('users');
$result = $q->result_array();
return $result;
}
// Update record by id
function updateUser($postData,$id){
$name = trim($postData['txt_name']);
$email = trim($postData['txt_email']);
if($name !='' && $email !='' ){
// Update
$value=array('name'=>$name,'email'=>$email);
$this->db->where('id',$id);
$this->db->update('users',$value)){
}
}
}
4. Controller
Create a new application/controllers/User.php file.
Define a single index() method –
- Load
Main_modeland checkeditvalue withget()method. - If
$editis notisset($edit)then get all users list by callinggetUsersList(). Loaduser_viewand pass response. - If
$editisisset($edit)then read selected user data and pass in the view. - On
<form>submit update record by callingupdateUser()method where pass POST data and$edit. Redirect the page withredirect()method.
Completed Code
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class User extends CI_Controller {
public function index(){
// load base_url
$this->load->helper('url');
//load model
$this->load->model('Main_model');
// Get
$edit = $this->input->get('edit');
if(!isset($edit)){
// get data
$data['response'] = $this->Main_model->getUsersList();
$data['view'] = 1;
// load view
$this->load->view('user_view',$data);
}else{
// Check submit button POST or not
if($this->input->post('submit') != NULL ){
// POST data
$postData = $this->input->post();
//load model
$this->load->model('Main_model');
// Update record
$this->Main_model->updateUser($postData,$edit);
// Redirect page
redirect('user/');
}else{
// get data by id
$data['response'] = $this->Main_model->getUserById($edit);
$data['view'] = 2;
// load view
$this->load->view('user_view',$data);
}
}
}
}
5. View
Create a new application/views/user_view.php file.
If $view value equals to 1 then display users list by looping on the $response.
Create a <form> with input elements to edit selected record. Fill the input element with value based on selected user $response when $view == 2.
Completed Code
<!doctype html>
<html>
<body>
<?php
// All users list
if(isset($view) && $view == 1) {
?>
<table border='1'>
<tr>
<td>S.no</td>
<td>Username</td>
<td>Name</td>
<td>Email</td>
<td> </td>
</tr>
<?php
$sno = 1;
foreach($response as $val){
echo '<tr>
<td>'.$sno.'</td>
<td>'.$val['username'].'</td>
<td>'.$val['name'].'</td>
<td>'.$val['email'].'</td>
<td><a href="'.site_url().'/user/index?edit='.$val['id'].'">Edit</a></td>
</tr>';
$sno++;
}
?>
</table>
<?php
}
// Edit user
if(isset($view) && $view == 2) {
?>
<form method='post' action=''>
<table>
<tr>
<td>Username</td>
<td><input type='text' name='txt_uname' readonly value='<?php echo $response[0]['username']; ?>' ></td>
</tr>
<tr>
<td>Name</td>
<td><input type='text' name='txt_name' value='<?php echo $response[0]['name']; ?>'></td>
</tr>
<tr>
<td>Email</td>
<td><input type='text' name='txt_email' value='<?php echo $response[0]['email']; ?>' ></td>
</tr>
<tr>
<td> </td>
<td><input type='submit' name='submit' value='Submit'></td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
6. Conclusion
Use $this->db->update() method to update records in your Database table which takes a table name and an array or object as a parameter. In the example, I used an array for update fields.
