Codeigniter Active Record: Insert, Select, Update, Delete

Details

Data is the bloodline of most applications. The data needs to be stored in such a way that it can be further analyzed to provide insights and facilitate business decisions. The data is usually stored in the database. Among the chief concerns when interacting with the database is security, ease of access and database vendor specific implementations of Structured Query Language (SQL).

Active record is a design pattern that makes it easy to interact with the database in ease, secure and eloquent way.

The active record has the following advantages

In this tutorial, you will learn:

How to use Active Record: Example

In this tutorial, we will discuss the tutorial database. We will have two tables, one with orders the other with details.

This tutorial assumes you have MySQL database installed up and running.

Run the following scripts to create tutorial database: 

CREATE SCHEMA ci_active_record;

USE ci_active_record;

CREATE TABLE `order_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `item` varchar(245) DEFAULT NULL,
  `quantity` int(11) DEFAULT '0',
  `price` decimal(10,2) DEFAULT '0.00',
  `sub_total` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT = 1;

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NULL DEFAULT NULL,
  `customer_name` varchar(245) DEFAULT NULL,
  `customer_address` varchar(245) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT = 1;

The above code creates a database named ci_active_record and creates two tables namely orders and order_details. The relationship between the two tables is defined by the column id in orders and order_id in order_details table.

CodeIgniter Database Configuration

We will now configure our application to be able to communicate with this database.

Open database configuration file located in application/config/database.php

locate the following lines in the configuration file 

'hostname' => 'localhost',
	'username' => '',
	'password' => '',
	'database' => '',

Update the above code to the following 

'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'letmein',
	'database' => 'ci_active_record',

Note: you will need to replace the username and password to the ones that match your configuration of MySQL.

In addition to the database configuration details, we also need to tell CodeIgniter to load the database library when it loads

Step 1) Open the following file application/config/autoload.php

Step 2) Locate the $autoload array key libraries and load the database library as shown below 

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

HERE,

CodeIgniter Insert Active Record

For testing purposes, we will create a controller and defined routes that we will be using to interact with our application via active record.

Create a new file application/controllers/ActiveRecordController.php

Add the following code to ActiveRecordController.php 

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class ActiveRecordController extends CI_Controller {
    public function store_order(){
        $data = [
            'date' => '2018-12-19',
            'customer_name' => 'Joe Thomas',
            'customer_address' => 'US'
        ];

        
        $this->db->insert('orders', $data); 

        echo 'order has successfully been created';
    }
}

HERE,

Now that we have successfully created the controller method for active record, we will now need to create a route that we will call to execute the controller method.

Now open routes.php in application/config/routes.php

add the following line to the routes 

$route['ar/insert'] = 'activerecordcontroller/store_order';

HERE,

Let's now start the web server to test our method.

Run the following command to start the built-in server for PHP 

cd C:\Sites\ci-app
php -S localhost:3000

HERE,

Load the following URL into your browser

http://localhost:3000/ar/insert

You will get the following results 

order has successfully been created

Open the MySQL tutorial database and check the orders table

You will able to newly created row as shown in the image below

CodeIgniter Select Active Record

In this section, we will see how to read the records that we have in the database and display them in the web browser as an unordered list

Add the following method to the ActiveRecordController 

public function index() {
        $query = $this->db->get('orders');
        
        echo "<h3>Orders Listing</h3>";
        echo "<ul>";
        
        foreach ($query->result() as $row) {
            echo "<li>$row->customer_name</li>";
        }
        
        echo "</ul>";
    }

HERE,

Before you load the following URL, you can load a couple more records to the database.

Lets now define a route for the SELECT query

Open application/config/routes.php table

Add the following route 

$route['ar'] = 'activerecordcontroller';

HERE,

Assuming the web server is already running, load the following URL

http://localhost:3000/ar

You should be able to see results which is very much similar to the following in your web browser

CodeIgniter Update Active Record

In this section, we will tal about how to use the active record to update the database. Let's say we want to update the customer name Joe Thomas to Joe.

Add the following method to ActiveRecordController class 

public function update_order() {
        $data = [
            'customer_name' => 'Joe',
        ];
        $this->db->where('id', 1);
        $this->db->update('orders', $data);
        echo 'order has successfully been updated';
    }

HERE,

The above code will produce the following SQL statement 

UPDATE orders SET customer_name = 'Joe' WHERE id = 1;

Let's now update the routes.php application/config/routes.php

Add the following route 

$route['ar/update'] = 'activerecordcontroller/update_order';

Save the changes

Load the following URL in the web browser

Let's now display the database records and see if the changes have been affected.

As you can see from the above-given Image, the first record has been updated from Joe Thomas to Joe.

CodeIgniter Delete Active Record

We will now delete a record from the database. We will delete the record with the id of 3.

Add the following method to the ActiveRecordController 

public function delete_order() {
        $this->db->where('id', 3);
        $this->db->delete('orders');

        echo 'order has successfully been deleted';
    }

HERE,

To execute the above code, load the following URL in our web browser

http://localhost:3000/ar/delete

Summary

In this tutorial, you have learned how to work with an active record to insert, update, delete and select records from the database. We worked with static values to create records, update and delete. In the next tutorial, we will create a user interface that the user can use to create records dynamically in the database.

 

YOU MIGHT LIKE: