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 <a href="https://qiita.com/amin_ilias/items/8482db3360431ad52e33">post </a>)

##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

```php5: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

```mysql: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:

```php5: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:

```php5: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:

```php5: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
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/488641/169a38b4-cf20-f5ff-0c47-9ee2a7de7d0e.png)

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

```php5: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:

![orderby.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/488641/669e62fa-4f4a-99b7-fa9c-66e016506297.png)
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