CodeIgniter Database: Configuration, Edit, Update, Delete Data
Fiona Brown
Codeigniter 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
Codeigniter 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.
