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.
This code will show error please replace
$result = $this->usermodel->insert_user($this->input->post(‘user’), $this->input->post(’email’), $this->input->post(‘phone’), $this->input->post(‘address’));
with
$result = $this->usermodel->insert_user($this->input->post(‘name’), $this->input->post(’email’), $this->input->post(‘phone’), $this->input->post(‘address’));
.Good Explanation.
Thanks a lot for post.I will be grateful if you post stored procedure with codeigniter for update ,delete the data in the table.