How to Update Data in Database using ID in CodeIgniter

How to update data in database using id in codeigniter?. Using codeigniter update query will let you perform update operation on database. In the previous post, I explained you about how to insert data into database using codeigniter and bootstrap. This tutorial will demonstrates you about updating database records in codeigniter.

CodeIgniter DB Update is going to be very easy as we have made required ground coding in CodeIgniter Database Insert tutorial. The update model, controller, and the view files will share some of the same features like insert along with fewer coding of its own.

Update Data in Database using ID in CodeIgniter

If you are a regular reader of Koding Made Simple, then you would have known, in most of my tutorials I use Twitter Bootstrap CSS Framework to design the front end. In this codeigniter database update tutorial too, I'm going to combine bootstrap with codeigniter to design the update form.

Setting up MySQL Database

For DB, I'm going to use the same employee database used in codeigniter insert tutorial. Run this sql file in MySQL to create the DB.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `tbl_department` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(80) NOT NULL,
  PRIMARY KEY (`department_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `tbl_department` (`department_id`, `department_name`) VALUES
(1, 'Finance'),
(2, 'HQ'),
(3, 'Operations'),
(4, 'Marketing'),
(5, 'Sales');

CREATE TABLE IF NOT EXISTS `tbl_designation` (
  `designation_id` int(4) NOT NULL AUTO_INCREMENT,
  `designation_name` varchar(50) NOT NULL,
  PRIMARY KEY (`designation_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `tbl_designation` (`designation_id`, `designation_name`) VALUES
(1, 'VP'),
(2, 'Manager'),
(3, 'Executive'),
(4, 'Trainee'),
(5, 'Senior Executive');

CREATE TABLE IF NOT EXISTS `tbl_employee` (
  `employee_id` int(4) NOT NULL AUTO_INCREMENT,
  `employee_no` int(6) NOT NULL,
  `employee_name` varchar(60) NOT NULL,
  `department_id` int(4) NOT NULL,
  `designation_id` int(4) NOT NULL,
  `hired_date` date NOT NULL,
  `salary` int(10) NOT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `employee_no` (`employee_no`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `tbl_employee` (`employee_id`, `employee_no`, `employee_name`, `department_id`, `designation_id`, `hired_date`, `salary`) VALUES
(1, 1001, 'Steve John', 1, 2, '2013-08-01', 60000);

The Model File ('models/employee_model.php')

<?php
/* 
 * File Name: employee_model.php
 */
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class employee_model extends CI_Model
{
    function __construct()
    {
        //Call the Model constructor
        parent::__construct();
    }

    //fetch employee record by employee no
    function get_employee_record($empno)
    {
        $this->db->where('employee_no', $empno);
        $this->db->from('tbl_employee');
        $query = $this->db->get();
        return $query->result();
    }
    
    //get department table to populate the department name dropdown
    function get_department()
    { 
        $this->db->select('department_id');
        $this->db->select('department_name');
        $this->db->from('tbl_department');
        $query = $this->db->get();
        $result = $query->result();

        //array to store department id & department name
        $dept_id = array('-SELECT-');
        $dept_name = array('-SELECT-');

        for ($i = 0; $i < count($result); $i++)
        {
            array_push($dept_id, $result[$i]->department_id);
            array_push($dept_name, $result[$i]->department_name);
        }
        return $department_result = array_combine($dept_id, $dept_name);
    }
    
    //get designation table to populate the designation dropdown
    function get_designation()     
    { 
        $this->db->select('designation_id');
        $this->db->select('designation_name');
        $this->db->from('tbl_designation');
        $query = $this->db->get();
        $result = $query->result();

        $designation_id = array('-SELECT-');
        $designation_name = array('-SELECT-');

        for ($i = 0; $i < count($result); $i++)
        {
            array_push($designation_id, $result[$i]->designation_id);
            array_push($designation_name, $result[$i]->designation_name);
        }
        return $designation_result = array_combine($designation_id, $designation_name);
    }
}
?>

Read: How to integrate Twitter Bootstrap 3 with PHP CodeIgniter Framework

As said earlier, the model file will be similar to the Codeigniter Bootstrap Insert example but with one additional method to fetch the employee record for the given employee number. Rest of the two methods get_department() and get_designation() will be the same as insert. These two methods are used to populate the drop down list in the update form.

The Controller File ('controllers/updateEmployee.php')

<?php
/* 
 * File Name: updateEmployee.php
 */
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class updateEmployee extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->library('session');
        $this->load->helper('form');
        $this->load->helper('url');
        $this->load->database();
        $this->load->library('form_validation');
        //load the employee model
        $this->load->model('employee_model');
    }
    
    //index function
    function index($empno)
    {
        $data['empno'] = $empno;

        //fetch data from department and designation tables
        $data['department'] = $this->employee_model->get_department();
        $data['designation'] = $this->employee_model->get_designation();

        //fetch employee record for the given employee no
        $data['emprecord'] = $this->employee_model->get_employee_record($empno);    

        //set validation rules
        $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|xss_clean|callback_alpha_only_space');
        $this->form_validation->set_rules('department', 'Department', 'callback_combo_check');
        $this->form_validation->set_rules('designation', 'Designation', 'callback_combo_check');
        $this->form_validation->set_rules('hireddate', 'Hired Date', 'required');
        $this->form_validation->set_rules('salary', 'Salary', 'required|numeric');

        if ($this->form_validation->run() == FALSE)
        {   
            //fail validation
            $this->load->view('update_employee_view', $data);
        }
        else
        {
            //pass validation
            $data = array(
                'employee_name' => $this->input->post('employeename'),
                'department_id' => $this->input->post('department'),
                'designation_id' => $this->input->post('designation'),
                'hired_date' => @date('Y-m-d', @strtotime($this->input->post('hireddate'))),
                'salary' => $this->input->post('salary'),
            );

            //update employee record
            $this->db->where('employee_no', $empno);
            $this->db->update('tbl_employee', $data);

            //display success message
            $this->session->set_flashdata('msg', '<div class="alert alert-success text-center">Employee Record is Successfully Updated!</div>');
            redirect('updateEmployee/index/' . $empno);
        }
    }
    
    //custom validation function for dropdown input
    function combo_check($str)
    {
        if ($str == '-SELECT-')
        {
            $this->form_validation->set_message('combo_check', 'Valid %s Name is required');
            return FALSE;
        }
        else
        {
            return TRUE;
        }
    }

    //custom validation function to accept only alpha and space input
    function alpha_only_space($str)
    {
        if (!preg_match("/^([-a-z ])+$/i", $str))
        {
            $this->form_validation->set_message('alpha_only_space', 'The %s field must contain only alphabets or spaces');
            return FALSE;
        }
        else
        {
            return TRUE;
        }
    }
}
?>

Read: How to Create Login Form in CodeIgniter, MySQL and Twitter Bootstrap

Create the controller file and load the necessary codeigniter libraries along with employee model. Here we update the employee records based on employee number field. Pass it as a url parameter when we call the controller like this,

http://yourdomain.com/ci-demo/index.php/updateEmployee/index/1001

The last uri segment '1001' is the argument to the controller index() function.

Pre Populate the CodeIgniter Update Form with Database Data

Next call back all the three model methods get_department(), get_designation(), get_employee_record() and store them in the data array to pass it to the view file.

Add Form Validation to CodeIgniter Update Form

Next set the validation rules for the form input fields. I have disabled the employee number field to restrict the user from editing it. So no validation for that field.

Use CodeIgniter Update Query to Edit Database Record

Next run the validation rule on the submitted values and display the error message on failed validation. Else move the post data into an array and use codeigniter update query statement, $this->db->update() to update the particular employee record provided by $this->db->where() statement.

Upon successful db update, we notify the user by displaying a message using set_flashdata() method.

The View File ('views/update_employee_view.php')

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CodeIgniter Update Database Demo | CodeIgniter Update Query</title>
    <!--link the bootstrap css file-->
    <link href="<?php echo base_url("assets/bootstrap/css/bootstrap.css"); ?>" rel="stylesheet" type="text/css" />
    <!-- link jquery ui css-->
    <link href="<?php echo base_url('assets/jquery-ui-1.11.2/jquery-ui.min.css'); ?>" rel="stylesheet" type="text/css" />
    <!--include jquery library-->
    <script src="<?php echo base_url('assets/js/jquery-1.10.2.js'); ?>"></script>
    <!--load jquery ui js file-->
    <script src="<?php echo base_url('assets/jquery-ui-1.11.2/jquery-ui.min.js'); ?>"></script>
        
    <style type="text/css">
    .colbox {
        margin-left: 0px;
        margin-right: 0px;
    }
    </style>
    
    <script type="text/javascript">
    //load datepicker control onfocus
    $(function() {
        $("#hireddate").datepicker();
    });
    </script>
    
</head>
<body>
<br>

<div class="container">
    <div class="row">
        <div class="col-md-6 col-md-offset-3 well">
        <legend>CodeIgniter Update Database Demo</legend>
        <?php 
        $attributes = array("class" => "form-horizontal", "id" => "employeeform", "name" => "employeeform");
        echo form_open("updateEmployee/index/" . $empno, $attributes);?>
        <fieldset>
            
            <div class="form-group">
            <div class="row colbox">
            
            <div class="col-md-4">
                <label for="employeeno" class="control-label">Employee Number</label>
            </div>
            <div class="col-md-8">
                <input id="employeeno" name="employeeno" placeholder="employeeno" type="text" disabled="disabled" class="form-control"  value="<?php echo $emprecord[0]->employee_no; ?>" />
                <span class="text-danger"><?php echo form_error('employeeno'); ?></span>
            </div>
            </div>
            </div>

            <div class="form-group">
            <div class="row colbox">
            <div class="col-md-4">
                <label for="employeename" class="control-label">Employee Name</label>
            </div>
            <div class="col-md-8">
                <input id="employeename" name="employeename" placeholder="employeename" type="text" class="form-control"  value="<?php echo set_value('employeename', $emprecord[0]->employee_name); ?>" />
                <span class="text-danger"><?php echo form_error('employeename'); ?></span>
            </div>
            </div>
            </div>
            
            <div class="form-group">
            <div class="row colbox">
            <div class="col-md-4">
                <label for="department" class="control-label">Department</label>
            </div>
            <div class="col-md-8">
            
                <?php
                $attributes = 'class = "form-control" id = "department"';
                echo form_dropdown('department',$department,set_value('department', $emprecord[0]->department_id),$attributes);?>
                <span class="text-danger"><?php echo form_error('department'); ?></span>
            </div>
            </div>
            </div>

            <div class="form-group">
            <div class="row colbox">
            <div class="col-md-4">
                <label for="designation" class="control-label">Designation</label>
            </div>
            <div class="col-md-8">
            
                <?php
                $attributes = 'class = "form-control" id = "designation"';
                echo form_dropdown('designation',$designation, set_value('designation', $emprecord[0]->designation_id), $attributes);?>
                
                <span class="text-danger"><?php echo form_error('designation'); ?></span>
            </div>
            </div>
            </div>
            
            <div class="form-group">
            <div class="row colbox">
            <div class="col-md-4">
                <label for="hireddate" class="control-label">Hired Date</label>
            </div>
            <div class="col-md-8">
                <input id="hireddate" name="hireddate" placeholder="hireddate" type="text" class="form-control"  value="<?php echo set_value('hireddate', @date('d-m-Y', @strtotime($emprecord[0]->hired_date))); ?>" />
                <span class="text-danger"><?php echo form_error('hireddate'); ?></span>
                
            </div>
            </div>
            </div>
            
            <div class="form-group">
            <div class="row colbox">
            <div class="col-md-4">
                <label for="salary" class="control-label">Salary</label>
            </div>
            <div class="col-md-8">
                <input id="salary" name="salary" placeholder="salary" type="text" class="form-control"  value="<?php echo set_value('salary', $emprecord[0]->salary); ?>" />
                <span class="text-danger"><?php echo form_error('salary'); ?></span>
            </div>
            </div>
            </div>
            
            <div class="form-group">
            <div class="col-sm-offset-4 col-md-8 text-left">
                <input id="btn_update" name="btn_update" type="submit" class="btn btn-primary" value="Update" />
                <input id="btn_cancel" name="btn_cancel" type="reset" class="btn btn-danger" value="Cancel" />
            </div>
            </div>
        </fieldset>
        <?php echo form_close(); ?>
        <?php echo $this->session->flashdata('msg'); ?>
        </div>
    </div>
</div>
</body>
</html>

The codeigniter view contains the update form. Here we receive employee record fetched from the database and populate the form fields on loading. After the user submits the edited form, on successful form field validation we update the database record using codeigniter update query.

To populate the update form field values from database on load, I have passed them as second parameter to set_value() functions. Which initially will load the database value and in case of validation error, it will re-populate the user submitted values to the form fields.

Read Also:

• CodeIgniter Database CRUD - Insert
• CodeIgniter Database CRUD - Read
• CodeIgniter Database CRUD - Delete

That explains about updating data from database using id in codeigniter and bootstrap frameworks. I hope you find this PHP Codeigniter Database Update Tutorial useful.