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.

If you found this tutorial helpful then don't forget to share.

Related Post

Spread the love
  • 1
  •  
  •  
  •  
  •  
  •  

3 Comments

  1. Gurpreet Kaur Handa said:

    Thankyou very much ….its really very helpful…

    January 16, 2018
    Reply
  2. lolita said:

    thanku

    February 7, 2018
    Reply
  3. smudge said:

    thank u

    June 29, 2018
    Reply

Leave a Reply

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