PHP Codeigniter 3 - Basic CRUD Operation with MySQL Database with example

Posted 6 years ago By Ajay Gupta 755306

Codeigniter 3 - Basic CRUD Operation with MySQL Database with example

In this tutorial, I will tell you the basic CRUD operation with MySQL database with example in Codeigniter 3.

You will find the step by step process to build a simple application having crud functionality in Codeigniter 3 with MySQL Database.

CRUD operation is a common thing in the application of familiarity with basic create, read, update and delete functionality of the Database.

Step 1: Install Codeigniter 3

Codeigniter 3 is one of the most popular PHP Framework and you can easily install it in your system.

You can easily download it from here : Download Codeigniter 3

As we know, CodeIgniter is very lightweight framework so it will not take long time to download.

After download successfully, extract it in your working directory where you run your PHP script.

Step 2: Database Configuration

To perform crud operation, We will need to have a database with tables so first I will create a demo database and then create a products table within the database.

Run the following sql query to create a table :

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `description` varchar(255) NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

Now I have a products table on which i will perform the crud functionality.

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 will 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' => 'xxxx',
    'database' => 'demo',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'xxxx',
	'database' => 'demo',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'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'] = 'welcome';
$route['404_override'] = '';
$route['translate_uri_dashes'] = FALSE;
$route['products'] = "products/index";
$route['productsCreate']['post'] = "products/store";
$route['productsEdit/(:any)'] = "products/edit/$1";
$route['productsUpdate/(:any)']['put'] = "products/update/$1";
$route['productsDelete/(:any)']['delete'] = "products/delete/$1";

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


$route['products'] = "products/index";
$route['productsCreate']['post'] = "products/store";
$route['productsEdit/(:any)'] = "products/edit/$1";
$route['productsUpdate/(:any)']['put'] = "products/update/$1";
$route['productsDelete/(:any)']['delete'] = "products/delete/$1";

In routes "products/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 product listing, create, edit, update and delete.

Now I will create a Products.php file in following path application/controllers/.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Products extends CI_Controller {
   /**
    * Get All Data from this method.
    *
    * @return Response
   */
   public function __construct() {
    //load database in autoload libraries 
      parent::__construct(); 
      $this->load->model('ProductsModel');         
   }
   public function index()
   {
       $products=new ProductsModel;
       $data['data']=$products->get_products();
       $this->load->view('includes/header');       
       $this->load->view('products/list',$data);
       $this->load->view('includes/footer');
   }
   public function create()
   {
      $this->load->view('includes/header');
      $this->load->view('products/create');
      $this->load->view('includes/footer');      
   }
   /**
    * Store Data from this method.
    *
    * @return Response
   */
   public function store()
   {
       $products=new ProductsModel;
       $products->insert_product();
       redirect(base_url('products'));
    }
   /**
    * Edit Data from this method.
    *
    * @return Response
   */
   public function edit($id)
   {
       $product = $this->db->get_where('products', array('id' => $id))->row();
       $this->load->view('includes/header');
       $this->load->view('products/edit',array('product'=>$product));
       $this->load->view('includes/footer');   
   }
   /**
    * Update Data from this method.
    *
    * @return Response
   */
   public function update($id)
   {
       $products=new ProductsModel;
       $products->update_product($id);
       redirect(base_url('products'));
   }
   /**
    * Delete Data from this method.
    *
    * @return Response
   */
   public function delete($id)
   {
       $this->db->where('id', $id);
       $this->db->delete('products');
       redirect(base_url('products'));
   }
}

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

class Products extends CI_Controller {

   /**
    * Get All Data from this method.
    *
    * @return Response
   */
   public function __construct() {
    //load database in autoload libraries 
      parent::__construct(); 
      $this->load->model('ProductsModel');         
   }
   public function index()
   {

       $products=new ProductsModel;
       $data['data']=$products->get_products();
       $this->load->view('includes/header');       
       $this->load->view('products/list',$data);
       $this->load->view('includes/footer');

   }

   public function create()
   {
      $this->load->view('includes/header');
      $this->load->view('products/create');
      $this->load->view('includes/footer');      
   }

   /**
    * Store Data from this method.
    *
    * @return Response
   */
   public function store()
   {
       $products=new ProductsModel;
       $products->insert_product();
       redirect(base_url('products'));
    }

   /**
    * Edit Data from this method.
    *
    * @return Response
   */
   public function edit($id)
   {
       $product = $this->db->get_where('products', array('id' => $id))->row();
       $this->load->view('includes/header');
       $this->load->view('products/edit',array('product'=>$product));
       $this->load->view('includes/footer');   
   }

   /**
    * Update Data from this method.
    *
    * @return Response
   */
   public function update($id)
   {
       $products=new ProductsModel;
       $products->update_product($id);
       redirect(base_url('products'));
   }

   /**
    * Delete Data from this method.
    *
    * @return Response
   */
   public function delete($id)
   {
       $this->db->where('id', $id);
       $this->db->delete('products');
       redirect(base_url('products'));
   }

}

Step 4: Create Model

In this step, I will create a model file to define some common functionality that will interact with the database table.

Create a ProductsModel.php file in following path application/models.

<?php
class ProductsModel extends CI_Model{
    
    public function get_products(){
        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("products");
        return $query->result();
    }
    public function insert_product()
    {    
        $data = array(
            'title' => $this->input->post('title'),
            'description' => $this->input->post('description')
        );
        return $this->db->insert('products', $data);
    }
    public function update_product($id) 
    {
        $data=array(
            'title' => $this->input->post('title'),
            'description'=> $this->input->post('description')
        );
        if($id==0){
            return $this->db->insert('products',$data);
        }else{
            $this->db->where('id',$id);
            return $this->db->update('products',$data);
        }        
    }
}
?>

<?php

class ProductsModel extends CI_Model{
	
    public function get_products(){

        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("products");
        return $query->result();
    }

    public function insert_product()
    {    
        $data = array(
            'title' => $this->input->post('title'),
            'description' => $this->input->post('description')
        );
        return $this->db->insert('products', $data);
    }

    public function update_product($id) 
    {
    	$data=array(
			'title' => $this->input->post('title'),
			'description'=> $this->input->post('description')
    	);
    	if($id==0){
    		return $this->db->insert('products',$data);
    	}else{
    		$this->db->where('id',$id);
    		return $this->db->update('products',$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
products/list.php
products/create.php
products/edit.php

includes/header.php

<!DOCTYPE html>
<html>
<head>
    <title>Basic Crud operation in Codeigniter 3</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<div class="container">

<!DOCTYPE html>
<html>
<head>
	<title>Basic Crud operation in Codeigniter 3</title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<div class="container">

includes/footer.php

</div>
</body>
</html>

</div>
</body>
</html>

products/list.php

<div class="row">
    <div class="col-lg-12">           
        <h2>Products CRUD           
            <div class="pull-right">
               <a class="btn btn-primary" href="<?php echo base_url('products/create') ?>"> Create New Product</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('products/delete/'.$d->id);?>">
         <a class="btn btn-info btn-xs" href="<?php echo base_url('products/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>

<div class="row">
	<div class="col-lg-12">           
        <h2>Products CRUD           
            <div class="pull-right">
               <a class="btn btn-primary" href="<?php echo base_url('products/create') ?>"> Create New Product</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('products/delete/'.$d->id);?>">
         <a class="btn btn-info btn-xs" href="<?php echo base_url('products/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>

products/create.php

<form method="post" action="<?php echo base_url('productsCreate');?>">
    <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">
                </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"></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>

<form method="post" action="<?php echo base_url('productsCreate');?>">
	<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">
				</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"></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>

products/edit.php

<form method="post" action="<?php echo base_url('products/update/'.$product->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 $product->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 $product->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>

<form method="post" action="<?php echo base_url('products/update/'.$product->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 $product->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 $product->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>

If you will get the error of undefined function base_url() then follow the URL : Call to undefined function base_url() in Codeigniter

Click here to know how to implement CRUD Operation in Laravel PHP Framework :

Laravel CRUD Example

Try this code and feel free to give your suggestion.

PHP Codeigniter 3 - Basic CRUD Operation with MySQL Database with example

Codeigniter 3 - Basic CRUD Operation with MySQL Database with example

Tags :

Related Posts

[Solved]Fatal error: Call to undefined function base_url() in Codeigniter

Codeigniter - Upload image with preview using jQuery Ajax Form Plugin with example

Codeigniter 3 - Dynamic dependent dropdown using jquery ajax with example

How to upload image with validation in PHP Codeigniter with example?

How to get last record from MySQL Database table in Codeigniter?

How to get last insert id in PHP Codeigniter?

Ajax contact us form validation in Codeigniter 3 using javascript jQuery

Working on Datatables with Ajax in PHP Codeigniter 3 Example

Codeigniter 3 upload multiple images using drag and drop with example

Codeigniter 3: Create Restful API with example

PHP Codeigniter - Load more data on page scroll using jQuery and Ajax

PHP CodeIgniter 4 - Basic CRUD operation with MySQL Database with example