Add, Edit and Delete Record Using Codeigniter and MySQL

This Codeigniter tutorial will help to create a sample crud operation application using CI framework and MySQL, As you know CRUD example will have add record, edit record and delete record from the database.

I am creating CRUD operations using the Codeigniter framework and MySQL database. We will create a simple Codeigniter example that will use MySQL for database related operations.

I have already shared Simple Listing of Codeigniter using MySQL. I am extending this tutorial and will add functionality to add, edit and delete record from the database.

You can get basic CI configuration information from jQuery datatable Listing Example and template integration using Simple Layout Example in Codeigniter.

CRUD Application Example Using Codeigniter and MySQL

Below is a step-by-step process to create a listing, add, edit and delete record using Codeigniter and MySQL, In earlier Codeigniter tutorials, I created connection configuration and enable library into the bootstrap file.

I am assuming you have controllers/Home.php file and employee.php model which will use in this tutorials.You can read Simple Listing and configuration of Codeigniter Framework with MySQL.

We can make the home controller as default controller like below using application/config/routes.php file.

$route['default_controller'] = 'home';
$route['404_override'] = '';
$route['translate_uri_dashes'] = FALSE;

How to Insert Record into MySQL Using Codeigniter

So now we have an HTML table listing and will add a feature to add new records into the MySQL database. We will create a new add view file and bind with the action method in the controller file.

Step 1: Created add button HTML element into home.php listing page.

<div class="clearfix well">
  <a class="btn btn-info pull-right" href="<?php echo site_url('home/create'); ?>">Add New Record</a>
</div>

Step 2: We will create a new create.php file in views/ folder which will use to enter employee record information to create new record into MySQL.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<?php echo validation_errors(); ?>
 
<?php echo form_open('home/create/', "class='form-horizontal'"); ?>

<div class="form-group">
 <label class="col-sm-3 control-label no-padding-right" for="form-field-1"> Employee Name </label>
 <div class="col-sm-9">
 <input type="text" id="form-field-1" placeholder="Employee Name" name="employee_name" class="col-xs-10 col-sm-5" value="<?php echo set_value('employee_name'); ?>">
 </div>
</div>

<div class="form-group">
  <label class="col-sm-3 control-label no-padding-right" for="form-field-2"> Employee Age </label>


 <div class="col-sm-9">
  <input type="number" id="form-field-2" placeholder="Age" class="col-xs-10 col-sm-5" name="employee_age" value="<?php echo set_value('employee_age'); ?>">
 </div>

</div>


<div class="form-group">
  <label class="col-sm-3 control-label no-padding-right" for="form-field-3"> Employee Salary </label>


<div class="col-sm-9">
   <input type="number" id="form-field-3" placeholder="Employee Salary" class="col-xs-10 col-sm-5" name="employee_salary" value="<?php echo set_value('employee_salary'); ?>">
</div>

</div>


<div class="space-4"></div>


<div class="clearfix form-actions">

<div class="col-md-offset-3 col-md-9">
   <button class="btn btn-info" type="submit">
    <i class="ace-icon fa fa-check bigger-110"></i>
    Submit
   </button>

   &nbsp; &nbsp; &nbsp;
   <button class="btn" type="reset">
    <i class="ace-icon fa fa-undo bigger-110"></i>
    Reset
   </button>
</div>

</div>

As you can see, I used /home/create action point in HTML form.We will add create() method into home controller file(controllers/Home.php).

Step 3: We will create a method in controllers/Home.php file which will use to create a new record into MySQL using model method(We will later discuss).

public function create()
 {
  $data = array();
  $this->template->set('title', 'Add New Employee');
  $this->load->helper('form');
        $this->load->library('form_validation');
  $this->form_validation->set_rules('employee_name', 'Name', 'required');
        $this->form_validation->set_rules('employee_age', 'Age', 'required');
  $this->form_validation->set_error_delimiters('
<div class="alert alert-danger">', '</div>
');
  
        if ($this->form_validation->run() === FALSE)
        {
  
   $data["data"] = array();
            $this->template->load('default_layout', 'contents' , 'create', $data);
 
        }
        else
        {
            $this->employee->saveEmployee();
            redirect(base_url());
        }
  
  
 }

I am using Codeigniter form_validation library to validate form data, if everything validates fine then access model saveEmployee() method to save posted data into the MySQL database.

Step 4: We will create model saveEmployee() method into m_employee.php file.

public function saveEmployee($id = 0)
    {
 
        $data = array(
            'employee_name' => $this->input->post('employee_name'),
            'id' => $id,
            'employee_salary' => $this->input->post('employee_salary'),
   'employee_age' => $this->input->post('employee_age')
        );
        
        if ($id == 0) {
            return $this->db->insert('employee', $data);
        } else {
            $this->db->where('id', $id);
            return $this->db->update('employee', $data);
        }
    }

I am using the same model method for creating new entry in the employee table as well update the existing entry in the employee table.

Step 5: We will add new entry in config/routes.php file.

$route['home/create/(:any)'] = 'home/create/$1';

How to Edit or Update records into Codeigniter

We have created form to add new record into MySQL database using codeigniter. We will next step and update existing entry using codeigniter and MySQL. Create a new edit record page in views folder and bind with update controller method.

Step 1: We will add edit action button on table each row as like below,

<i class="ace-icon fa fa-pencil bigger-120"></i>

Step 2: We will create a new edit.php file in views/ folder which will use to update employee record information into MySQL table.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<?php echo validation_errors(); ?>
 
<?php echo form_open('home/edit/'.$data['id'], "class='form-horizontal'"); ?>

<div class="form-group">
  <label class="col-sm-3 control-label no-padding-right" for="form-field-1"> Employee Name </label>


<div class="col-sm-9">
   <input type="text" id="form-field-1" placeholder="Employee Name" name="employee_name" class="col-xs-10 col-sm-5" value="<?php echo $data['employee_name']; ?>">
</div>

</div>



<div class="form-group">
  <label class="col-sm-3 control-label no-padding-right" for="form-field-2"> Employee Age </label>


<div class="col-sm-9">
   <input type="number" id="form-field-2" placeholder="Age" class="col-xs-10 col-sm-5" name="employee_age" value="<?php echo $data['employee_age']; ?>">
</div>

</div>


<div class="form-group">
  <label class="col-sm-3 control-label no-padding-right" for="form-field-3"> Employee Salary </label>


<div class="col-sm-9">
   <input type="number" id="form-field-3" placeholder="Employee Salary" class="col-xs-10 col-sm-5" name="employee_salary" value="<?php echo $data['employee_salary']; ?>">
</div>

</div>


<div class="space-4"></div>


<div class="clearfix form-actions">

<div class="col-md-offset-3 col-md-9">
   <button class="btn btn-info" type="submit">
    <i class="ace-icon fa fa-check bigger-110"></i>
    Submit
   </button>

   &nbsp; &nbsp; &nbsp;
   <button class="btn" type="reset">
    <i class="ace-icon fa fa-undo bigger-110"></i>
    Reset
   </button>
</div>

</div>

As you can see, I used /home/edit action point in HTML form with record primary id. The url will make like home/edit/1 where 1 is employee id which is primary key in MySQL table.
We will add a new method edit() method into home controller file.

Step 3: We will create a new edit() method in controller/home.php file which will use to update a new record into MySQL using model method,

public function edit($id)
 {
  $data = array();
  $this->load->helper('form');
        $this->load->library('form_validation');
  $this->form_validation->set_rules('employee_name', 'Name', 'required');
        $this->form_validation->set_rules('employee_age', 'Age', 'required');
  $this->form_validation->set_error_delimiters('
<div class="alert alert-danger">', '</div>
');
  
        if ($this->form_validation->run() === FALSE)
        {
   $data["data"] = $this->employee->get_employees('', '', $id);
   $this->template->set('title', 'Profile edit - '.$data["data"]['employee_name']);
            $this->template->load('default_layout', 'contents' , 'edit', $data);
 
        }
        else
        {
            $this->employee->saveEmployee($id);
            redirect( base_url());
        }
  
  
 }

As stated above,I am using codeigniter form_validation library to validate form data. Validator return true and false, if validator return false then return posted data on edit page with validator message otherwise access model saveEmployee() to save posted data into MySQL database.

I am not creating saveEmployee() method in model, since its already created in model file at the time of create new record step.

Step 4: We will add new entry in config/routes.php file.

$route['home/edit/(:any)'] = 'home/edit/$1';

How to Delete Record into Codeigniter Using MySQL

We will create delete functionality in this posed tutorial.I do not need to create view for delete record we just create action delete button and attached on each table row.

Step 1: We will attached delete action table row as like below,

<i class="ace-icon fa fa-trash-o bigger-120"></i>

Step 2: We will create delete() action method in controller/Home.php file.

public function delete($id)
    {
        $id = $this->uri->segment(3);
        
        if (empty($id))
        {
            show_404();
        }
                
        $news_item = $this->employee->get_employees('', '', $id);;
        
        if($this->employee->delete_employee($id)){
   $this->session->set_flashdata('message', 'Deleted Sucessfully');
   redirect( base_url());  
  }  
    }

first, we will check record is existing then the delete action will fire.

Step 3: We will create delete() model method in m_employee.php file.

public function delete_employee($id)
    {
        $this->db->where('id', $id);
        return $this->db->delete('employee');
    }

Step 4: We will add new entry in config/routes.php file.

$route['home/delete/(:any)'] = 'home/delete/$1';

Conclusion:

I have added insert, edit and delete record into MySQL using Codeigniter framework. I used Codeigniter validation, URL helper library to validate input values and create urls. I am using a simple three columns table. You can create a complex form as well using this Codeigniter tutorial.

You can download the source code from the below link.