Codeigniter Active Record: Insert, Select, Update, Delete

By : Paul Jackson Paul Jackson
Hours Updated

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

  • Insert, update and delete records with simple method chains of active record
  • Submits the user input in a secure way using parameters
  • Allows you to work with multiple database engines such as MySQL, SQL Server, etc. without rewriting the application code
  • CodeIgniter uses drivers specific for each database engine in the background.

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,

  • The above code loads the database library when the application starts

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,

  • $data = […] defines an array variable data that uses database table names as array keys and assigns values to them
  • $this->db->insert(‘orders’, $data); calls the insert method of the database library, passes in the table name orders and the array variable $data as the parameter. This line generates the SQL INSERT statement using the array keys as the field names and the array values as the values to be inserted into the database.

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,

  • We define a route ar/insert that calls the store_order of the ActiveRecordController.

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,

  • The above command browser to the command line and start the built-in server at port 3000.

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 Insert Active Record

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,

  • $query = $this->db->get(‘orders’); runs the select query against the orders table selecting all the fields
  • echo “<h4>Orders Listing</h4>”; displays a HTML heading of size 3
  • echo “<ul>”; prints the opening tag for un-ordered HTML list
  • foreach ($query->result() as $row) {…} used the for loop to loop through the results returned from the database. echo “<li>$row->customer_name</li>”; prints the customer_name from the database

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,

  • The route ar points to the index method of ActiveRecordController class. This is by default that’s why we didn’t specify the index method as you did for the route that inserts records

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 Select Active Record

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,

  • $data = […] defines the fields and values that we wish to update in the database table
  • $this->db->where(‘id’, 1); sets the where clause of the update query
  • $this->db->update(‘orders’, $data); generates the SQL update query and executes it against our database.

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

CodeIgniter Update Active Record

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

CodeIgniter Update Active Record

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,

  • $this->db->where(‘id’, 1); sets the where clause
  • $this->db->delete(‘orders’); deletes the database row in orders table based on the criteria set using the where clause.

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: