Post
Ask Question

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 Models, we have 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


Similar Articles