Retrieve Data From Database Using AJAX & CodeIgniter

Hi! In this tutorial I'm going to show you how to retrieve data from database using ajax in codeigniter. Fetching records from database is one of the primitive processes of CRUD and using jQuery AJAX will allow us to retrieve database records and display it as html table without refreshing page. To make ajax call we are going to use jquery's ajax() method but alternatively you can also use post(). Come, let's see how to send ajax request to fetch rows from database and display it in code igniter view.

CodeIgniter AJAX Retrieve Data from Database

First create database and a table required for our demo and insert some sample records to it.

1. Create MySQL Database:

CREATE DATABASE `db_demo`;
Use `db_demo`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(30) NOT NULL,
  `lname` varchar(30) NOT NULL,
  `email` varchar(60) NOT NULL,
  `credits` int(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `customers` (`id`, `fname`, `lname`, `email`, `credits`) VALUES
(1, 'Caesar', 'Vance', 'caesarvance21@gmail.com', 12),
(2, 'Haley', 'Kennedy', 'kennedyhaley010@outlook.com', 43),
(3, 'Colleen', 'Hurst', 'colleen.hurst.909@yahoo.com', 32),
(4, 'Dai', 'Rios', 'dairios.edinburgh@gmail.com', 87),
(5, 'Quinn', 'Gaines', 'gaines.london.1987@yahoo.com', 26);

2. Create Button to Show Customers Details On Click:

Next add a button to codeigniter view, which will send ajax request when clicked.

<button id="show">Show Cutomers</button>

3. Add Placeholder for Customer Table:

Then you'll need a placeholder to display the records fetched from the database via ajax call. So add a <div> container to the same codeigniter view file.

<div id="customers-list"></div>

4. jQuery AJAX Script:

Next add the below ajax script to codeignitor view file which will be triggered when the user clicks on the 'Show Customers' button we have created earlier.

<script src="<?php echo base_url("js/jquery-1.10.2.js"); ?>" type="text/javascript"></script>

<script type="text/javascript">
$("#show").click(function() {
    $.ajax({
        type: "POST",
        url: "<?php echo site_url('mycontroller/show_customers'); ?>",
        success: function(data) {
            $("#customers-list").html(data);
        }
    });
});
</script>

5. Create Controller Function:

Next you have to create a controller named 'mycontroller' with function show_customers() where the ajax call is sent. This function communicates to the database, retrieve records, create table object from the query result and send it back to the ajax() function.

<?php
class mycontroller extends CI_Controller {

    ...
    ...
    
    function show_customers()
    {
        // load table library
        $this->load->library('table');
        
        // set heading
        $this->table->set_heading('#', 'First Name', 'Last Name', 'Email-ID', 'Credits');

        // set template
        $style = array('table_open'  => '<table class="table table-striped table-hover">');
        $this->table->set_template($style);

        echo $this->table->generate($this->db->get('customers'));
    }
}
?>

Note that we have used code igniter's table library to produce html table. This way it's much simpler and you can also use $this->table->set_template() function of the library to add css styles to the table.

Now run the controller in the browser and click on the 'Show Customers' button. It will display customer's details from the database in a nice html table without page refresh.

Also Read: How to Load View Inside View in CodeIgniter

So that explains about retrieving data from database using codeigniter and ajax. I hope you find this tutorial useful. Meet you in another interesting post.