Call Stored Procedure using Codeigniter

This tutorial will show you how to call stored procedure using Codeigniter framework. Here we will use MySQL database for this example.

Prerequisites

Netbeans 8.2
XAMPP in Windows
Codeigniter 3.1.7

Go through below steps in order to complete the example how to call stored procedure using Codeigniter.

Step 1. Create a table called user and Stored Procedure in MySQL database. Create database roytuts in MySQL server if it does not exist already.

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` int(10) unsigned NOT NULL,
  `address` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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

Step 2. Now we will see how to create stored procedure in MySQL database. Here we 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
    PROCEDURE `roytuts`.`insertuser`(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', 'contact@roytuts.com', 1234567890, 'roytuts.com');

Step 3. Now first configure Codeigniter 3. with Netbeans 8.2 IDE if you have not configured already. Setup Codeigniter, Netbeans and XAMPP in Windows

Step 4. Open the file application/config/autoload.php and autoload few libraries and helper functions in order to avoid load every time you want to use.

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

Step 5. Open application/config/database.php file and make required changes for database configurations.

'username' => 'root',
'password' => '',
'database' => 'roytuts',

If you have password for the database then put appropriate password.

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

<?php

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

    function insert_user($name, $email, $phone, $address) {
        $insert_user_stored_proc = "CALL insertuser(?, ?, ?, ?)";
        $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;
    }

}

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

<?php

/**
 * Description of User
 *
 * @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('user'), $this->input->post('email'), $this->input->post('phone'), $this->input->post('address'));
                $data['success'] = $result;
                $this->load->view('user', $data);
            } else {
                $this->load->view('user');
            }
        } else {
            $this->load->view('user');
        }
    }

}

Step 7. Create View file where user will input various information.

<?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>

Step 8. Open file application/config/routes.php and change the default controller.

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

Step 9. Now run 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.

stored procedure using codeigniter

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

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

Thanks for reading.

Soumitra Roy Sarkar

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials Twitter Facebook  Google Plus Linkedin Or Email Me

Leave a Reply

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