Update records in Database Table with CodeIgniter

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.

Update records in Database Table with CodeIgniter


Contents

  1. Table structure
  2. Configuration
  3. Model
  4. Controller
  5. View
  6. Conclusion

 

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_model and check edit value with get() method.
  • If $edit is not isset($edit) then get all users list by calling getUsersList(). Load user_view and pass response.
  • If $edit is isset($edit) then read selected user data and pass in the view.
  • On <form> submit update record by calling updateUser() method where pass POST data and $edit. Redirect the page with redirect() 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>&nbsp;</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>&nbsp;</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.

Related Post

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *