Codeigniter 3 CRUD Operation with MySQL Database with example
In this tutorial, I will cover CRUD operation with MySQL database with example in Codeigniter 3.
You can check the step by step process we used a simple application having crud functionality in Codeigniter 3 with MySQL Database.
CRUD operation is functionality in all application of familiarity with basic create, read, update and delete functionality with the Database.
The Process of crud functionality.
Step 1: Install Codeigniter 3
Codeigniter 3 is one of the most popular PHP Framework and you can easily install it in your system and process all step.
You can download it from here: https://codeigniter.com/download
Codeigniter lightweight framework it will not take more than 1min to download.
Step 2: Database Configuration
Database configuration
CREATE TABLE `blogs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
Ok, let’s configure the database with codeigniter application so open the database.php file and put your database credentials (database name, username and password) there.
You can find the database.php file in following path application/config/database.php.
$active_group = 'default';
$query_builder = TRUE;
$db['default'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => 'root',
'database' => 'ci_blogs',
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'blogion'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);Step 3: Route
In this step, I will add routes in application/congif/routes.php file to handle the request.
$route['default_controller'] = 'blogs';
$route['blogs'] = "blogs/index";
$route['blogsCreate']['post'] = "blogs/store";
$route['blogsEdit/(:any)'] = "blogs/edit/$1";
$route['blogsUpdate/(:any)']['put'] = "blogs/update/$1";
$route['blogsDelete/(:any)']['delete'] = "blogs/delete/$1";
$route['404_override'] = '';
$route['translate_uri_dashes'] = FALSE;In routes “blogs/index”, the first segment will be remapped to the controller class and second will be remapped with the method of that controller.
Step 4: Create Controller
In this step, I will create a controller to handle the method for blog listing, create, edit, update and delete.
Now I will create a Blogs.php file in following path application/controllers/.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Blogs extends CI_Controller {
/**
* Get All Data from this method.
*
* @return Response
*/
public function __construct() {
//load database in autoload libraries
parent::__construct();
$this->load->model('BlogsModel');
}
public function index()
{
$blogs =new BlogsModel;
$data['data']= $blogs->get_blogs();
$this->load->view('includes/header');
$this->load->view('blogs/list',$data);
$this->load->view('includes/footer');
}
public function create()
{
$this->load->view('includes/header');
$this->load->view('blogs/create');
$this->load->view('includes/footer');
}
/**
* Store Data from this method.
*
* @return Response
*/
public function store()
{
$blogs=new BlogsModel;
$blogs->insert_blog();
redirect(base_url('blogs'));
}
/**
* Edit Data from this method.
*
* @return Response
*/
public function edit($id)
{
$blog = $this->db->get_where('blogs', array('id' => $id))->row();
$this->load->view('includes/header');
$this->load->view('blogs/edit',array('blog'=>$blog));
$this->load->view('includes/footer');
}
/**
* Update Data from this method.
*
* @return Response
*/
public function update($id)
{
$blogs=new BlogsModel;
$blogs->update_blog($id);
redirect(base_url('blogs'));
}
/**
* Delete Data from this method.
*
* @return Response
*/
public function delete($id)
{
$this->db->where('id', $id);
$this->db->delete('blogs');
redirect(base_url('blogs'));
}
}Step 4: Create a Model
In this step, I will create a model file to define some common functionality that will interact with the database table.
Create a BlogsModel.php file in following path application/models.
<?php
class BlogsModel extends CI_Model{
public function get_blogs(){
if(!empty($this->input->get("search"))){
$this->db->like('title', $this->input->get("search"));
$this->db->or_like('description', $this->input->get("search"));
}
$query = $this->db->get("blogs");
return $query->result();
}
public function insert_blog()
{
$data = array(
'title' => $this->input->post('title'),
'description' => $this->input->post('description')
);
return $this->db->insert('blogs', $data);
}
public function update_blog($id)
{
$data=array(
'title' => $this->input->post('title'),
'description'=> $this->input->post('description')
);
if($id==0){
return $this->db->insert('blogs',$data);
}else{
$this->db->where('id',$id);
return $this->db->update('blogs',$data);
}
}
}
?>Step 5: Create View File
In this step, I will create different view files that will be use in this crud application :
includes/header.php
includes/footer.php
blogs/list.php
blogs/create.php
blogs/edit.php
includes/header.php
<!DOCTYPE html>
<html>
<head>
<title>Codeigniter 3 Crud</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="jumbotron text-center">
<h1>Boostrap </h1>
<p>You can check from git hub</p>
</div>blogs/list.php
<div class="row">
<div class="col-lg-12">
<h2>Blogs CRUD
<div class="pull-right">
<a class="btn btn-primary" href="<?php echo base_url('blogs/create') ?>"> Create New Blog</a>
</div>
</h2>
</div>
</div>
<div class="table-responsive">
<table class="table table-bordered">
<thead>
<tr>
<th>Title</th>
<th>Description</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php foreach ($data as $d) { ?>
<tr>
<td><?php echo $d->title; ?></td>
<td><?php echo $d->description; ?></td>
<td>
<form method="DELETE" action="<?php echo base_url('blogs/delete/'.$d->id);?>">
<a class="btn btn-info btn-xs" href="<?php echo base_url('blogs/edit/'.$d->id) ?>"><i class="glyphicon glyphicon-pencil"></i></a>
<button type="submit" class="btn btn-danger btn-xs"><i class="glyphicon glyphicon-remove"></i></button>
</form>
</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>blogs/create.php
<div class="container col-md-8 col-md-offset-2">
<h2>Create form</h2>
<form method="post" action="<?php echo base_url('blogsCreate');?>">
<div class="form-group">
<label for="email">Title:</label>
<input type="text" name="title" class="form-control">
</div>
<div class="form-group">
<label for="pwd">Description:</label>
<textarea name="description" class="form-control"></textarea>
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>blogs/edit.php
<form method="post" action="<?php echo base_url('blogs/update/'.$blog->id);?>">
<div class="row">
<div class="col-md-8 col-md-offset-2">
<div class="form-group">
<label class="col-md-3">Title</label>
<div class="col-md-9">
<input type="text" name="title" class="form-control" value="<?php echo $blog->title; ?>">
</div>
</div>
</div>
<div class="col-md-8 col-md-offset-2">
<div class="form-group">
<label class="col-md-3">Description</label>
<div class="col-md-9">
<textarea name="description" class="form-control"><?php echo $blog->description; ?></textarea>
</div>
</div>
</div>
<div class="col-md-8 col-md-offset-2 pull-right">
<input type="submit" name="Save" class="btn">
</div>
</div>
</form> Step 6: Remove in index.php from url
Create .htaccess file in route directory with file name is: .htaccess
remove index.php from $config[‘index_page’] = ‘index.php’;
and put code in .htaccess file.
RewriteEngine on
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule .* index.php/$0 [PT,L]Step 7: Now Run Project
Now you can create update and delete operation.
