CodeIgniter Database Tutorial: Create, Update, Delete

Details: Last Updated: 09 February 2021

CodeIgniter Working with Database

In the previous tutorial, we have covered the basics of CodeIgniter active record and how to insert, update, delete and read records from the database. In this tutorial, we will create database models and use forms to create and update database records. if you are entirely new to working with databases in CodeIgniter, then you advised reading the previous tutorial

Database Configuration

We will start by creating the tutorial project database. We will create a simple database for managing contact details. We will create a simple database with two (2) tables names pals and cities that they live in. The relationship between pals and cities is one-to-one with id in cities as the primary key and city_id as the foreign key in pals tables.

Run the following scripts to create the database:

CREATE TABLE `pals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city_id` int(11) DEFAULT NULL,
  `contact_name` varchar(245) DEFAULT NULL,
  `contact_number` varchar(245) DEFAULT NULL,
  `email_address` varchar(245) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Let's now create the cities table

CREATE TABLE `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(245) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

CodeIgniter Database Models

We will now create models for our database. The Model is the M part of the MVC. The model deals with data access, data manipulation, and business logic.

In CodeIgniter, each model has to define the methods that it will support. Instead of repeating the same code in each model, we will take advantage of inheritance in object-oriented programming and create a parent model class that will define the basic methods that we wish our models to support.

The table below shows the methods that we will define and how data will be accessed.

S/N	Method	Description
1	__construct	Defines the constructor method that calls the parent constructor method
2	get_all	Retrieves all the fields and records from the database without any conditions
3	get_by_id	Retrieves a single row from the database using the primary key of INT type named id
4	get_where	Retrieves all fields from the database based on the based in criteria
5	insert	Inserts a new record into the database
6	update	Updates an existing database record based on the primary key of INT type named id
7	delete	Deletes an existing record from the database based on the primary key of INT type named id

The following image shows the class diagram and how Pals and Cities child models relate to the parent model BaseModel.

We will create two models as described in the above image

Create a new class BaseModel in application/models/BaseModel.php

Add the following code

<?php

class BaseModel extends CI_Model {

    protected $table = '';

    public function __construct() {
        parent::__construct();
    }

    public function get_all() {
        return $this->db->get($this->table)
                        ->result();
    }

    public function get_by_id($id) {
        return $this->db->get_where($this->table, array('id' => $id))
                        ->row();
    }

    public function get_where($where) {
        return $this->db->where($where)
                        ->get($this->table)
                        ->result();
    }

    public function insert($data) {
        return $this->db->insert($this->table, $data);
    }

    public function update($id, $data) {
        $this->db->where('id', $id);
        $this->db->update($this->table, $data);
    }

    public function delete($id) {
        $this->db->where('id', $id);
        $this->db->delete($this->table);
    }
}

HERE,

protected $table = ''; defines a protected variable named table. This will be populated by the respective child class to specify which table our base model class methods should interact with.
public function __construct() {…} defines the constructor method and executes the constructor method of the parent class CI_Model.
get_all() {…} uses the database library and the value of the variable $table to run the SELECT query against our database.
get_by_id($id) {…} defines the method for retrieving a single row from the database and accepts a parameter $id that should be of INT data type.
get_where($where) {…} defines the get method that allows you to set a where clause.
insert($data) {…} defines the insert method and accepts the array parameter $data that contains the values to be written to the database.
update($id, $data) {…} defines the update method and accepts the array parameter $data that contains the values to be updated in the database.
delete($id) {…} defines the delete method that accepts a parameter of $id that should be of data type INT.

now that we are done with the parent model class, lets create our Pals models

Create a new file in application/models/Pals.php

Add the following code

<?php

class Pals_model extends BaseModel {

    protected $table = 'pals';

    public function __construct() {
        parent::__construct();
    }

    public function get_by_id($id) {
        $this->db->where('pals.id', $id);
        $this->db->select('pals.*,cities.name');
        $this->db->from('pals');
        $this->db->join('cities', 'pals.city_id = cities.id');
        $query = $this->db->get();
        return $query->row();
    }

    public function get_all() {
        $this->db->select('pals.*,cities.name');
        $this->db->from('pals');
        $this->db->join('cities', 'pals.city_id = cities.id');
        $query = $this->db->get();
        return $query->result();
    }
}

HERE,

class Pals extends BaseModel {…} extends the parent model BaseModel and automatically makes all the methods defined in the BaseModel accessed to the child class.
protected $table = 'pals'; defines the table name associated with our parent model
__construct() {…} initializes the parent constructor
public function get_by_id($id) {…} overrides the get_by_id to provide custom implementation specific to Pals model. The query for get_by_id uses a join to retrieve the city name from cities table
public function get_all() {…} overrides the get_all method to implement a join query between pals and cities table

Create a new file in application/models/Cities.php

<?php
class Cities extends BaseModel {
    protected $table = 'cities';
    
    public function __construct() {
        parent::__construct();
    }
}

HERE,

protected $table = 'cities'; defines the model database table.

As you can see from the above-given code, Inheritance saves us a lot of time when working with models in CodeIgniter. In the next section, we will learn

Contacts Manager Controllers

Now that we have created the models let's create the controllers for our application. We will have two controllers namely Contacts and Cities

Let's start with cities

Create a new file Cities.php in application/controllers directory

Add the following code

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Cities extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->helper('url', 'form');
        $this->load->library('form_validation');
        $this->load->model('cities_model');
    }

    public function index() {
        $header['title'] = 'Cities Listing';
        $data['pals'] = $this->cities_model->get_all();

        $this->load->view('header',$header);
        $this->load->view('cities/index', $data);
        $this->load->view('footer');
    }

    public function create() {
        $header['title'] = 'Create City';
        
        $this->load->view('header',$header);
        $this->load->view('cities/create');
        $this->load->view('footer');
    }

    public function store() {
        $rules = array(
            array(
                'field' => 'name',
                'label' => 'City Name',
                'rules' => 'required'
            )
        );

        $this->form_validation->set_rules($rules);

        if ($this->form_validation->run() == TRUE) {
            $data = array('name' => $this->input->post('name'));
            $this->cities_model->insert($data);

            redirect(base_url('cities'));
        } else {
            $header['title'] = 'Create City';
            
            $this->load->view('header',$header);
            $this->load->view('cities/create');
            $this->load->view('footer');
        }
    }

    public function edit($id) {
        $header['title'] = 'Edit City';
        $data['city'] = $this->cities_model->get_by_id($id);

        $this->load->view('header', $header);
        $this->load->view('cities/edit', $data);
        $this->load->view('footer');
    }

    public function update($id) {
        $rules = array(
            array(
                'field' => 'name',
                'label' => 'City Name',
                'rules' => 'required'
            )
        );

        $this->form_validation->set_rules($rules);

        if ($this->form_validation->run() == TRUE) {
            $data = array('name' => $this->input->post('name'));
            $this->cities_model->update($id,$data);

            redirect(base_url('cities'));
        } else {
            $header['title'] = 'Edit City';
            $data['city'] = $this->cities_model->get_by_id($id);
            
            $this->load->view('header',$header);
            $this->load->view('cities/create',$data);
            $this->load->view('footer');
        }
    }

    public function delete($id) {
        $header['title'] = 'Delete City';
        $data['city'] = $this->cities_model->get_by_id($id);

        $this->load->view('header', $header);
        $this->load->view('cities/delete', $data);
        $this->load->view('footer');
    }

    public function destroy($id) {
        $this->cities_model->delete($id);

        redirect(base_url('cities'));
    }
}

HERE,

The above code implements all the methods needs to create, updated, delete and read rows from the database.

Create another file Contacts.php in application/controllers

Add the following code

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Contacts extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->helper('url', 'form');
        $this->load->library('form_validation');
        $this->load->model('pals_model');
    }

    public function index() {
        $header['title'] = 'Contacts List';
        $data['pals'] = $this->pals_model->get_all();

        $this->load->view('header', $header);
        $this->load->view('contacts/index', $data);
        $this->load->view('footer');
    }

    public function create() {
        $this->load->model('cities_model');
        $header['title'] = 'Create Contact';
        $data['cities'] = $this->cities_model->get_all();

        $this->load->view('header', $header);
        $this->load->view('contacts/create', $data);
        $this->load->view('footer');
    }

    public function store() {
        $rules = array(
            array(
                'field' => 'contact_name',
                'label' => 'Contact Name',
                'rules' => 'required'
            ),
            array(
                'field' => 'contact_number',
                'label' => 'Contact Number',
                'rules' => 'required',
                'errors' => array(
                    'required' => 'You must provide a %s.',
                ),
            ),
            array(
                'field' => 'email_address',
                'label' => 'Email Address',
                'rules' => 'required'
            ),
            array(
                'field' => 'city_id',
                'label' => 'City',
                'rules' => 'required'
            )
        );

        $this->form_validation->set_rules($rules);

        if ($this->form_validation->run() == FALSE) {
            $this->load->model('cities_model');
            $header['title'] = 'Create Contact';
            $data['cities'] = $this->cities_model->get_all();

            $this->load->view('header', $header);
            $this->load->view('contacts/create', $data);
            $this->load->view('footer');
        } else {
            $data = array(
                'city_id' => $this->input->post('city_id'),
                'contact_name' => $this->input->post('contact_name'),
                'contact_number' => $this->input->post('contact_number'),
                'email_address' => $this->input->post('email_address'),
            );

            $this->pals_model->insert($data);

            redirect(base_url('contacts'));
        }
    }

    public function edit($id) {
        $this->load->model('cities_model');
        $header['title'] = 'Edit Contact';
        $data['cities'] = $this->cities_model->get_all();

        $data['pal'] = $this->pals_model->get_by_id($id);
 
        $this->load->view('header', $header);
        $this->load->view('contacts/edit', $data);
        $this->load->view('footer');
    }

    public function update($id) {
        $rules = array(
            array(
                'field' => 'contact_name',
                'label' => 'Contact Name',
                'rules' => 'required'
            ),
            array(
                'field' => 'contact_number',
                'label' => 'Contact Number',
                'rules' => 'required',
                'errors' => array(
                    'required' => 'You must provide a %s.',
                ),
            ),
            array(
                'field' => 'email_address',
                'label' => 'Email Address',
                'rules' => 'required'
            ),
            array(
                'field' => 'city_id',
                'label' => 'City',
                'rules' => 'required'
            )
        );

        $this->form_validation->set_rules($rules);

        if ($this->form_validation->run() == FALSE) {
            $this->load->model('cities_model');
            $header['title'] = 'Create Contact';
            $data['cities'] = $this->cities_model->get_all();

            $data['pal'] = $this->pals_model->get_by_id($id);

            $this->load->view('header', $header);
            $this->load->view('contacts/edit', $data);
            $this->load->view('footer');
        } else {
            $data = array(
                'city_id' => $this->input->post('city_id'),
                'contact_name' => $this->input->post('contact_name'),
                'contact_number' => $this->input->post('contact_number'),
                'email_address' => $this->input->post('email_address'),
            );

            $this->pals_model->update($id, $data);

            redirect(base_url('contacts'));
        }
    }

    public function delete($id) {
        $this->load->model('cities_model');
        $header['title'] = 'Delete Contact';
        $data['cities'] = $this->cities_model->get_all();

        $data['pal'] = $this->pals_model->get_by_id($id);

        $this->load->view('header',$header);
        $this->load->view('contacts/delete',$data);
        $this->load->view('footer');
    }
    
    public function destroy($id){
        $this->pals_model->delete($id);
        
        redirect(base_url('contacts'));
    }
}

Contacts Manager Views

We already looked at forms and validation in CodeIgniter in the previous tutorials. We will use the code that we developed in the previous tutorials. For completeness' sake, we will reproduce the code we created in the previous tutorials.

The views of our application will be as follows

You can download the code for the above views by clicking the link down below

CodeIgniter Contacts Manager Views Download

Summary

In this tutorial, You will learn how to create models in CodeIgniter. We took advantage of inheritance in object-oriented programming to promote code reusability by creating a base model that implements the four major database operations of inserting, reading, updating and deleting.

We have demonstrated the concepts using a practical application, and we shall continue to do so in the next tutorials by adding more functionality to the application.

PHP

PHP Control Structures: If else, Switch Case

What is a control structure? Code execution can be grouped into categories as shown below...

PHP

What is PHP? Write your first PHP Program

What is PHP? PHP is a server side scripting language. that is used to develop Static websites or...

PHP

PHP Security Function: strip_tags, filter_var, Md5 and sha1

Potential security threats They are basically two groups of people that can attack your system...

PHP

PHP Projects: Create an Opinion Poll Application

In this PHP project , we are going to create an opinion poll application. The opinion poll will...

PHP

85 Best Web Development Courses in 2021

Web development is the process of building and maintenance of websites. Web development has a wide...

PHP

CakePHP Tutorial for Beginners: What is, Why Use & Features

What is CakePHP? CakePHP is an open-source framework for the rapid development and maintenance of web...