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.

In this tutorial, you will learn:

How to use Active Record: Example
CodeIgniter Database Configuration
CodeIgniter Insert Active Record
CodeIgniter Select Active Record
CodeIgniter Update Active Record
CodeIgniter Delete Active Record

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: 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 Update the above code to the following 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 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 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 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 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 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 HERE,

$query = $this->db->get(‘orders’); runs the select query against the orders table selecting all the fields echo “

Orders Listing

”; displays a HTML heading of size 3 echo “
    ”; 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 “
  • $row->customer_name
    • ”; 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 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 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 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 Let’s now update the routes.php application/config/routes.php Add the following route 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 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.