Fetch Data From Database and Show in 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

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now