DataTable CRUD Example using Codeigniter, MySQL and AJAX

Introduction

In this post we will see Datatable CRUD example using Codeigniter, MySQL and AJAX. We have seen in our previous post on DataTable using Codeigniter, MySQL and AJAX, where we have the following features:

  • Loading data from MySQL database in HTML table
  • Pagination – each page shows 10 records
  • Searching
  • Filtering
  • Sorting

Here in this Datatable CRUD example using Codeigniter, MySQL and AJAX, we will see the additional features along with the above features as given below:

  • Creating new record
  • Updating the existing record
  • Deleting the existing record

Prerequisites

Knowledge of PHP & Codeigniter, jQuery and AJAX
MySQL 5.x
Apache HTTP server 2.4
Codeigniter 3.1.10
XAMPP installed and configured in Windows OS

You need to create assets folder in parallel to application folder for putting all asset files such as js, css, images etc

Example with Source Code

Please go through the following steps to create the example.

MySQL Table

First thing first! So let’s create a table called products in the MySQL server. We have few self explanatory columns in the table as shown below:

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `price` double NOT NULL,
  `sale_price` double NOT NULL,
  `sales_count` int(10) unsigned NOT NULL,
  `sale_date` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

We need to test our application, so we will put some data into the table:

insert  into `products`(`id`,`name`,`price`,`sale_price`,`sales_count`,`sale_date`) values 
(1,'Desktop',5,10,10,'21-0-2019'),
(4,'Phone',15000,17505,251,'05-04-2018'),
(5,'Phone',18000,22080,178,'05-04-2018'),
(6,'Tablet',30500,34040,58,'05-04-2018'),
(7,'Adapter',2000,2500,68,'06-04-2018'),
(8,'TV',45871,55894,165,'07-04-2018'),
(9,'Laptop',67000,75000,2,'19-03-2019'),
(10,'Laptop',120000,150000,100,'19-03-2019'),
(11,'Tab',23000,300000,20,'18-03-2019'),
(12,'Tab',34000,45000,24,'17-03-2019'),
(14,'Mobile',23000,45000,24,'16-02-2019'),
(15,'Mobile',24000,50000,34,'12-02-2019'),
(16,'cc',111,11111,1,''),
(18,'xcxc',0,0,0,''),
(19,'wew',0,0,0,'');

Codeigniter Configurations

I assume you have configured the Codeigniter project setup under the root directory ci3_datatable.

Configure the database setup in ci3_datatable/application/config/database.php . If you have different configurations then you can change accordingly.

'username' => 'root',
'password' => '',
'database' => 'roytuts',

We want to auto-load some configurations to avoid every time call explicitly to load them. Open ci3_datatable/application/config/autoload.php and update as below:

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url', 'file', 'text', 'form');

Edit ci3_datatable/application/config/routes.php to update the default controller:

$route['default_controller'] = 'datatable';

Codeigniter Model Class

We want data to be fetched from database table – products and we will display on the datatable. We will also perform Add, Update, and Delete operations.

Therefore we will create model class Datatable_Model.php under
ci3_datatable/application/models and put the below code as shown below:

<?php

defined('BASEPATH') OR exit('No direct script access allowed');
	
/**
* Description of Datatable_Model
*
* @author https://roytuts.com
*/

class Datatable_Model extends CI_Model {
	
	private $products = 'products';

	function get_products() {		
		//columns
		$columns = array(
            'id',
            'name',
            'price',
            'sale_price',
            'sales_count',
            'sale_date');
		
		//index column
		$indexColumn = 'id';
		
		//total records
		$sqlCount = 'SELECT COUNT(' . $indexColumn . ') AS row_count FROM ' . $this->products;
		$totalRecords = $this->db->query($sqlCount)->row()->row_count;
		
		//pagination
		$limit = '';
		$displayStart = $this->input->get_post('start', true);
		$displayLength = $this->input->get_post('length', true);
		
		if (isset($displayStart) && $displayLength != '-1') {
            $limit = ' LIMIT ' . intval($displayStart) . ', ' . intval($displayLength);
        }
		
		$uri_string = $_SERVER['QUERY_STRING'];
        $uri_string = preg_replace("/%5B/", '[', $uri_string);
        $uri_string = preg_replace("/%5D/", ']', $uri_string);

        $get_param_array = explode('&', $uri_string);
        $arr = array();
        foreach ($get_param_array as $value) {
            $v = $value;
            $explode = explode('=', $v);
            $arr[$explode[0]] = $explode[1];
        }
		
		$index_of_columns = strpos($uri_string, 'columns', 1);
        $index_of_start = strpos($uri_string, 'start');
        $uri_columns = substr($uri_string, 7, ($index_of_start - $index_of_columns - 1));
        $columns_array = explode('&', $uri_columns);
        $arr_columns = array();
		
		foreach ($columns_array as $value) {
            $v = $value;
            $explode = explode('=', $v);
            if (count($explode) == 2) {
                $arr_columns[$explode[0]] = $explode[1];
            } else {
                $arr_columns[$explode[0]] = '';
            }
        }
		
		//sort order
		$order = ' ORDER BY ';
        $orderIndex = $arr['order[0][column]'];
        $orderDir = $arr['order[0][dir]'];
        $bSortable_ = $arr_columns['columns[' . $orderIndex . '][orderable]'];
        if ($bSortable_ == 'true') {
            $order .= $columns[$orderIndex] . ($orderDir === 'asc' ? ' asc' : ' desc');
        }
		
		//filter
		$where = '';
        $searchVal = $arr['search[value]'];
        if (isset($searchVal) && $searchVal != '') {
            $where = " WHERE (";
            for ($i = 0; $i < count($columns); $i++) {
                $where .= $columns[$i] . " LIKE '%" . $this->db->escape_like_str($searchVal) . "%' OR ";
            }
            $where = substr_replace($where, "", -3);
            $where .= ')';
        }
		
		//individual column filtering
        $searchReg = $arr['search[regex]'];
        for ($i = 0; $i < count($columns); $i++) {
            $searchable = $arr['columns[' . $i . '][searchable]'];
            if (isset($searchable) && $searchable == 'true' && $searchReg != 'false') {
                $search_val = $arr['columns[' . $i . '][search][value]'];
                if ($where == '') {
                    $where = ' WHERE ';
                } else {
                    $where .= ' AND ';
                }
                $where .= $columns[$i] . " LIKE '%" . $this->db->escape_like_str($search_val) . "%' ";
            }
        }
		
		//final records
		$sql = 'SELECT SQL_CALC_FOUND_ROWS ' . str_replace(' , ', ' ', implode(', ', $columns)) . ' FROM ' . $this->products . $where . $order . $limit;
        $result = $this->db->query($sql);
		
		//total rows
		$sql = "SELECT FOUND_ROWS() AS length_count";
        $totalFilteredRows = $this->db->query($sql)->row()->length_count;
		
		//display structure
		$echo = $this->input->get_post('draw', true);
        $output = array(
            "draw" => intval($echo),
            "recordsTotal" => $totalRecords,
            "recordsFiltered" => $totalFilteredRows,
            "data" => array()
        );
		
		//put into 'data' array
		foreach ($result->result_array() as $cols) {
            $row = array();
            foreach ($columns as $col) {
                $row[] = $cols[$col];
            }
			array_push($row, '<button class=\'edit\'>Edit</button>  <button class=\'delete\' id='. $cols[$indexColumn] .'>Delete</button>');
            $output['data'][] = $row;
        }
		
		return $output;
	}
	
	function delete_product($id) {
		$sql = 'DELETE FROM ' . $this->products . ' WHERE id=' . $id;
		$this->db->query($sql);
		
		if ($this->db->affected_rows()) {
			return TRUE;
		}
		
		return FALSE;
	}
	
	function update_product($id, $name, $price, $sale_price, $sales_count, $sale_date) {
		$data = array(
					'name' => $name,
					'price' => $price,
					'sale_price' => $sale_price,
					'sales_count' => $sales_count,
					'sale_date' => $sale_date
				);
		
		$this->db->where('id', $id);
		$this->db->update($this->products, $data);
		
		if ($this->db->affected_rows()) {
			return TRUE;
		}
		
		return FALSE;
	}
	
	function add_product($name, $price, $sale_price, $sales_count, $sale_date) {
		$data = array(
					'name' => $name,
					'price' => $price,
					'sale_price' => $sale_price,
					'sales_count' => $sales_count,
					'sale_date' => $sale_date
				);
		
		$this->db->insert($this->products, $data);
		
		if ($this->db->affected_rows()) {
			return TRUE;
		}
		
		return FALSE;
	}
	
}

/* End of file Datatable_Model.php */
/* Location: ./application/models/Datatable_Model.php */

As usual the above model class extends CI_Model class. At the beginning of the class we first declare our table, products, using a variable. Next we have defined get_products() method, where we perform filter, sort, pagination before fetching the final data from the products table.

Notice how I have structure the final results to display using datatable API.

Notice also how I have put the Edit and Delete buttons for each row to perform update and delete operations.

As we are performing CRUD operations, so our next methods are delete_product(), update_product() and add_product() to delete an existing product, to update an existing product and add a new product, respectively.

Codeigniter Controller Class

We need to create controller class Datatable.php to handle client’s request and response under ci3_datatable/application/controllers directory.

<?php

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

class Datatable extends CI_Controller {
	
	function __construct() {
        parent::__construct();
        $this->load->model('datatable_model', 'dm');
    }
	
	function index() {
		$this->load->view('datatable', NULL);
	}
	
	function get_products() {
		$products = $this->dm->get_products();
		echo json_encode($products);
	}
	
	function delete_product() {
		$id = isset($_POST['id']) ? $_POST['id'] : NULL;
		
		if($this->dm->delete_product($id) === TRUE) {
			return TRUE;
		}
		
		return FALSE;
	}
	
	function update_product() {
		$id = $_POST['id'];
		$name = $_POST['name'];
		$price = $_POST['price'];
		$sale_price = $_POST['sale_price'];
		$sale_count = $_POST['sale_count'];
		$sale_date = $_POST['sale_date'];
		
		if($this->dm->update_product($id, $name, $price, $sale_price, $sale_count, $sale_date) === TRUE) {
			return TRUE;
		}
		
		return FALSE;
	}
	
	function add_product() {
		$name = $_POST['name'];
		$price = $_POST['price'];
		$sale_price = $_POST['sale_price'];
		$sale_count = $_POST['sale_count'];
		$sale_date = $_POST['sale_date'];
		
		if($this->dm->add_product($name, $price, $sale_price, $sale_count, $sale_date) === TRUE) {
			return TRUE;
		}
		
		return FALSE;
	}
}

/* End of file Datatable.php */
/* Location: ./application/controllers/Datatable.php */

So the above class extends Codeigniter’s CI_Controller class to get the API’s built-in functionalities.

We define constructor to load our model class. We define the index() function to load the view, where product records are displayed.

Next we define four methods get_products(), delete_product(), update_product() and add_product() to perform CRUD operations, respectively.

Codeigniter View File

Next we need the view file datatable.php under ci3_datatable/application/views directory to show data, to update data, to delete data and to add new data.

Here I will tell you each section separately before I show you the complete code for view file.

<p id='err'/>
	
	<p><a class='btn' href="#add" rel="modal:open">Add New Product</a></p>
	
	<table id="product-grid" class="display" cellspacing="0" width="100%">
		<thead>
			<tr>
				<th>Id</th>
				<th>Name</th>
				<th>Price</th>
				<th>Sale Price</th>
				<th>Sale Count</th>
				<th>Sale Date</th>
				<th>Actions</th>
			</tr>
		</thead>
	</table>
	
	<form id="add" action="#" class="add_form modal" style="display:none;">
		<div id='msgAdd'/>
		<h3>Add a new product</h3>
		<p>
			<label>Name</label>
			<input type="text" name="name">
		</p>
		<p>
			<label>Price</label>
			<input type="text" name="price">
		</p>
		<p>
			<label>Sale Price</label>
			<input type="text" name="sale_price">
		</p>
		<p>
			<label>Sale Count</label>
			<input type="text" name="sale_count">
		</p>
		<p>
			<label>Sale Date</label>
			<input type="text" name="sale_date">
		</p>
		<p>
			<input type="submit" id="addNew" value="Submit">
		</p>
	</form>

The above HTML code snippets are written under <body/> tag.

First we have <p id=’err’/>, where we will show error message when you try to delete a row from the table but unable to delete the row due to any error.

Next we have Add New Product link inside a <p/> tag, clicking on which the form <form id=”add”…/> is opened in a modal window. I have used modal API to use the modal functionalities.

We have defined <table id=”product-grid” …/> to display the data into HTML table using datatable API.

Now we are done with the HTML markup. Next we will add few libraries into head section of the view file – datatable.php.

<link type="text/css" rel="stylesheet" href="<?php echo base_url(); ?>assets/css/jquery.dataTables.min.css"/>
	<link type="text/css" rel="stylesheet" href="<?php echo base_url(); ?>assets/css/jquery.modal.min.css"/>
	<script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery-1.11.3.min.js"></script>
	<script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery.modal.min.js"></script>
	<script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>

The libraries include datatable API, jQuery API and Modal API.

Now we will define our functions using jQuery API to perform
CRUD operations. The code snippets are written under head section under <script/> tag inside $(document).ready(function () { … }).

We display the records into tabular format using datatable API using the below code snippets:

$('#product-grid').DataTable({
				"processing": true,
				"serverSide": true,
				"ajax": "http://localhost/ci3_datatable/index.php/datatable/get_products"
			});

The below function deletes a particular row from the table:

$(document).delegate('.delete', 'click', function() { 
				if (confirm('Do you really want to delete record?')) {
					var id = $(this).attr('id');
					var parent = $(this).parent().parent();
					$.ajax({
						type: "POST",
						url: "http://localhost/ci3_datatable/index.php/datatable/delete_product",
						data: 'id=' + id,
						cache: false,
						success: function() {
							parent.fadeOut('slow', function() {
								$(this).remove();
							});
						},
						error: function() {
							$('#err').html('<span style=\'color:red; font-weight: bold; font-size: 30px;\'>Error deleting record').fadeIn().fadeOut(4000, function() {
								$(this).remove();
							});
						}
					});
				}
			});

If you face any error during the row deletion then you will see the error message.

Now when you click the Edit button corresponding to a particular row, then your cell values in a row will be edited inline except the Id cell value.

$(document).delegate('.edit', 'click', function() {
				var parent = $(this).parent().parent();
				
				var id = parent.children("td:nth-child(1)");
				var name = parent.children("td:nth-child(2)");
				var price = parent.children("td:nth-child(3)");
				var sale_price = parent.children("td:nth-child(4)");
				var sale_count = parent.children("td:nth-child(5)");
				var sale_date = parent.children("td:nth-child(6)");
				var buttons = parent.children("td:nth-child(7)");
				
				name.html("<input type='text' id='txtName' value='"+name.html()+"'/>");
				price.html("<input type='text' id='txtPrice' value='"+price.html()+"'/>");
				sale_price.html("<input type='text' id='txtSalePrice' value='"+sale_price.html()+"'/>");
				sale_count.html("<input type='text' id='txtSaleCount' value='"+sale_count.html()+"'/>");
				sale_date.html("<input type='text' id='txtSaleDate' value='" + sale_date.html()+"'/>");
				buttons.html("<button id='save'>Save</button>  <button class='delete' id='" + id.html() + "'>Delete</button>");
			});

When you edit a particular row you don’t need to show the Edit button. So we are showing Save and Delete buttons for that row.

Now you have edited a particular row, so you need to save that row. Here is the below function that does the job:

$(document).delegate('#save', 'click', function() {
				var parent = $(this).parent().parent();
				
				var id = parent.children("td:nth-child(1)");
				var name = parent.children("td:nth-child(2)");
				var price = parent.children("td:nth-child(3)");
				var sale_price = parent.children("td:nth-child(4)");
				var sale_count = parent.children("td:nth-child(5)");
				var sale_date = parent.children("td:nth-child(6)");
				var buttons = parent.children("td:nth-child(7)");
				
				$.ajax({
					type: "POST",
					url: "http://localhost/ci3_datatable/index.php/datatable/update_product",
					data: 'id=' + id.html() + '&name=' + name.children("input[type=text]").val() + '&price=' + price.children("input[type=text]").val() + '&sale_price=' + sale_price.children("input[type=text]").val() + '&sale_count=' + sale_count.children("input[type=text]").val() + '&sale_date=' + sale_date.children("input[type=text]").val(),
					cache: false,
					success: function() {
						name.html(name.children("input[type=text]").val());
						price.html(price.children("input[type=text]").val());
						sale_price.html(sale_price.children("input[type=text]").val());
						sale_count.html(sale_count.children("input[type=text]").val());
						sale_date.html(sale_date.children("input[type=text]").val());
						buttons.html("<button class='edit' id='" + id.html() + "'>Edit</button>  <button class='delete' id='" + id.html() + "'>Delete</button>");
					},
					error: function() {
						$('#err').html('<span style=\'color:red; font-weight: bold; font-size: 30px;\'>Error updating record').fadeIn().fadeOut(4000, function() {
							$(this).remove();
						});
					}
				});
			});

You will see error message or success message depending upon whether the edited record gets updated or not.

Now when you click on the link Add New Product then you will see a modal popup opens with a form to input your product information. You will see success or error message according to your product information saved successfully or not. The corresponding code snippets are given below:

$(document).delegate('#addNew', 'click', function(event) {
				event.preventDefault();
				
				var str = $('#add').serialize();
				
				$.ajax({
					type: "POST",
					url: "http://localhost/ci3_datatable/index.php/datatable/add_product",
					data: str,
					cache: false,
					success: function() {
						$("#msgAdd").html( "<span style='color: green'>Product added successfully</span>" );
					},
					error: function() {
						$("#msgAdd").html( "<span style='color: red'>Error adding a new product</span>" );
					}
				});
			});

The whole datatable.php view file looks as below:

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>Datatable CRUD Example using Codeigniter, MySQL, AJAX</title>
	<!--[if IE]> <script> (function() { var html5 = ("abbr,article,aside,audio,canvas,datalist,details," + "figure,footer,header,hgroup,mark,menu,meter,nav,output," + "progress,section,time,video").split(','); for (var i = 0; i < html5.length; i++) { document.createElement(html5[i]); } try { document.execCommand('BackgroundImageCache', false, true); } catch(e) {} })(); </script> <![endif]-->
	<link type="text/css" rel="stylesheet" href="<?php echo base_url(); ?>assets/css/jquery.dataTables.min.css"/>
	<link type="text/css" rel="stylesheet" href="<?php echo base_url(); ?>assets/css/jquery.modal.min.css"/>
	<script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery-1.11.3.min.js"></script>
	<script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery.modal.min.js"></script>
	<script type= 'text/javascript' src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
	<script type= 'text/javascript'>
		$(document).ready(function () {
			$('#product-grid').DataTable({
				"processing": true,
				"serverSide": true,
				"ajax": "http://localhost/ci3_datatable/index.php/datatable/get_products"
			});
			
			$(document).delegate('.delete', 'click', function() { 
				if (confirm('Do you really want to delete record?')) {
					var id = $(this).attr('id');
					var parent = $(this).parent().parent();
					$.ajax({
						type: "POST",
						url: "http://localhost/ci3_datatable/index.php/datatable/delete_product",
						data: 'id=' + id,
						cache: false,
						success: function() {
							parent.fadeOut('slow', function() {
								$(this).remove();
							});
						},
						error: function() {
							$('#err').html('<span style=\'color:red; font-weight: bold; font-size: 30px;\'>Error deleting record').fadeIn().fadeOut(4000, function() {
								$(this).remove();
							});
						}
					});
				}
			});
			
			$(document).delegate('.edit', 'click', function() {
				var parent = $(this).parent().parent();
				
				var id = parent.children("td:nth-child(1)");
				var name = parent.children("td:nth-child(2)");
				var price = parent.children("td:nth-child(3)");
				var sale_price = parent.children("td:nth-child(4)");
				var sale_count = parent.children("td:nth-child(5)");
				var sale_date = parent.children("td:nth-child(6)");
				var buttons = parent.children("td:nth-child(7)");
				
				name.html("<input type='text' id='txtName' value='"+name.html()+"'/>");
				price.html("<input type='text' id='txtPrice' value='"+price.html()+"'/>");
				sale_price.html("<input type='text' id='txtSalePrice' value='"+sale_price.html()+"'/>");
				sale_count.html("<input type='text' id='txtSaleCount' value='"+sale_count.html()+"'/>");
				sale_date.html("<input type='text' id='txtSaleDate' value='" + sale_date.html()+"'/>");
				buttons.html("<button id='save'>Save</button>  <button class='delete' id='" + id.html() + "'>Delete</button>");
			});
			
			$(document).delegate('#save', 'click', function() {
				var parent = $(this).parent().parent();
				
				var id = parent.children("td:nth-child(1)");
				var name = parent.children("td:nth-child(2)");
				var price = parent.children("td:nth-child(3)");
				var sale_price = parent.children("td:nth-child(4)");
				var sale_count = parent.children("td:nth-child(5)");
				var sale_date = parent.children("td:nth-child(6)");
				var buttons = parent.children("td:nth-child(7)");
				
				$.ajax({
					type: "POST",
					url: "http://localhost/ci3_datatable/index.php/datatable/update_product",
					data: 'id=' + id.html() + '&name=' + name.children("input[type=text]").val() + '&price=' + price.children("input[type=text]").val() + '&sale_price=' + sale_price.children("input[type=text]").val() + '&sale_count=' + sale_count.children("input[type=text]").val() + '&sale_date=' + sale_date.children("input[type=text]").val(),
					cache: false,
					success: function() {
						name.html(name.children("input[type=text]").val());
						price.html(price.children("input[type=text]").val());
						sale_price.html(sale_price.children("input[type=text]").val());
						sale_count.html(sale_count.children("input[type=text]").val());
						sale_date.html(sale_date.children("input[type=text]").val());
						buttons.html("<button class='edit' id='" + id.html() + "'>Edit</button>  <button class='delete' id='" + id.html() + "'>Delete</button>");
					},
					error: function() {
						$('#err').html('<span style=\'color:red; font-weight: bold; font-size: 30px;\'>Error updating record').fadeIn().fadeOut(4000, function() {
							$(this).remove();
						});
					}
				});
			});
			
			$(document).delegate('#addNew', 'click', function(event) {
				event.preventDefault();
				
				var str = $('#add').serialize();
				
				$.ajax({
					type: "POST",
					url: "http://localhost/ci3_datatable/index.php/datatable/add_product",
					data: str,
					cache: false,
					success: function() {
						$("#msgAdd").html( "<span style='color: green'>Product added successfully</span>" );
					},
					error: function() {
						$("#msgAdd").html( "<span style='color: red'>Error adding a new product</span>" );
					}
				});
			});
		});
	</script>
	
	<style>
		.modal p { margin: 1em 0; }
		
		.add_form.modal {
		  border-radius: 0;
		  line-height: 18px;
		  padding: 0;
		  font-family: "Lucida Grande", Verdana, sans-serif;
		}

		.add_form h3 {
		  margin: 0;
		  padding: 10px;
		  color: #fff;
		  font-size: 14px;
		  background: -moz-linear-gradient(top, #2e5764, #1e3d47);
		  background: -webkit-gradient(linear,left bottom,left top,color-stop(0, #1e3d47),color-stop(1, #2e5764));
		}

		.add_form.modal p { padding: 20px 30px; border-bottom: 1px solid #ddd; margin: 0;
		  background: -webkit-gradient(linear,left bottom,left top,color-stop(0, #eee),color-stop(1, #fff));
		  overflow: hidden;
		}
		.add_form.modal p:last-child { border: none; }
		.add_form.modal p label { float: left; font-weight: bold; color: #333; font-size: 13px; width: 110px; line-height: 22px; }
		.add_form.modal p input[type="text"],
		.add_form.modal p input[type="submit"]		{
		  font: normal 12px/18px "Lucida Grande", Verdana;
		  padding: 3px;
		  border: 1px solid #ddd;
		  width: 200px;
		}
		
		#msgAdd {
		  margin: 10px;
		  padding: 30px;
		  color: #fff;
		  font-size: 18px;
		  font-weight: bold;
		  background: -moz-linear-gradient(top, #2e5764, #1e3d47);
		  background: -webkit-gradient(linear,left bottom,left top,color-stop(0, #1e3d47),color-stop(1, #2e5764));
		}
	</style>
</head>
<body>
	<p id='err'/>
	
	<p><a class='btn' href="#add" rel="modal:open">Add New Product</a></p>
	
	<table id="product-grid" class="display" cellspacing="0" width="100%">
		<thead>
			<tr>
				<th>Id</th>
				<th>Name</th>
				<th>Price</th>
				<th>Sale Price</th>
				<th>Sale Count</th>
				<th>Sale Date</th>
				<th>Actions</th>
			</tr>
		</thead>
	</table>
	
	<form id="add" action="#" class="add_form modal" style="display:none;">
		<div id='msgAdd'/>
		<h3>Add a new product</h3>
		<p>
			<label>Name</label>
			<input type="text" name="name">
		</p>
		<p>
			<label>Price</label>
			<input type="text" name="price">
		</p>
		<p>
			<label>Sale Price</label>
			<input type="text" name="sale_price">
		</p>
		<p>
			<label>Sale Count</label>
			<input type="text" name="sale_count">
		</p>
		<p>
			<label>Sale Date</label>
			<input type="text" name="sale_date">
		</p>
		<p>
			<input type="submit" id="addNew" value="Submit">
		</p>
	</form>
</body>
</html>

Static Resources

The required assets directory where your static libraries, such as, JavaScript and CSS file are included. You need to put this directory under ci3_datatable directory. Download the assets directory from the below link:

That’s all about our coding part. Now let’s move on to test our application.

Testing the application

Make sure you run the apache http 2.4 server and MySQL server to test the application.

Hit the URL http://localhost/ci3_datatable in the browser to get the home page on browser.

When your home page is open in the browser you will the page looks similar to the below image.

In this page you can search for a particular product, edit an existing product, delete an existing product, add a new product.

If you need only to use datatable for displaying data in a tabular format then you can read
https://roytuts.com/datatable-using-codeigniter-mysql-and-ajax/

When you delete a record, you will get a popup to confirm the deletion. If you press OK then record will be deleted else no.

In this functionality the data gets deleted from the database table first and then the particular row gets deleted from the table.

When you click on Edit button you will see all cells in a particular row get edited with existing values in the input fields. You can change only the field value you want to change, then click on the save button. Here also the record gets saved into database table first then the updated values get reflected on the row.

When you click on Add New Product link you will see a modal window gets open with input fields, where you need to input values and click on the submit button to save the new record.

You will get success or error message depending upon whether the record saved successfully or not.

Hope, you got idea how to create Datatable CRUD Example Codeigniter, MySQL and AJAX.

If you have better idea you can let me know also. You can make improvements if you wish to, such as:

  • Validation when you add new record or edit exiting record
  • Check appropriate date value for date field
  • Better look and feel
  • Better way to handle CRUD operations
  • Better way to server side processing
  • etc…

Source Code

here

Thanks for reading.

1 thought on “DataTable CRUD Example using Codeigniter, MySQL and AJAX

Leave a Reply

Your email address will not be published. Required fields are marked *