How to Fetch Data From Database in CodeIgniter

How to fetch data from database to view? In this tutorial, we're going to fetch list of students from the database and display it in a view. CodeIgniter allows us to connect to our database either through the Controller or the Model. We will be connecting to our database through the controller on this tutorial.
Before we could connect to our database, we need to create one with the following tables.

1. courses
   
   • id(bigint auto increment)
   • course(varchar 255)
   • description(text)
2. students
   
   • id(bigint auto increment)
   • name(varchar 255)
   • course_id(bigint foreign key)

You could either manually create the tables above or just run the following code.

CREATE TABLE `courses` (
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(255) NOT NULL,
 `description` TEXT NOT NULL,
 PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE `students` (
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(255) NOT NULL,
 `course_id` BIGINT(20) NOT NULL,
 PRIMARY KEY (`id`),
 INDEX `student_courses_fk` (`course_id`),
 CONSTRAINT `student_courses_fk` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON UPDATE CASCADE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

At the end of this tutorial, we should be able to display all students with their course and course description.

Step 1. Create Model class

First, we need to create a model class. Make sure to name the file exactly with what you named the class. In our case, we'll be naming it as "Students_model". Take note, CodeIgniter 3 requires capitalization of the first character on both filename and class name.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Students_model extends CI_Model {
 public $variable;
 public function __construct()
 {
  parent::__construct();
  $this->load->database();
 }
 public function getAllStudents(){
  return $this->db->select("t1.name as student , t2.name as course , t2.description as course_description")
  ->from('students as t1')
  ->join('courses as t2','t1.course_id=t2.id','left')
  ->get()
  ->result_array();
 }
}
/* End of file  */
/* Location: ./application/models/ */

Step 2. Create Controller

Now that we defined functions in our model, we need a Controller to call these functions which then be sent to our view. Create a controller with the following code and name it "Students.php".

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Students extends CI_Controller {
 public function __construct()
 {
  parent::__construct();
  $this->load->model('students_model');
 }
 public function index()
 {
  $data['students']=$this->students_model->getAllStudents();
  $this->load->view('student_view',$data);
 }
}
/* End of file  */
/* Location: ./application/controllers/ */

The code simply run the function "getAllStudents" which we defined on our model then return all students together with course and course description. Afterwards, the data is sent to "student_view" for the display.

Step 3. Create View

Finally we need to display the data in our view. Create a new view and name it "student_view" and paste the following code.

<div class="col-xs-12">
 <div class="col-xs-12">
  <div class="col-xs-4">
   <strong>Name</strong>
  </div>
  <div class="col-xs-4">
   <strong>Course</strong>
  </div>
  <div class="col-xs-4">
   <strong>Course Description</strong>
  </div>
 </div>
 <?php foreach((array)$students as $student): ?>
  <div class="col-xs-12">
   <div class="col-xs-4">
    <?=$student['student']?>
   </div>
   <div class="col-xs-4">
    <?=$student['course']?>
   </div>
   <div class="col-xs-4">
    <?=$student['course_description']?>
   </div>
  </div>
 <?php  endforeach; ?>
</div>

The data that was passed on to our view is an array of students where each student contains an array of details. We used CodeIgniter's parser library which I have included on my autoload config. You can find it in "application/config/autoload.php" then find

$autoload['libraries'] = array('parser');