CRUD (Create, Read, Update, and Delete) these are the basic operations that are performed in any project. In the previous tutorial, I had shared how to insert data into the database using ajax in CodeIgniter 3? In this tutorial, I am going to share with you how to retrieve or fetch data from the database using Ajax in CodeIgniter 3?
In this tutorial, I am using DataTables to display data into a tabular format. Datatable is a plugin for the jQuery JavaScript library that is very helpful to display data in tabular format. It provides many features in a simple way like searching in the table, sorting data, exporting into different types of files, pagination, etc.
Nowadays, DataTables is used in every project to display reports on web pages. Let’s see steps to fetch data from the database using Ajax in CodeIgniter 3 and display it into DataTables.
Video Tutorial: Click here to watch video tutorial
How to fetch data from the database using Ajax in CodeIgniter 3?
Now, we will learn how to retrieve or fetch data from the database using Ajax in CodeIgniter 3 and display it into DataTables.
First, I have created a table. In the previous tutorial, I have setup all basic functionalities of CodeIgniter so in this tutorial, I am sharing only the data fetching process.
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`message` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 Next, I am going to create a view file inside the application >> views folder in my current project.
index.php
<!DOCTYPE html>
<html>
<head>
<title>Fetch data from the database by using Ajax in CodeIgniter 3</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<table id="example1" class="display table">
<thead>
<tr>
<th>S.No</th>
<th>Name</th>
<th>Message</th>
<th>Age</th>
</tr>
</thead>
</table>
</div>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
<script type="text/javascript">
//displaying data on page start here
$(document).ready(function () {
$('#example1').DataTable({
"ajax": "<?php echo base_url('my_controller/fetchDatafromDatabase'); ?>"
});
});
//displaying data on page end here
</script>
</body>
</html>In this file, I have linked two CSS. The first one is for DataTable and the second one is for bootstrap. After that, I have created a simple table structure. In this table, there are four columns: S. No., name, message, and age.
After that, I have linked jQuery, Bootstrap, and DataTable JavaScript files. Next, I have initialized DataTable and call an ajax by back end URL which helps to interact with the server.
Now, I have created a controller file inside the application >> controllers folder.
MY_Controller.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class MY_Controller extends CI_Controller {
public function __construct()
{
parent::__construct();
$this->load->model('ajax_model');
}
public function index()
{
$this->load->view('index');
}
public function fetchDatafromDatabase()
{
$resultList = $this->ajax_model->fetchAllData('*','person',array());
$result = array();
$i = 1;
foreach ($resultList as $key => $value) {
$button = '<a class="btn-sm btn-success text-light" onclick="editFun('.$value['id'].')" href="#"> Edit</a> ';
$button .= ' <a class="btn-sm btn-danger text-light" onclick="deleteFun('.$value['id'].')" href="#"> Delete</a>';
$result['data'][] = array(
$i++,
$value['name'],
$value['message'],
$value['age'],
$button
);
}
echo json_encode($result);
}
}In this controller file, a class MY_Controller extends CI_Controller class which is the base controller of CodeIgniter. A constructor function is calling inside it and it is getting all features of parent class that means CI_Controller class. After that, I have loaded model by $this->load->model();
The index function loads the view page to display in the browser. And then the next function is fetchDatafromDatabase. In this function, fetchAllData function is calling which is in Ajax_model and getting data from ajax model function.
After that, a loop is executing to make an array according to the DataTable column. Here, I am sending data in a two-dimensional array because DataTable takes data in a two-dimensional array format. At last, I have encoded data in JSON format and print this data to send to the view page.
Next page, I have created a Model. In this model, all execution that is linked from the database is executed. The model is created inside the application >> models folder.
Ajax_model.php
<?php
class Ajax_model extends CI_Model
{
public function fetchAllData($data,$tablename,$where)
{
$query = $this->db->select($data)
->from($tablename)
->where($where)
->get();
return $query->result_array();
}
}In this file, a class Ajax_model is created with their same file name and it extends CI_Model which is the base model of CodeIgniter.
Inside it, a function fetchAllData is created and it takes three-parameter. The first parameter takes data that you want to get from the table. If you want to get multiple columns then you can send multiple column names separated by a comma. The second parameter takes the table name and the third parameter takes the where condition in a single-dimensional array format.
Conclusion and Final Words
DataTable has various methods to display data in it. In this tutorial, I have shown a simple method to display data. I will write another tutorial to display data in DataTable with another method that will be very helpful for all of you.
Nowadays, every developer uses DataTable to display data in tabular format because it provides various functions in an easy way such as export option, filter option, sorting option, pagination option, etc.
Always try to use DataTable to display data on the webpage because it is a popular method to display data. All companies use DataTables to show the data.