CodeIgniter CRUD Operations with MySQL

CRUD (Create, Read, Update and Delete) operations are commonly used to manipulate data in the database. Almost, all web application used Add, Edit, Update and Delete functionality for managing data. In this tutorial, we will create a simple CRUD application in CodeIgniter with MySQL to perform create (insert), read (select), update, and delete operations.

CodeIgniter is one of the most popular PHP framework and CRUD is the most required functionality for every CodeIgniter application. In our earlier CodeIgniter tutorial, we have learned CodeIgniter framework installation, configuration, database creation, data listing and basic functionality. Now we’ll integrate some advanced functionality in CodeIgniter 3 by creating a simple blog post application.

If you are new to CodeIgniter, before getting started with this CodeIgniter CRUD tutorial, see the CodeIgniter Tutorial for Beginners. This tutorial will show you how to implement add, edit, and delete operations in CodeIgniter application.

The following functionalities will be integrated into CodeIgniter CRUD application.

  • Retrieve posts from the MySQL database and listed in tabular format.
  • View post details.
  • Add post data to the MySQL database.
  • Edit and update post data.
  • Delete post data from the MySQL database.

Create Database Table

To store the post information a table needs to be created in the MySQL database. The following SQL creates a posts table with some basic columns in the MySQL database.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `content` text COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

AUTO-LOADER

It always a good idea to autoload the libraries and helpers which are commonly used in the application. Open the config/autoload.php file, specify the library and helper files to autoload.

$autoload['libraries'] = array('database''session');
$autoload['helper'] = array('url');

Create Model

Open the application/models/ directory and create a new file called Post.php for Post model. The Post model class has the following methods to handle the database related operations (fetch, insert, update, and delete).

  • getRows() : Fetch all the posts data from the database. If ID given, it returns only the perticular post data.
  • insert() : Insert post data into the database.
  • update() : Update post data in the database.
  • delete() : Delete post data from the database.
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class 
Post extends CI_Model{
    
/*
     * Get posts
     */
    
function getRows($id ""){
        if(!empty(
$id)){
            
$query $this->db->get_where('posts', array('id' => $id));
            return 
$query->row_array();
        }else{
            
$query $this->db->get('posts');
            return 
$query->result_array();
        }
    }
    
    
/*
     * Insert post
     */
    
public function insert($data = array()) {
        
$insert $this->db->insert('posts'$data);
        if(
$insert){
            return 
$this->db->insert_id();
        }else{
            return 
false;
        }
    }
    
    
/*
     * Update post
     */
    
public function update($data$id) {
        if(!empty(
$data) && !empty($id)){
            
$update $this->db->update('posts'$data, array('id'=>$id));
            return 
$update?true:false;
        }else{
            return 
false;
        }
    }
    
    
/*
     * Delete post
     */
    
public function delete($id){
        
$delete $this->db->delete('posts',array('id'=>$id));
        return 
$delete?true:false;
    }
}

Create Controller

Open the application/controllers/ directory and create a new file called Posts.php for Posts controller. The Posts controller class has the following methods to perform CRUD operations (view, add, edit, and delete).

  • __construct() : Loads the required library, helper, and model.
  • index() :
    • Get status messages from the session.
    • Retrieve posts data from database using getRows() method of Post model.
    • Load post list view.
  • view() :
    • Retrieve post data based on the ID using getRows() method of Post model.
    • Load post details view.
  • add() :
    • If post submitted, the post data passes to insert() method for insertion.
    • Load post create form view.
  • edit() :
    • If post submitted, the post data passes to update() method for update.
    • Load post edit form view.
  • delete() : Remove post data using delete() method of Post model.

The CodeIgniter’s form_validation library is used to validate the submitted data on add and edit request.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class 
Posts extends CI_Controller {
    
    function 
__construct() {
        
parent::__construct();
        
$this->load->helper('form');
        
$this->load->library('form_validation');
        
$this->load->model('post');
    }
    
    public function 
index(){
        
$data = array();
        
        
//get messages from the session
        
if($this->session->userdata('success_msg')){
            
$data['success_msg'] = $this->session->userdata('success_msg');
            
$this->session->unset_userdata('success_msg');
        }
        if(
$this->session->userdata('error_msg')){
            
$data['error_msg'] = $this->session->userdata('error_msg');
            
$this->session->unset_userdata('error_msg');
        }

        
$data['posts'] = $this->post->getRows();
        
$data['title'] = 'Post Archive';
        
        
//load the list page view
        
$this->load->view('templates/header'$data);
        
$this->load->view('posts/index'$data);
        
$this->load->view('templates/footer');
    }
    
    
/*
     * Post details
     */
    
public function view($id){
        
$data = array();
        
        
//check whether post id is not empty
        
if(!empty($id)){
            
$data['post'] = $this->post->getRows($id);
            
$data['title'] = $data['post']['title'];
            
            
//load the details page view
            
$this->load->view('templates/header'$data);
            
$this->load->view('posts/view'$data);
            
$this->load->view('templates/footer');
        }else{
            
redirect('/posts');
        }
    }
    
    
/*
     * Add post content
     */
    
public function add(){
        
$data = array();
        
$postData = array();
        
        
//if add request is submitted
        
if($this->input->post('postSubmit')){
            
//form field validation rules
            
$this->form_validation->set_rules('title''post title''required');
            
$this->form_validation->set_rules('content''post content''required');
            
            
//prepare post data
            
$postData = array(
                
'title' => $this->input->post('title'),
                
'content' => $this->input->post('content')
            );
            
            
//validate submitted form data
            
if($this->form_validation->run() == true){
                
//insert post data
                
$insert $this->post->insert($postData);

                if(
$insert){
                    
$this->session->set_userdata('success_msg''Post has been added successfully.');
                    
redirect('/posts');
                }else{
                    
$data['error_msg'] = 'Some problems occurred, please try again.';
                }
            }
        }
        
        
$data['post'] = $postData;
        
$data['title'] = 'Create Post';
        
$data['action'] = 'Add';
        
        
//load the add page view
        
$this->load->view('templates/header'$data);
        
$this->load->view('posts/add-edit'$data);
        
$this->load->view('templates/footer');
    }
    
    
/*
     * Update post content
     */
    
public function edit($id){
        
$data = array();
        
        
//get post data
        
$postData $this->post->getRows($id);
        
        
//if update request is submitted
        
if($this->input->post('postSubmit')){
            
//form field validation rules
            
$this->form_validation->set_rules('title''post title''required');
            
$this->form_validation->set_rules('content''post content''required');
            
            
//prepare cms page data
            
$postData = array(
                
'title' => $this->input->post('title'),
                
'content' => $this->input->post('content')
            );
            
            
//validate submitted form data
            
if($this->form_validation->run() == true){
                
//update post data
                
$update $this->post->update($postData$id);

                if(
$update){
                    
$this->session->set_userdata('success_msg''Post has been updated successfully.');
                    
redirect('/posts');
                }else{
                    
$data['error_msg'] = 'Some problems occurred, please try again.';
                }
            }
        }

        
        
$data['post'] = $postData;
        
$data['title'] = 'Update Post';
        
$data['action'] = 'Edit';
        
        
//load the edit page view
        
$this->load->view('templates/header'$data);
        
$this->load->view('posts/add-edit'$data);
        
$this->load->view('templates/footer');
    }
    
    
/*
     * Delete post data
     */
    
public function delete($id){
        
//check whether post id is not empty
        
if($id){
            
//delete post
            
$delete $this->post->delete($id);
            
            if(
$delete){
                
$this->session->set_userdata('success_msg''Post has been removed successfully.');
            }else{
                
$this->session->set_userdata('error_msg''Some problems occurred, please try again.');
            }
        }

        
redirect('/posts');
    }
}

Create Views

Open the application/views/ directory and create the following views.

templates/header.php
This view contains the header section of the webpage. The Bootstrap library is used to provide a better UI, so, include it in the header section. If you don’t want to use Bootstrap for styling HTML table and form, you can omit it to include.

<!DOCTYPE html>
<html lang="en-US">
<head>
    <meta charset="utf-8">
    <title><?php echo $title?></title>
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
</head>
<body>

templates/footer.php
This view contains the footer section of the webpage.

</body>
</html>

posts/index.php
In this view, the posts are listed in a tabular format with the view, edit, and delete links. Also, an add link is provided to create a new post.

<div class="container">
    <?php if(!empty($success_msg)){ ?>
    <div class="col-xs-12">
        <div class="alert alert-success"><?php echo $success_msg?></div>
    </div>
    <?php }elseif(!empty($error_msg)){ ?>
    <div class="col-xs-12">
        <div class="alert alert-danger"><?php echo $error_msg?></div>
    </div>
    <?php ?>
    <div class="row">
        <div class="col-xs-12">
            <div class="panel panel-default ">
                <div class="panel-heading">Posts <a href="<?php echo site_url('posts/add/'); ?>" class="glyphicon glyphicon-plus pull-right" ></a></div>
                <table class="table table-striped">
                    <thead>
                        <tr>
                            <th width="5%">ID</th>
                            <th width="30%">Title</th>
                            <th width="50%">Content</th>
                            <th width="15%">Action</th>
                        </tr>
                    </thead>
                    <tbody id="userData">
                        <?php if(!empty($posts)): foreach($posts as $post): ?>
                        <tr>
                            <td><?php echo '#'.$post['id']; ?></td>
                            <td><?php echo $post['title']; ?></td>
                            <td><?php echo (strlen($post['content'])>150)?substr($post['content'],0,150).'...':$post['content']; ?></td>
                            <td>
                                <a href="<?php echo site_url('posts/view/'.$post['id']); ?>" class="glyphicon glyphicon-eye-open"></a>
                                <a href="<?php echo site_url('posts/edit/'.$post['id']); ?>" class="glyphicon glyphicon-edit"></a>
                                <a href="<?php echo site_url('posts/delete/'.$post['id']); ?>" class="glyphicon glyphicon-trash" onclick="return confirm('Are you sure to delete?')"></a>
                            </td>
                        </tr>
                        <?php endforeach; else: ?>
                        <tr><td colspan="4">Post(s) not found......</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

posts/view.php
In this view, the post details are shown.

<div class="container">
    <div class="row">
        <div class="panel panel-default">
            <div class="panel-heading">Post Details <a href="<?php echo site_url('posts/'); ?>" class="glyphicon glyphicon-arrow-left pull-right"></a></div>
            <div class="panel-body">
                <div class="form-group">
                    <label>Title:</label>
                    <p><?php echo !empty($post['title'])?$post['title']:''?></p>
                </div>
                <div class="form-group">
                    <label>Content:</label>
                    <p><?php echo !empty($post['content'])?$post['content']:''?></p>
                </div>
            </div>
        </div>
    </div>
</div>

posts/add-edit.php
This view contains an HTML form to submit the post information.

<div class="container">
    <div class="col-xs-12">
    <?php 
        
if(!empty($success_msg)){
            echo 
'<div class="alert alert-success">'.$success_msg.'</div>';
        }elseif(!empty(
$error_msg)){
            echo 
'<div class="alert alert-danger">'.$error_msg.'</div>';
        }
    
?> </div> <div class="row"> <div class="col-xs-12"> <div class="panel panel-default"> <div class="panel-heading"><?php echo $action?> Post <a href="<?php echo site_url('posts/'); ?>" class="glyphicon glyphicon-arrow-left pull-right"></a></div> <div class="panel-body"> <form method="post" action="" class="form"> <div class="form-group"> <label for="title">Title</label> <input type="text" class="form-control" name="title" placeholder="Enter title" value="<?php echo !empty($post['title'])?$post['title']:''?>">                             <?php echo form_error('title','<p class="help-block text-danger">','</p>'); ?> </div> <div class="form-group"> <label for="content">Content</label> <textarea name="content" class="form-control" placeholder="Enter post content"><?php echo !empty($post['content'])?$post['content']:''?></textarea>                             <?php echo form_error('content','<p class="text-danger">','</p>'); ?> </div> <input type="submit" name="postSubmit" class="btn btn-primary" value="Submit"/> </form> </div> </div> </div> </div> </div>

Routing

To access the methods of Posts controller on the browser, you need to add some rules in config/routes.php file.

$route['posts/view/(:num)'] = 'posts/view/$1';
$route['posts/add'] = 'posts/add';
$route['posts/edit/(:num)'] = 'posts/edit/$1';
$route['posts/delete/(:num)'] = 'posts/delete/$1';
$route['posts'] = 'posts';

Remove the index.php from URL

By default, the index.php file will be included in your URLs, follow the below steps to remove index.php from URLs.

1. Open the config/config.php file and make index_page blank for using mod_rewrite to remove index.php from URL.

$config['index_page'] = '';

2. Create an .htaccess file in CodeIgniter’s root directory and place write the following rules.

RewriteEngine on
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /codeigniter/index.php/$1 [L]

Note that: The mod_rewrite must be enabled on your Apache server to rewrite URL using an .htaccess file.

Example CodeIgniter CRUD Application

Now point your browser to post controller URL (http://localhost/codeigniter/posts/), the post lists will appear on the screen. Also, you will be able to perform the view, add, edit and delete operations in CodeIgniter.

codeigniter-crud-bootstrap-view-add-edit-delete-operations-codexworld

You can download the example CodeIgniter CRUD project which has created by following this tutorial. Only some minimal configuration needed to set up this demo CodeIgniter CRUD application on your server.

Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request

8 Comments

  1. Vijay Said...
  2. Castle Said...
    • CodexWorld Said...
  3. Chandu Said...
  4. Deep Said...
  5. Wankunda Simuchimba Said...
    • CodexWorld Said...

Leave a reply

keyboard_double_arrow_up