How to use database in CodeIgniter

If we can successfully install CodeIgniter into our local server, then we can start working with a database. Just follow the 6 easy steps given below:

(For CodeIgniter setting, you can check my previous post )

##Step 1: Activate database library from autoloader
From application\config\autoload.php we need to load our database library
$autoload['libraries'] = array(); → $autoload['libraries'] = array('database');

##Step 2: Change the database settings
If our database name is test, with the root user and a blank password, we can make change the database setting from the application\config\database.php file

database.php

<?php
$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'test',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);
?>

##Step 3: Create the database in mySQL and insert data into a table
For our case we will create a database named test, then we will create a table and add some sample records like follows

SQL dump

test.db

--
-- Table structure for table `students`
--

CREATE TABLE IF NOT EXISTS `students` (
  `studentID` int(11) DEFAULT NULL,
  `LastName` varchar(255) DEFAULT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `Department` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`studentID`, `LastName`, `FirstName`, `Address`, `Department`) VALUES
(1, 'Ahmed', 'Toufiq', 'Sabae, Fukui', 'Marketting'),
(2, 'Asaduzzaman', 'MD.', 'Fukui, Fukui', 'Electrical'),
(3, 'Ferdous', 'Farhan', 'Sabae, Fukui', 'Robotics'),
(4, 'Shill', 'Sujan Chandra', 'Takefu, Fukui', 'Architechture'),
(5, 'Hamid', 'Syed', 'Fukui, Fukui', 'Sales');

If we import this dump to the mysql 'test' database, the following table will be created.

studentID	LastName	FirstName	Address	Department
1	Ahmed	Toufiq	Sabae, Fukui	Marketting
2	Asaduzzaman	MD.	Fukui, Fukui	Electrical
3	Ferdous	Farhan	Sabae, Fukui	Robotics
4	Shill	Sujan Chandra	Takefu, Fukui	Architechture
5	Hamid	Syed	Fukui, Fukui	Sales

##Step 4: Create a Controller

Now, for a test we will create another Controller named Student (application\controllers\Students.php) as follows:

Students.php

<?php
  class Students extends CI_Controller {
    public function index() {
      $this->load->model('students_model');
      $data['students'] = $this->students_model->get_students();
      $this->load->view('students', $data);
    }
  }

?>

If we look at the index() method of Students controller,

###(a). Load a model

In the 1st line $this->load->model('students_model'); we are calling 'students_model' which we will describe in next section.

###(b). Call a method of the model to fetch data and save into a variable

In the 2nd line we fetch data by $data['students'] = $this->students_model->get_students(); get_students(); method of 'students_model' and save into a variable called $data['students']

###(c). Pass the variable to render into the view page.
In the 3rd line $this->load->view('students', $data); we are rendering '$data['students']' variable to our view page named students, which we will desribe in the following section.

##Step 5: Create a Model

Now, for a test we will create a model named Students_model(application\models\Students_model.php) as follows:

Students_model.php

<?php
class Students_model extends CI_Model {
  public function __construct() {
    $this->load->database();
  }
  public function get_students() {
    $this->db->from('students');
    $query = $this->db->get();
    $query_result = $query->result();
    return $query_result;
  }
}
?>

In this Model, first we are loading the database in the constructor by $this->load->database();

Now, we create the get_students() method for fetching our student records by the CI based query

$this->db->from('students');
$query = $this->db->get();
$query_result = $query->result();

This is equivalent to mySQL command
SELECT * FROM students

##Step 6: Create a View Page to render the data

For our case, we create a view page named students.php (application\views\students.php) as follows:

students.php

<!DOCTYPE html>
<html lang="en">

<head>
	<meta charset="utf-8">
	<title>Data Retrival in CI</title>
</head>
<style>
	table {width: 50%;}
	th{background-color: #ddd;}
	th, td {border: 1px solid #ddd;text-align: center;}
</style>

<body>
	<div>Student Records Table</div>
	<table>
		<?php $i = 0;?>
		<?php if(sizeof($students)):?>
		<tr>
			<th>SL</th>
			<th>Last Name</th>
			<th>First Name</th>
			<th>Department Name</th>
		</tr>
		<?php foreach($students as $student) { ?>
		<tr>
			<td><?php echo ++$i;?></td>
			<td><?php echo $student->LastName;?></td>
			<td><?php echo $student->FirstName;?></td>
			<td><?php echo $student->Department;?></td>
		</tr>
		<?php } ?>
	</table>
	<?php endif;?>
</body>

</html>

If we look at the above view page (students.php), we can access $student array with a simple PHP foreach loop (foreach($students as $student)) and can access the elements by array key by $student->LastName;

If we hit the Students controller (https://codeigniter-test.localhost/Students) we get the following page

Now, if we want to order the records by Department Name in descending order, we can just add one line $this->db->order_by('Department','DESC'); into the Students_model (application\models\Students_model.php) like following

Students_model.php

<?php
class Students_model extends CI_Model {
  public function __construct() {
    $this->load->database();
  }
  public function get_students() {
    $this->db->from('students');
    $this->db->select('LastName, FirstName, Department'); //select the necessary columns only, by default select all columns
    $this->db->order_by('Department', 'DESC'); //order be descending, for ascending use 'ASC'
    $this->db->limit(4);  //for limiting no. of records
    $query = $this->db->get();
    $query_result = $query->result();
    return $query_result;
  }
}
?>

The above query is equivalent to following mySQL command:
SELECT LastName, FirstName, Department FROM students ORDER BY Department DESC LIMIT 4;

However, the result of the above query is as follows:

You can notice that only 4 records out 5 have shown as the query is limited to 4 records only.

For further SQL query you can check https://codeigniter.com/userguide3/database/query_builder.html