CodeIgniter and Bootstrap: Update Database from Form with Validations

CodeIgniter and Bootstrap: Update Database from Form with Validations

Using CodeIgniter Update Database Query will let you perform update operation on database. In the previousCodeIgniter Database Tutorial, I explained you How to Insert Data into Database using CodeIgniter and Bootstrap. This tutorial will demonstrates you about updating Database records in CodeIgniter.
Bootstrap-CodeIgniter-Update-Database-Example
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.
Recommended Read: How to integrate Twitter Bootstrap 3 with PHP CodeIgniter Framework
Recommended Read: How to Create Login Form in CodeIgniter, MySQL and Twitter Bootstrap

Creating CodeIgniter Bootstrap Update Form

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);
    }
}
?>
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;
        }
    }
}
?>
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.
CodeIgniter-Update-Database-Form-Validation-Error
Upon successful db update, we notify the user by displaying a message using set_flashdata() method.
CodeIgniter-Update-Query-Success-Message

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>

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)