Fetch Data From Database and Show in Tabular Format in Codeigniter

In this article, I will show how to fetch data from a database and show it in a view of a tabular format in codeigniter.

Introduction

This tutorial is intended to introduce you to the CodeIgniter framework and the basic principles of MVC architecture. It will show you how a basic CodeIgniter application is constructed and for starters, I will assume that you already have installed CodeIgniter and you have a MySQL database installed on your webserver.

In this article, I will show you how to fetch data from s database and show it in a view in a tabular format in codeigniter.

See the following points.

  1. Create Table in MySQL database

    My database name is "abc" and my table name is "country".
    1. CREATE TABLE country  
    2. (  
    3.    Country_id int,  
    4.    country_name varchar(255)  
    5. );  
    Here is my table structure;

    table

  2. Database connectivity

    You must first edit the file database.php that can be found in application/config/ folder. The important changes are these:
    1. $db['default']['hostname'] = 'localhost';  
    2. $db['default']['username'] = 'root';  
    3. $db['default']['password'] = '';  
    4. $db['default']['database'] = 'abc';  
    5. $db['default']['dbdriver'] = 'mysql';  
  3. Autoload the database

    If your application uses the database very much then you need to change the autoload.php that can also be found inside the application/config/ folder.
    1. $autoload['libraries'] = array('database');   
  4. Config file

    By default, CodeIgniter has one primary config file, located at application/config/config.php.
    1. $config['base_url'] = 'http://localhost/CI/';   
    CI is the folder where my application is present.

  5. The Model

    In the Models we have the CRUD operations. I will create a model named select.php.
    1. <?php  
    2.    class select extends CI_Model  
    3.    {  
    4.       function __construct()  
    5.       {  
    6.          // Call the Model constructor  
    7.          parent::__construct();  
    8.       }  
    9.       //we will use the select function  
    10.       public function select()  
    11.       {  
    12.          //data is retrive from this query  
    13.          $query = $this->db->get('country');  
    14.          return $query;  
    15.       }  
    16.    }  
    17. ?>  
  6. The Controller

    I will create a model named home.php.
    1. <?php  
    2.    class home extends CI_Controller  
    3.    {  
    4.       public function index()  
    5.       {  
    6.          //load the database  
    7.          $this->load->database();  
    8.          //load the model  
    9.          $this->load->model('select');  
    10.          //load the method of model  
    11.          $data['h']=$this->select->select();  
    12.          //return the data in view  
    13.          $this->load->view('select_view'$data);  
    14.       }  
    15.    }  
    16. ?>  
  7. The view

    To display the output we need a view named "select_view". The code will go something like this:
    1. <html xmlns="http://www.w3.org/1999/xhtml">  
    2.    <head>  
    3.       <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />  
    4.       <title>Untitled Document</title>  
    5.    </head>  
    6.    <table border="1">  
    7.       <tbody>  
    8.          <tr>  
    9.             <td>Country Id</td>  
    10.             <td>Country Name</td>  
    11.          </tr>  
    12.          <?php  
    13.          foreach ($h->result() as $row)  
    14.          {  
    15.             ?><tr>  
    16.             <td><?php echo $row->Country_id;?></td>  
    17.             <td><?php echo $row->country_name;?></td>  
    18.             </tr>  
    19.          <?php }  
    20.          ?>  
    21.       </tbody>  
    22.    </table>  
    23. <body>  
    24. </body>  
    25. </html>  
  8. Output

    program output