How to Fetch Data from Database in CodeIgniter

On 3/05/2015

How to fetch data from database in codeigniter? CodeIgniter is the simplest and light PHP framework which lets you create robust web applications in no time. Using Twitter Bootstrap with CodeIgniter saves the hassle of writing CSS Stylesheets for your app and lets you focus on development. Later you can customize bootstrap styles to suit your need. That is the best part of using MVC pattern as the presentation (view) is separate, you can change the look and feel of the app anytime without disturbing the rest of it.

kodingmadesimple.com have good amount of twitter bootstrap tutorials about customizing bootstrap 3 and I recommend you to go through our bootstrap tutorials section.

Now we'll see how to read data from MySQL Database and display it in a neat table format with Bootstrap. Since we are going to use bootstrap we don't want to write any custom stylesheets for formatting the display. If you are not familiar with using Bootstrap with CodeIgniter, then you must read this tutorial on How to integrate Bootstrap with CodeIgniter.

In this CodeIgniter tutorial, I'm going to read data from the MySQL database and display it in a neat table format using Bootstrap3. For example assume we have a DB called employee which has the below two tables.

tbl_dept (int_id | var_dept_name | int_hod)
tbl_emp (int_id | var_emp_name | int_dept_id)

Now I want to display the entire department list along with the head-of-department employee name.

1. First create the model with name department_model.php in the folder system/application/models. Then add a function to read the department list from the DB.

department_model.php
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class department_model extends CI_Model{
     function __construct()
     {
          // Call the Model constructor
          parent::__construct();
     }

     //read the department list from db
     function get_department_list()
     {
          $sql = 'select var_dept_name, var_emp_name from tbl_dept, tbl_emp where tbl_dept.int_hod = tbl_emp.int_id';
          $query = $this->db->query($sql);
          $result = $query->result();
          return $result;
     }
}

2. Next create the controller file with name department.php in the folder system/application/controllers. In this controller call the model function to get the department list and pass it to the view file for presentation.

department.php
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
 class department extends CI_Controller {
     public function __construct()
     {
          parent::__construct();
          $this->load->helper('url');
          $this->load->database();
     }

     public function index()
     {
          //load the department_model
          $this->load->model('department_model');  
          //call the model function to get the department data
          $deptresult = $this->department_model->get_department_list();           
          $data['deptlist'] = $deptresult;
          //load the department_view
          $this->load->view('department_view',$data);
     }
}

3. Finally create the view file with name department_view.php in the folder system/application/views. Now parse the data received from the controller one by one and display it. In order to display the department lists in a neat table format use the bootstrap built-in classes table, table-striped & table-hover.

department_view.php
<html>
     <head>
          <title>Department Master</title>
          <meta name="viewport" content="width=device-width, initial-scale=1.0">
          <!--link the bootstrap css file-->
          <link rel="stylesheet" href="<?php echo base_url("assets/css/bootstrap.css"); ?>">
     </head>
     <body>
          <div class="container">
          <div class="row">
          <div class="col-lg-12 col-sm-12">
               <table class="table table-striped table-hover">
                    <thead>
                         <tr>
                              <th>#</th>
                              <th>Department Name</th>
                              <th>Head of Department</th>
                         </tr>
                    </thead>
                    <tbody>
                         <?php for ($i = 0; $i < count($deptlist); ++$i) { ?>
                              <tr>
                                   <td><?php echo ($i+1); ?></td>
                                   <td><?php echo $deptlist[$i]->var_dept_name; ?></td>
                                   <td><?php echo $deptlist[$i]->var_emp_name; ?></td>
                              </tr>
                         <?php } ?>
                    </tbody>
               </table>
          </div>
          </div>
          </div>
     </body>
</html>

Related: CodeIgniter Pagination using Twitter Bootstrap CSS

Here is the table view of the above example

codeigniter-fetch-data-from-database

As I've said earlier in this tutorial we haven't written any CSS styles of our own, yet we get a neatly formatted table list by using bootstrap classes (Nice isn't it?).

Read:

That explains about fetching data from database in codeigniter and display it in html table using bootstrap.

Last Modified: 29-Sep-2016

11 comments:

  1. Would you like me to share a function that builds a table from an array?? It's just native php not CI ready.

    ReplyDelete
    Replies
    1. Hi, if you intend to do it with php alone try something like this,

      $data = array(
      array("foo", 1),
      array("bar", 2)
      );

      $table = "table thead tr th Column one /th th Column two /th /tr /thead tbody";
      for ($i=0; $i < count($data); $i++)
      {
      $table .= "tr td" . $data[$i][0] . "/td";
      $table .= "td" . $data[$i][1] . "/td /tr ";
      }

      $table .= "/tbody /table";

      echo $table;

      (can't insert html tags here so i have given them without opening and closing brackets, but you include them while trying out this)

      Delete
  2. Replies
    1. Please check this link to add pagination in codeigniter.

      http://www.kodingmadesimple.com/2015/04/php-codeigniter-pagination-twitter-bootstrap-styles.html

      Delete
  3. Is there a github repository of this? Thanks

    ReplyDelete
  4. I've already followed those instructor, but how do I access the view?

    ReplyDelete
  5. how do I access these code? I've tried localhost/project_name/index.php/department/index/department_view.php but it gave me 404

    ReplyDelete
    Replies
    1. Hi! Codeigniter is an mvc and you can't run it like core php file. You have to access the views only through controllers. Try this,

      localhost/project_name/index.php/department

      It will work!

      Cheers.

      Delete
  6. this blog is not written very well with pathing information

    ReplyDelete
  7. this blog is not written very well with pathing information

    ReplyDelete
  8. hi, could you write example for display more tables in a page??

    ReplyDelete

Contact Form

Name

Email *

Message *