Codeigniter MySQL Stored Procedure CRUD Example

Introduction

Here I am going to show you an example on Codeigniter MySQL Stored Procedure CRUD. You may also read my previous tutorial on Call Stored Procedure using Codeigniter. CRUD means, Create, Read, Update Delete operations.

We can perform the similar the CRUD operations by executing individual SQL statement for each operation.

Prerequisites

Codeigniter 3.1.11, MySQL 8.0.17, Apache HTTP Server 2.4

Create Project Directory

It’s assumed that you have setup Apache 2.4, PHP 7.4.3 and Codeigniter 3.1.11 in Windows system.

Now we will create a project root directory called codeIgniter-mysql-stored-procedure-crud the Apache server’s htdocs folder.

Now move all the directories and files from CodeIgniter 3.1.11 framework into codeIgniter-mysql-stored-procedure-crud directory.

We may not mention the project root directory in subsequent sections and we will assume that we are talking with respect to the project root directory.

MySQL Table/Stored Procedures

Create a table called product in the MySQL database using the following structure.

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` int(10) unsigned NOT NULL,
  `address` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

You can download the full SQL code from the source code section.

Autoload Configuration

We need some configurations, such as, auto-loading for helpers to avoid loading every time we need to use.

Modify application/config/autoload.php file for auto-loading libraries and helper functions.

This one time auto-loading gives flexibility to uniformly use the helpers and libraries anywhere throughout the application without loading repeatedly.

$autoload['libraries'] = array('database', 'form_validation');
$autoload['helper'] = array('url');

Database Configurations

Now we will configure database connection into application/config/database.php file. Please make sure to change according to your database settings.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'root',
	'database' => 'roytuts',
        ...
	'char_set' => 'utf8mb4',
	'dbcollat' => 'utf8mb4_unicode_ci',
        ...
);

Create Model Class

We perform database operations to fetch or delete product data into database.

Create a file spcrud_model.php under applications/models folder to create the required model class.

Here in this model class we will see how to call stored procedure. Using this procedure we will read/save/update/delete user information into user table.

<?php

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

class SPCRUD_model extends CI_Model {

    private $table_user = 'user';
	
	function get_user_list() {
		$sp_read_users = "CALL sp_read_users()";
        $query = $this->db->query($sp_read_users);
        if ($query) {
            return $query->result();
        }
        return NULL;
    }
	
	function get_user($id) {
		$sp_read_user = "CALL sp_read_user(?)";
		$data = array('id' => $id);
        $query = $this->db->query($sp_read_user, $data);
        if ($query) {
            return $query->row();
        }
        return NULL;
    }
	
	function insert_user($name, $email, $phone, $address) {
		$sp_insert_user = "CALL sp_insert_user(?, ?, ?, ?)";
        $data = array('name' => $name, 'email' => $email, 'phone' => $phone, 'address' => $address);
        $result = $this->db->query($sp_insert_user, $data);
        if ($result) {
            return $result;
        }
        return NULL;
    }
	
	function update_user($id, $name, $email, $phone, $address) {
		$sp_update_user = "CALL sp_update_user(?, ?, ?, ?, ?)";
        $data = array('id' => $id, 'name' => $name, 'email' => $email, 'phone' => $phone, 'address' => $address);
        $result = $this->db->query($sp_update_user, $data);
        if ($result) {
            return $result;
        }
        return NULL;
    }
	
	function delete_user($id) {
		$sp_delete_user = "CALL sp_delete_user(?)";
        $data = array('id' => $id);
        $result = $this->db->query($sp_delete_user, $data);
        if ($result) {
            return $result;
        }
        return NULL;
    }

}

Create Controller Class

Create a controller file spcrud.php under application/controllers with the following source code.

The Controller class will handle request and response. It will also receive/send data from/to model for performing CRUD operations.

<?php

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

class SPCRUD extends CI_Controller {
	
	function __construct() {
        parent::__construct();
        $this->load->model('spcrud_model', 'spm');
    }

	public function index() {
		$data['users'] = $this->spm->get_user_list();
		$this->load->view('users', $data);
	}
	
	public function insert() {
        if ($this->input->post('submit')) {
            $this->form_validation->set_rules('name', 'Full Name', 'trim|required');
            $this->form_validation->set_rules('email', 'Email Address', 'trim|required');
            $this->form_validation->set_rules('phone', 'Phone No.', 'trim|required');
            $this->form_validation->set_rules('address', 'Contact Address', 'trim|required');

            if ($this->form_validation->run() !== FALSE) {
                $result = $this->spm->insert_user($this->input->post('name'), $this->input->post('email'), $this->input->post('phone'), $this->input->post('address'));
                redirect('/');
            } else {
				$data['error'] = 'error occurred during saving data: all fields are required';
                $this->load->view('create_user', $data);
            }
        } else {
            $this->load->view('create_user');
        }
    }
	
	public function update($id) {
        if ($this->input->post('submit')) {
            $this->form_validation->set_rules('name', 'Full Name', 'trim|required');
            $this->form_validation->set_rules('email', 'Email Address', 'trim|required');
            $this->form_validation->set_rules('phone', 'Phone No.', 'trim|required');
            $this->form_validation->set_rules('address', 'Contact Address', 'trim|required');

            if ($this->form_validation->run() !== FALSE) {
                $result = $this->spm->update_user($id, $this->input->post('name'), $this->input->post('email'), $this->input->post('phone'), $this->input->post('address'));
                redirect('/');
            } else {
				$data['error'] = 'error occurred during saving data: all fields are mandatory';
                $this->load->view('update_user', $data);
            }
        } else {
			$data['user'] = $this->spm->get_user($id);
            $this->load->view('update_user', $data);
        }
    }
	
	public function delete($id) {
        if ($id) {
            $this->spm->delete_user($id);
        }
		redirect('/');
    }
}

In the above controller class, after we save user information to the database and immediately redirect user to the home page to show the updated list of users.

Similarly when we update or delete user information then also we redirect end user to the home page to show the updated user information.

View Files

Here we need to create some view files for handling CRUD operations.

applications/views/users.php

<?php
	defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>CodeIgniter Stored Procedure CRUD Example</title>
    </head>
    <body>
        <div>
            <h1>CodeIgniter Stored Procedure Create/Read/Update/Delete Example</h1>
			<div>
				<?php echo anchor('/spcrud/insert', 'Create');?>
			</div>
            <div>
                <?php
					if ($users) {
					?>
					<table class="datatable">
						<thead>
							<tr>
								<th>Name</th>
								<th>Phone</th>
								<th>Email</th>
								<th>Address</th>
								<th>Actions</th>
							</tr>
						</thead>
						<tbody>
							<?php
								foreach ($users as $user) {
								?>
									<tr>
										<td>
											<?php echo $user->name; ?>
										</td>
										<td>
											<?php echo $user->email; ?>
										</td>
										<td>
											<?php echo $user->phone; ?>
										</td>
										<td>
											<?php echo $user->address; ?>
										</td>
										<td>
											<?php echo anchor('/spcrud/update/' . $user->id, 'Update'); ?>
											  
											<?php echo anchor('/spcrud/delete/' . $user->id, 'Delete', array('onclick' => "return confirm('Do you want delete this record')")); ?>
										</td>
									</tr>
								<?php
								}
							?>
						</tbody>
					</table>
					<?php
					} else {
						echo '<div style="color:red;"><p>Record Not Found!</p></div>';
					}
				?>
            </div>
        </div>
    </body>
</html>

applications/views/user_create.php

<?php
	defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>CodeIgniter Stored Procedure CRUD Example</title>
    </head>
    <body>
        <div id="container">
            <h1>CodeIgniter Stored Procedure Create Example</h1>
            <div id="body">
                <?php
					if (isset($error)) {
						echo '<p style="color:red;">' . $error . '</p>';
					} else {
						echo validation_errors();
					}
                ?>

                <?php 
					$attributes = array('name' => 'form', 'id' => 'form');
					echo form_open($this->uri->uri_string(), $attributes);
                ?>

                <h5>Full Name</h5>
                <input type="text" name="name" value="" size="50" />

                <h5>Email Address</h5>
                <input type="text" name="email" value="" size="50" />

                <h5>Phone No.</h5>
                <input type="text" name="phone" value="" size="30" />

                <h5>Contact Address</h5>
                <textarea name="address" rows="5" cols="50"></textarea>

                <p><input type="submit" name="submit" value="Submit"/></p>
                
                <?php echo form_close(); ?>
            </div>
        </div>
    </body>
</html>

applications/views/user_update.php

<?php
	defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>CodeIgniter Stored Procedure CRUD Example</title>
    </head>
    <body>
        <div id="container">
            <h1>CodeIgniter Stored Procedure Update Example</h1>
            <div id="body">
                <?php
					if (isset($error)) {
						echo '<p style="color:red;">' . $error . '</p>';
					} else {
						echo validation_errors();
					}
                ?>

                <?php 
					$attributes = array('name' => 'form', 'id' => 'form');
					echo form_open($this->uri->uri_string(), $attributes);
                ?>

                <h5>Full Name</h5>
                <input type="text" name="name" value="<?php echo $user->name; ?>" size="50" />

                <h5>Email Address</h5>
                <input type="text" name="email" value="<?php echo $user->email; ?>" size="50" />

                <h5>Phone No.</h5>
                <input type="text" name="phone" value="<?php echo $user->phone; ?>" size="30" />

                <h5>Contact Address</h5>
                <textarea name="address" rows="5" cols="50"><?php echo $user->address; ?></textarea>

                <p><input type="submit" name="submit" value="Submit"/></p>
                
                <?php echo form_close(); ?>
            </div>
        </div>
    </body>
</html>

Configure Route

We need to replace our default controller in application/config/routes.php file as shown below:

Update the default controller in application/config/routes.php

$route['default_controller'] = 'spcrud';

Testing the Application

When your table does not have any data:

Codeigniter MySQL Stored Procedure CRUD Example

When your table has some data:

Codeigniter MySQL Stored Procedure CRUD Example

When you create a new user information, if you do not fill any field’s value then you will get error.

Codeigniter MySQL Stored Procedure CRUD Example

After adding the new user’s information:

Codeigniter MySQL Stored Procedure CRUD Example

When you delete record using Delete link, you will get a confirmation alert box whether you want to delete or not.

Codeigniter MySQL Stored Procedure CRUD Example

After deleting the record:

Codeigniter MySQL Stored Procedure CRUD Example

When you update the existing record using the Update link:

Codeigniter MySQL Stored Procedure CRUD Example

After updating the list of users becomes:

Codeigniter MySQL Stored Procedure CRUD Example

Hope you got an idea on Codeigniter MySQL Stored Procedure CRUD Example.

Source Code

Download

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *