CodeIgniter Display Query Result in View

This tutorial will show you how to display query result in view in codeigniter. Take an example in which you want to select records from the database table and display in html table. If you think about looping through the query results one by one and echoing in view file then scratch it. What we are going to do is far simpler and better.

In Code igniter, generating html table from database query result is a piece of cake. You have to just use it's readily available table class and it will auto generate html table with the given array or database result set for you.

For example, these three lines of code will do the trick.

$this->load->library('table');
$query = $this->db->query("SELECT * FROM mytable");
echo $this->table->generate($query);

Easy isn't it? Now let's see the actual implementation of code.

How to Display Query Result in View in CodeIgniter?

// MySQL Database Structure

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(60) NOT NULL,
  `city` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `users` (`id`, `name`, `email`, `city`) VALUES
(1, 'Jim Connor', 'jimconnor@yahoo.com', 'Las Vegas'),
(2, 'Taylor Fox', 'taylorfox@hotmail.com', 'San Francisco'),
(3, 'Daniel Greyson', 'danielgreyson@hotmail.com', 'New York'),
(4, 'Julia Brown', 'juliabrown@gmail.com', 'Los Angeles'),
(5, 'Rose Harris', 'roseharris@gmail.com', 'New York');

In order to create table, first you'll need to fetch data from DB. So create a codeigniter model function to select records from the database table.

Model:

function getUsers()
{
    return $this->db->get('users');
}

Next create a controller function where you call back the model function created earlier and produce html table with query result and pass it to the view file.

Controller:

function displayUsers()
{
    // load model
    $this->load->model('demo_model');

    // load table library
    $this->load->library('table');
    
    // set table heading
    $this->table->set_heading('ID', 'Name', 'EmailID', 'City');
    
    // set table template
    $style = array('table_open'  => '<table class="table table-bordered table-hover">');
    $this->table->set_template($style);

    // call model function
    $users = $this->demo_model->getUsers();
    
    // generate table from query result
    $data['usertable'] = $this->table->generate($users);
    
    // load view
    $this->load->view('demo_view', $data);
}

Guess above controller function is pretty self-explanatory so I'm not going repeat the process. But about one thing, the method $this->table->generate() will produce basic html table and displaying it in codeigniter view will look plain. So I have added some css styles to the table element which can be set using $this->table->set_template() function.

Finally it's time to display the html table. In codeigniter view, just echo the table object we have generated in the controller function.

View:

<div>
    <?php echo $usertable;?>
</div>

Now you get a nice looking html table displaying user records fetched from the database.

By this way you can pass any type of data to view from controller in code igniter. If records list is long and wish to split the query results across several pages then you can simply add codeigniter pagination to html table.

So, that was all about displaying query result in view using php codeigniter. Just stay tuned to get more updates :)

Tweet