CodeIgniter Bootstrap: Insert Form Data into Database

On 1/02/2015

Hi, this CodeIgniter Bootstrap Tutorial will teach you How to Insert Form Data into MySQL Database using CodeIgniter, Bootstrap CSS framework and jQuery UI. I'm going to walk you through step-by-step, creating a form in codeigniter and insert those form data into database. To design the said php codeigniter form, we use twitter boostrap css framework and it's one of my all time favorite and saves time from creating style sheets of our own. If you wonder how to use bootstrap in codeigniter, then read this tutorial on Integrating Bootstrap with CodeIgniter.

MySQL Database Example

I'm going to use mysql database as an example for this tutorial. Here, take a look at the sample employee database I have.

mysql-employee-database-structure-example

The fields that are highlighted in yellow color are primary keys and those ones in green are foreign keys.

Now run this sql query in mysql to create the above database.

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`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Recommended Read: How to Connect to Multiple Databases in CodeIgniter

The Model ('models/employee_model.php')

First create the model file 'employee_model.php' with two functions. One is to fetch all the records from department table and other one to fetch all the records from the designation table.

<?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();
    }

    //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);
    }
}
?>

Populate the Drop Down List from Database in CodeIgniter Form

If you wonder why we need to fetch data from the above said tables, well the employee table consists of two dependant fields department_id and designation_id for which their values should be derived from tbl_department and tbl_designation respectively. So in our codeigniter form, we should use dropdown list pre-populated with the list of available department and designation names like this.

codeigniter-form-populate-dropdown-from-database

The Controller ('controllers/employee.php')

Next create the controller file 'employee.php' and load the required libraries and helpers.

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

class employee 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');
}
?>

We require the 'database' library as we have to connect and fetch and insert data into database. Also loaded the session library for displaying notification. Learn more about using codeigniter sessions in our tutorial create login form in codeigniter and bootstrap.

Note: When developing a complete codeigniter application, you have to load the session library in each and every controller module for which you want only the logged in user to access.

Next add index() function which is the default function of any codeigniter controllers. As I have mentioned earlier we have to populate the drop down list in the view with values from database. It's time to callback our employee model functions to fetch the department and designation table data.

<?php
//index function
function index()
{
    ...

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

Add CodeIgniter Form Validation Rules

Next we have to validate the form input data posted by the user for database insert. Using the codeigniter form validation library, let's set the required validation rules for each and every field present in our employee form.

<?php
//index function
function index()
{
    ...

    //set validation rules
    $this->form_validation->set_rules('employeeno', 'Employee No', 'trim|required|numeric');
    $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');
}
?>

Add Custom Form Validation Callback in CodeIgniter

Next create two codeingiter custom validation callback functions, one to make sure the user selects valid department and designations in the drop down list and the other one to restrict the employee name field to contain only alphabets and space.

<?php
//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;
    }
}
?>

Run Form Validation on CodeIgniter Bootstrap Form Data

Next we run the form validation on the form data we received upon submission. If the submitted form contains valid data then we insert the form data into database else display the error message in the codeigniter view. Add this code to controller's index() function.

<?php
//index function
function index()
{
    ...
    
    if ($this->form_validation->run() == FALSE)
    {
        //fail validation
        $this->load->view('employee_view', $data);
    }
    else
    {
        //pass validation
        $data = array(
            'employee_no' => $this->input->post('employeeno'),
            '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'),
        );

        //insert the form data into database
        $this->db->insert('tbl_employee', $data);

        //display success message
        $this->session->set_flashdata('msg', '<div class="alert alert-success text-center">Employee details added to Database!!!</div>');
        redirect('employee/index');
    }
}
?>

As you can see in the above code, codeigniter provides very easy way of running form validations. Upon setting the validation rules we have to run $this->form_validation->run() statement, which will return true if all our validation rule passes else returns false. In case of failure, we reload the form and display the corresponding error messages in the view.

On successful validation check, we get all the form field values in an array and insert into database using the codeigniter statement, $this->db->insert('tbl_employee', $data). The first parameter of the insert() function should be the database table name and the second parameter is of an array of field name and their values. As you can see above, I have converted the date format into 'Y-m-d' with the date() function because mysql uses the very same date format. If you try to insert date in some other format, then the provided date will not be properly inserted into mysql.

After inserting into database we should display some sort of notification for which we use $this->session->set_flashdata(). The flashdata() will display the message immediately after page redirection.

Here is the complete code for the controller file.
<?php
/* 
 * File Name: employee.php
 */
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class employee 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()
    {
        //fetch data from department and designation tables
        $data['department'] = $this->employee_model->get_department();
        $data['designation'] = $this->employee_model->get_designation();

        //set validation rules
        $this->form_validation->set_rules('employeeno', 'Employee No', 'trim|required|numeric');
        $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('employee_view', $data);
        }
        else
        {    
            //pass validation
            $data = array(
                'employee_no' => $this->input->post('employeeno'),
                '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'),
            );

            //insert the form data into database
            $this->db->insert('tbl_employee', $data);

            //display success message
            $this->session->set_flashdata('msg', '<div class="alert alert-success text-center">Employee details added to Database!!!</div>');
            redirect('employee/index');
        }

    }
    
    //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;
        }
    }
}
?>

The View ('views/employee_view.php')

The codeigniter view is the interface between the application and the user and contains the html markup for our employee form.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CodeIgniter | Insert Employee Details into MySQL Database</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>
<div class="container">
    <div class="row">
        <div class="col-sm-offset-3 col-lg-6 col-sm-6 well">
        <legend>Add Employee Details</legend>
        <?php 
        $attributes = array("class" => "form-horizontal", "id" => "employeeform", "name" => "employeeform");
        echo form_open("employee/index", $attributes);?>
        <fieldset>
            
            <div class="form-group">
            <div class="row colbox">
            
            <div class="col-lg-4 col-sm-4">
                <label for="employeeno" class="control-label">Employee No</label>
            </div>
            <div class="col-lg-8 col-sm-8">
                <input id="employeeno" name="employeeno" placeholder="employeeno" type="text" class="form-control"  value="<?php echo set_value('employeeno'); ?>" />
                <span class="text-danger"><?php echo form_error('employeeno'); ?></span>
            </div>
            </div>
            </div>

            <div class="form-group">
            <div class="row colbox">
            <div class="col-lg-4 col-sm-4">
                <label for="employeename" class="control-label">Employee Name</label>
            </div>
            <div class="col-lg-8 col-sm-8">
                <input id="employeename" name="employeename" placeholder="employeename" type="text" class="form-control"  value="<?php echo set_value('employeename'); ?>" />
                <span class="text-danger"><?php echo form_error('employeename'); ?></span>
            </div>
            </div>
            </div>
            
            <div class="form-group">
            <div class="row colbox">
            <div class="col-lg-4 col-sm-4">
                <label for="department" class="control-label">Department</label>
            </div>
            <div class="col-lg-8 col-sm-8">
            
                <?php
                $attributes = 'class = "form-control" id = "department"';
                echo form_dropdown('department',$department,set_value('department'),$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-lg-4 col-sm-4">
                <label for="designation" class="control-label">Designation</label>
            </div>
            <div class="col-lg-8 col-sm-8">
            
                <?php
                $attributes = 'class = "form-control" id = "designation"';
                echo form_dropdown('designation',$designation, set_value('designation'), $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-lg-4 col-sm-4">
                <label for="hireddate" class="control-label">Hired Date</label>
            </div>
            <div class="col-lg-8 col-sm-8">
                <input id="hireddate" name="hireddate" placeholder="hireddate" type="text" class="form-control"  value="<?php echo set_value('hireddate'); ?>" />
                <span class="text-danger"><?php echo form_error('hireddate'); ?></span>
            </div>
            </div>
            </div>
            
            <div class="form-group">
            <div class="row colbox">
            <div class="col-lg-4 col-sm-4">
                <label for="salary" class="control-label">Salary</label>
            </div>
            <div class="col-lg-8 col-sm-8">
                <input id="salary" name="salary" placeholder="salary" type="text" class="form-control" value="<?php echo set_value('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-lg-8 col-sm-8 text-left">
                <input id="btn_add" name="btn_add" type="submit" class="btn btn-primary" value="Insert" />
                <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>

This is how our employee form looks like.

codeigniter-bootstrap-form-example

As already said, I have used twitter bootstrap css framework with codeigniter to design this user input form. Bootstrap provides all the necessary css components to design a website and let you jump start immediately into application development without worrying about the stylesheet. Here are some of our codeigniter tutorials for you which uses Twitter Bootstrap framework for front end development.

Add Date Picker to CodeIgniter Form using jQuery UI

The employee form contains a date input field named 'hired date'. To enhance the user experience, I have used jQuery UI plug-in to add date picker calendar. And this date picker calendar pops up when the 'hired date' input field got focus and let the user to pick up the date without typing.

codeigniter-bootstrap-form-date-picker-example

To add the date picker control to codeigniter form with jquery ui plug-in add the javascript line,

$("#hireddate").datepicker();

"#hireddate" is the form element id. To add date picker to more than one input field, use comma (,) to separate the id's like this $("#id1, #id2, #id3").datepicker();

Also using echo form_error() below every input element will display the corresponding form validation error messages under the input elements like this.

codeigniter-bootstrap-form-validation-error-message

The functions form_open() and form_close() are used to add html form element to the codeigniter view. Finally the line echo $this->session->flashdata('msg') displays the success message when the form values are inserted into database without error.

codeigniter-form-insert-db-success msg
Also Read:

Hope you have liked this CodeIgniter Database Tutorial on inserting form data into database. If you want to learn the complete basics of the framework, then check this guide on codeigniter beginners tutorial.

Last Modified: Jun-2-2016

26 comments:

  1. "form-horizontal", "id" => "employeeform", "name" => "employeeform");
    echo form_open("employee/index", $attributes);?>

    explain tnx

    ReplyDelete
    Replies
    1. Hi,

      It creates the following form tag,

      form class="form-horizontal" id=''employeeform" id=''employeeform" action="employee/index"

      "form-horizontal" is the bootstrap class that aligns the form elements in horizontal.

      Cheers.

      Delete
  2. Aloha,
    when I try this tutorial a give this message.

    A Database Error Occurred

    Error Number: 1054

    Unknown column 'department_id' in 'field list'

    SELECT `department_id`, `department_name`, `tbl_department`

    Filename: C:/xampp/htdocs/CI3-T/application/models/4_insert_db/Employee_model.php

    Line Number: 17

    ReplyDelete
    Replies
    1. Hi! First you have to create the database and the neccessary tables in mysql. Run the sql file given in the top of the article to create mysql db. The error will be fixed automatically.

      Cheers.

      Delete
  3. Wonderful tutorial really this one very helpful for beginner leaner

    ReplyDelete
  4. nice tutorial, it makes me understand more about Codeigniter.! Thanks

    ReplyDelete
    Replies
    1. Glad this tutorial helped you. Cheers!

      Delete
  5. hai pls help my department is always 0 in database whenever i save the employee info. pls help i really need the answer as soon as possible :( for my thesis.

    ReplyDelete
    Replies
    1. Does the department select box shows dept names like the above screenshot?

      Delete
  6. mine not working.
    all works fine but when i click on insert button it shows nothing

    ReplyDelete
  7. I wanna learn codeigniter for knowledge. Compared to laravel 5, for creating a simple CRUD CI has more code to write.

    ReplyDelete
  8. i am getting this error "The page you requested was not found." could you please help me for this...

    ReplyDelete
  9. i am getting this error
    Unable to access an error message corresponding to your field name Employee Name.(xss_clean)

    ReplyDelete
    Replies
    1. Hi! The validation filter 'xss_clean' is no longer required. You can simply remove it.

      Change this line,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|xss_clean|callback_alpha_only_space');

      To,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|callback_alpha_only_space');

      Incase you want to use 'xss_clean', then make sure to load the 'security' helper in the controller,

      $this->load->helper('security');

      It will fix the issue.
      Cheers!

      Delete
  10. Its giving error for employee name
    Unable to access an error message corresponding to your field name Employee Name.(xss_clean)

    ReplyDelete
    Replies
    1. add $autoload['helper'] = array('security'); to your autoload file and it should work.

      application/config/autoload

      Delete
    2. Hi! The validation filter 'xss_clean' is no longer required. You can simply remove it.

      Change this line,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|xss_clean|callback_alpha_only_space');

      To,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|callback_alpha_only_space');

      Incase you want to use 'xss_clean', then make sure to load the 'security' helper in the controller,

      $this->load->helper('security');

      Issue will be fixed.
      Cheers!

      Delete
  11. I am getting this error when Submit button is clicked and URL "localhost:8085/Code4/employee/index" is shown in address bar:

    The address wasn't understood

    Firefox doesn't know how to open this address, because one of the following protocols (localhost) isn't associated with any program or is not allowed in this context.

    You might need to install other software to open this address.

    ReplyDelete
  12. Getting the same error as 'manek Ineed' not sure how to fix this?
    Unable to access an error message corresponding to your field name Employee Name.(xss_clean)

    thanks

    ReplyDelete
    Replies
    1. Hi! The validation filter 'xss_clean' is no longer required. You can simply remove it.

      Change this line,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|xss_clean|callback_alpha_only_space');

      To,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|callback_alpha_only_space');

      Incase you want to use 'xss_clean', then make sure to load the 'security' helper in the controller,

      $this->load->helper('security');

      It'll be fixed. Cheers!

      Delete
  13. I'm also getting the employee name error

    Unable to access an error message corresponding to your field name Employee Name.(xss_clean)

    any chance you could help

    thanks

    ReplyDelete
    Replies
    1. Hi! The validation filter 'xss_clean' is no longer required. You can simply remove it.

      Change this line,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|xss_clean|callback_alpha_only_space');

      To,
      $this->form_validation->set_rules('employeename', 'Employee Name', 'trim|required|callback_alpha_only_space');

      Incase you want to use 'xss_clean', then make sure to load the 'security' helper in the controller,

      $this->load->helper('security');

      Issue will be fixed! Cheers:)

      Delete
  14. Unable to access an error message corresponding to your field name Employee Name.(xss_clean)

    I got this error message when hit insert button.
    Please help

    ReplyDelete

Contact Form

Name

Email *

Message *