Call Stored Procedure using Codeigniter

This tutorial will show you how to call stored procedure using CodeIgniter framework. Here I will use MySQL database server to store the data. Stored procedure is used when you want to execute multiple SQL statements in a group to perform certain actions.

Related Posts:

• Call Stored Procedure using CodeIgniter 4
• Codeigniter MySQL Stored procedure CRUD Example
• How to call stored procedure using Python

Prerequisites

Codeigniter 3.1.11, MySQL 8.0.17 – 8.0.22, Apache HTTP Server 2.4

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 I will create a project root directory called codeigniter-mysql-stored-procedure the Apache server’s htdocs folder.

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

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

MySQL Table/Stored Procedure

Create a table called user 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 later fromt he bottom of this tutorial.

Here in the above table I have declared five columns to store information about the users.

Now you will see how to create stored procedure in MySQL database. Here I create the stored procedure called insertuser() with parameters required to store in the above user table. Only the column id is not taken as input parameter because it is the primary key and this column value is auto generated.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_user`(
	IN `fullname` VARCHAR(255),
	IN `email` VARCHAR(255),
	IN `phone` BIGINT,
	IN `address` VARCHAR(255)
)
BEGIN
	INSERT INTO USER(NAME,email,phone,address) VALUES(fullname,email,phone,address);
END//
DELIMITER ;

Usage example of the above stored procedure:

CALL insertuser('Soumitra Roy', 'email@roytuts.com', 1234567890, 'roytuts.com');

Autoload Configuration

You need some configurations, such as, auto-loading for helpers to avoid loading every time you 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 I 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',
        ...
);

Model Class

Create Model class for executing Stored Procedure. Here in this model class you will see how to call stored procedure. Using this procedure you will save user information into user table.

The following code is written into usermodel.php file under application/models folder.

<?php

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

/**
 * Description of User
 *
 * @author roytuts.com
 */
class UserModel extends CI_Model {

    function insert_user($name, $email, $phone, $address) {
        $insert_user_stored_proc = "CALL sp_insert_user(?, ?, ?, ?)";
        $data = array('name' => $name, 'email' => $email, 'phone' => $phone, 'address' => $address);
        $result = $this->db->query($insert_user_stored_proc, $data);
        if ($result !== NULL) {
            return TRUE;
        }
        return FALSE;
    }

}

Controller Class

Create Controller class that will handle request and response. It will also receive data from model and send it to the end user.

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

<?php

/**
 * Description of UserController
 *
 * @author roytuts.com
 */
defined('BASEPATH') OR exit('No direct script access allowed');

class UserController extends CI_Controller {

    function __construct() {
        parent::__construct();
        $this->load->model('usermodel');
    }

    public function index() {
        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->usermodel->insert_user($this->input->post('name'), $this->input->post('email'), $this->input->post('phone'), $this->input->post('address'));
                $data['success'] = $result;
                $this->load->view('sp_view', $data);
            } else {
                $this->load->view('sp_view');
            }
        } else {
            $this->load->view('sp_view');
        }
    }

}

View File

Create View file where user will input various information. The code is written into sp_view.php file under applications/views folder.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?><!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>CodeIgniter Stored Procedure Example</title>

        <style type="text/css">

            ::selection { background-color: #E13300; color: white; }
            ::-moz-selection { background-color: #E13300; color: white; }

            body {
                background-color: #fff;
                margin: 40px;
                font: 13px/20px normal Helvetica, Arial, sans-serif;
                color: #4F5155;
            }

            a {
                color: #003399;
                background-color: transparent;
                font-weight: normal;
            }

            h1 {
                color: #444;
                background-color: transparent;
                border-bottom: 1px solid #D0D0D0;
                font-size: 19px;
                font-weight: normal;
                margin: 0 0 14px 0;
                padding: 14px 15px 10px 15px;
            }

            code {
                font-family: Consolas, Monaco, Courier New, Courier, monospace;
                font-size: 12px;
                background-color: #f9f9f9;
                border: 1px solid #D0D0D0;
                color: #002166;
                display: block;
                margin: 14px 0 14px 0;
                padding: 12px 10px 12px 10px;
            }

            #body {
                margin: 0 15px 0 15px;
            }

            p.footer {
                text-align: right;
                font-size: 11px;
                border-top: 1px solid #D0D0D0;
                line-height: 32px;
                padding: 0 10px 0 10px;
                margin: 20px 0 0 0;
            }

            #container {
                margin: 10px;
                border: 1px solid #D0D0D0;
                box-shadow: 0 0 8px #D0D0D0;
            }
        </style>
    </head>
    <body>

        <div id="container">
            <h1>CodeIgniter Stored Procedure Example</h1>

            <div id="body">
                <?php
                if (isset($success)) {
                    echo 'User record inserted';
                } 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>

            <p class="footer">Page rendered in <strong>{elapsed_time}</strong> seconds. <?php echo (ENVIRONMENT === 'development') ? 'CodeIgniter Version <strong>' . CI_VERSION . '</strong>' : '' ?></p>
        </div>

    </body>
</html>

Configure Route

You need to replace the default controller configuration in application/config/routes.php file as shown below.

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

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

Testing the Application

When you see the form and you do not enter anything and try to submit the form and you should see the below errors above the form.

Once you fill the form with all input fields and you will find one row has been inserted into the database table and you will see the below response above the form.

User record inserted

The record in the table inserted as shown in below image:

Now hope you have got an idea how to call stored procedure using Codeigniter. You can now implement this concept into your own project.

Source Code

Download