--- description: 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 title: "CodeIgniter Active Record: Insert, Update, Delete Query" image: https://www.guru99.com/images/codeigniter-active-record-crud.png --- [Skip to content](#main) **⚡ Smart Summary** CodeIgniter Active Record is a design pattern that lets a model read and write the database with simple, secure method chains. It supports insert, select, update, and delete across different database engines without rewriting code, and it parameterizes input to prevent SQL injection. - 🔗 **Core Idea:** Active Record wraps SQL in method calls like insert, get, update, and delete. - 🛡️ **Secure:** Values are passed as parameters, which protects against SQL injection. - 🔄 **Portable:** The same code works across MySQL, SQL Server, and others through database drivers. - ⚙️ **Setup:** Configure database.php and autoload the database library. - ➕ **Insert:** $this->db->insert(‘table’, $data) builds and runs an INSERT. - 🔍 **Select:** $this->db->get(‘table’) returns the rows to loop over. - ✏️ **Update and Delete:** A where clause plus update or delete changes or removes rows. Read More ![CodeIgniter Active Record CRUD](https://www.guru99.com/images/codeigniter-active-record-crud.png) Data is the bloodline of most applications. It needs to be stored so that it can be analyzed to provide insights and facilitate business decisions. The data is usually stored in a database. Among the chief concerns when interacting with the database are 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 a secure and eloquent way. The Active Record has the following advantages: - Insert, update, and delete records with simple method chains. - Submits user input securely using parameters. - Allows you to work with multiple database engines such as MySQL and SQL Server without rewriting the application code. - CodeIgniter uses drivers specific to each database engine in the background. ## How to Use Active Record: Example In this tutorial, we will use a sample database with two tables, one with orders and the other with details. This tutorial assumes you have a MySQL database installed and running. Run the following scripts to create the 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 code above creates a database named ci\_active\_record and two tables, orders and order\_details. The relationship between the two tables is defined by the column id in orders and order\_id in order\_details. ## CodeIgniter Database Configuration We will now configure our application to communicate with this database. Open the database configuration file located in application/config/database.php and locate the following lines: ``` 'hostname' => 'localhost', 'username' => '', 'password' => '', 'database' => '', ``` Update the code above to the following: ``` 'hostname' => 'localhost', 'username' => 'root', 'password' => 'letmein', 'database' => 'ci_active_record', ``` **Note:** you will need to replace the username and password with the ones that match your [MySQL](https://www.guru99.com/mysql-tutorial.html) configuration. 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 code above loads the database library when the application starts. ## CodeIgniter Insert Active Record For testing purposes, we will create a controller and define routes to interact with our application via Active Record. Create a new file application/controllers/ActiveRecordController.php and add the following code: ``` '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 column names as array keys and assigns values to them. - $this->db->insert(‘orders’, $data); calls the insert method of the database library, passing in the table name orders and the array variable $data. 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. Now that we have created the controller method, we need to create a route that we will call to execute it. Open application/config/routes.php and add the following line: ``` $route['ar/insert'] = 'activerecordcontroller/store_order'; ``` **Here:** - We define a route ar/insert that calls the store\_order method of the ActiveRecordController. Let us now start the web server to test our method. Run the following command to start the built-in PHP server: ``` cd C:\Sites\ci-app php -S localhost:3000 ``` **Here:** - The command above browses to the project directory and starts the built-in server at port 3000. Load the following URL into your browser: `http://localhost:3000/ar/insert`. You will get the following result: ``` order has successfully been created ``` Open the MySQL tutorial database and check the orders table. You will see the newly created row, as shown in the image below: [![New order row inserted via Active Record]()](https://www.guru99.com/images/1/011119_0545_Codeigniter1.png) **Don't Miss:** - [CodeIgniter Tutorial for Beginners](https://www.guru99.com/codeigniter-tutorial.html) - [CodeIgniter Routes: URL Routing with Example](https://www.guru99.com/codeigniter-url-routing.html) - [How to Upload Image & File in CodeIgniter (with Example)](https://www.guru99.com/codeigniter-file-upload.html) - [How to Send Email using CodeIgniter](https://www.guru99.com/codeigniter-sending-email.html) ## CodeIgniter Select Active Record In this section, we will see how to read the records 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 "

Orders Listing

"; echo ""; } ``` **Here:** - $query = $this->db->get(‘orders’); runs the select query against the orders table, selecting all the fields. - echo “\

Orders Listing\

”; displays an HTML heading. - echo “\