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.

 

YOU MIGHT LIKE: