Call Stored Procedure using Codeigniter

This tutorial will show you how to call stored procedure using Codeigniter framework.

Prerequisites

Netbeans 8.2
XAMPP in Windows
Codeigniter 3.1.7

Go through below steps

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;

 

Step 1. Create Stored Procedure in MySQL database. Create database roytuts in MySQL server if it does not exist already.

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

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

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

Step 3. Open application/config/autoload.php file and autoload few things

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

Step 4. 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

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

<?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 for user input

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

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 response above the form

User record inserted

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

Leave a Reply

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