CodeIgniter Database: Config, Update & Delete Data

By : Fiona Brown Fiona Brown
Hours Updated

⚡ Smart Summary

CodeIgniter Database builds models and controllers for a contacts manager backed by MySQL. A shared BaseModel implements insert, read, update, and delete, and child models Pals and Cities inherit it, so controllers reuse the same database operations across the application.

  • 🗄️ Schema: Two related tables, pals and cities, model a one-to-one relationship by foreign key.
  • ⚙️ Config: database.php sets the connection and the database library is loaded.
  • 🧬 BaseModel: A parent model defines get_all, get_by_id, get_where, insert, update, and delete.
  • 👨‍👩‍👧 Inheritance: Pals and Cities extend BaseModel, so they reuse its methods and override where needed.
  • 🔗 Joins: The Pals model joins cities to fetch the city name alongside contact details.
  • 🎛️ Controllers: Cities and Contacts controllers wire models, forms, and validation together.
  • ♻️ Reuse: Inheritance removes repeated CRUD code and keeps models thin.
Read More

CodeIgniter Database

CodeIgniter Database

In the previous tutorial, we 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, you are advised to read the previous tutorial first.

CodeIgniter Database Configuration

We will start by creating the tutorial project database. We will create a simple database for managing contact details, with two tables named pals and the cities 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 the pals table.

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 us 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 defines the basic methods we want 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 given 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 the Pals and Cities child models relate to the parent model BaseModel.

Class diagram of BaseModel with Pals and Cities models

We will create two models as described in the image above. Create a new class BaseModel in application/models/BaseModel.php and 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 is populated by the respective child class to specify which table the base model methods should interact with.
  • public function __construct() {…} defines the constructor and executes the constructor of the parent class CI_Model.
  • get_all() {…} uses the database library and the value of $table to run the SELECT query.
  • get_by_id($id) {…} retrieves a single row and accepts a parameter $id of INT data type.
  • get_where($where) {…} defines a get method that allows you to set a where clause.
  • insert($data) {…} accepts the array parameter $data containing the values to be written to the database.
  • update($id, $data) {…} accepts the array parameter $data containing the values to be updated.
  • delete($id) {…} accepts a parameter $id of INT data type.

Now that we are done with the parent model class, let us create our Pals model. Create a new file in application/models/Pals_model.php and 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_model extends BaseModel {…} extends the parent model BaseModel and automatically makes all its methods accessible to the child class.
  • protected $table = ‘pals’; defines the table name associated with this model.
  • __construct() {…} initializes the parent constructor.
  • public function get_by_id($id) {…} overrides get_by_id to provide a custom implementation. The query uses a join to retrieve the city name from the cities table.
  • public function get_all() {…} overrides get_all to implement a join query between the pals and cities tables.

Create a new file in application/models/Cities_model.php:

<?php
class Cities_model extends BaseModel {
protected $table = 'cities';

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

Here:

  • protected $table = ‘cities’; defines the model database table.

As you can see, inheritance saves us a lot of time when working with models in CodeIgniter.

RELATED ARTICLES

Contacts Manager Controllers

Now that we have created the models, let us create the controllers for our application. We will have two controllers, Cities and Contacts. Let us start with Cities. Create a new file Cities.php in the application/controllers directory and 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 code above implements all the methods needed to create, update, delete, and read rows from the database.

Create another file Contacts.php in application/controllers and 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, and we will use the code we developed there. For completeness, the views of our application will look as follows:

Contacts manager views

You can download the code for the views by clicking the link below:

CodeIgniter Contacts Manager Views Download

FAQs

A BaseModel holds the shared insert, read, update, and delete methods once. Child models inherit them and only override what differs, which removes duplication and keeps each model small.

Each contact stores a city_id, not the city name. The override joins the cities table so the query returns the readable city name alongside each contact in a single result.

The controller loads the model, validates the submitted form, and either saves the data and redirects, or reloads the form with errors. It connects the model, the views, and the validation library.

Yes. From your table definitions, AI can produce a BaseModel with CRUD methods and one child model per table, adding join overrides where a foreign key needs a readable label. Review the generated joins.

The database library provides the raw query and Active Record methods. A model wraps those calls into meaningful, reusable operations for a specific table, keeping data access out of the controller.

Summarize this post with: