In this example I am going to show you how to call stored procedure in CodeIgniter 4. CodeIgniter 4 is a PHP based light weight web based framework for rapid application development. In this example I am going to use MySQL database server for storing data.
Related Posts:
Prerequisites
PHP 7.4.3, Codeigniter 4.0.4/4.1.4, MySQL 8.0.17/8.0.22
Project Directory
It’s assumed that you have already setup PHP and CodeIgniter in Windows system.
Now I will create a project root directory called codeigniter-4-stored-procedure anywhere in the system.
Now move all the directories and files from CodeIgniter framework into the project root 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
For this example I am going to create a table called user under roytuts database. So create a table user under database roytuts in the MySQL server with the below 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 from the bottom of this tutorial.
Stored Procedure
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', 'soumitra@gmail.com', 1234567890, 'roytuts.com');Database Configuration
You need to setup database connection in order to fetch or write data to the table. The following configuration is done in the file app/Config/Database.php under the default group of database setting. Make sure you change or update the configuration according to yours. You can also setup your database using the .env file.
The main properties I have shown below and you can change according to your values:
...
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
...
'charset' => 'utf8mb4',
'DBCollat' => 'utf8mb4_unicode_ci',
...Model Class
The model class will insert the required data into the user table. The following source code is written into app/Models/UserModel.php file.
In CodeIgniter you can choose two ways of modeling your data – using CodeIgniter’s model and using entity class. I am using CodeIgniter’s model to create my own model that extends CodeIgniter’s model class. They come out of the box with helper methods for much of the standard ways you would need to interact with a database table, including finding records, updating records, deleting records, and more.
Here I am executing the stored procedure using the query() function. Inside this query() function you can see that I have called standard stored procedure calling method CALL <stored procedure name>. Also notice how I have bound the parameters with array for place holder (?).
<?php
namespace App\Models;
use CodeIgniter\Model;
class UserModel extends Model {
function insert_user($name, $email, $phone, $address) {
$sql = "CALL sp_insert_user(?, ?, ?, ?)";
$result = $this->db->query($sql, [$name, $email, $phone, $address]);
if ($result) {
return true;
}
return false;
}
}Controller Class
The controller class is responsible for handling request/response from/to end users or clients. The code is written into the file app/Controllers/UserController.php file.
In the index() function I am getting inputs from the client and inserting into the table.
I have also applied validations for input fields using CodeIgniter 4’s validation method.
<?php
namespace App\Controllers;
use App\Models\UserModel;
class UserController extends BaseController {
public function index() {
helper(['form', 'url']);
if($this->request->getPost('submit')) {
$validation = \Config\Services::validation();
$validation->setRules([
'name' => ['label' => 'Full Name', 'rules' => 'required|min_length[3]|max_length[30]'],
'phone' => ['label' => 'Phone No.', 'rules' => 'required|min_length[8]|max_length[20]'],
'address' => ['label' => 'Contact Address', 'rules' => 'required|min_length[10]|max_length[200]'],
'email' => ['label' => 'Email Address', 'rules' => 'required|valid_email|max_length[150]']
]);
if (!$validation->withRequest($this->request)->run()) {
echo view('sp_view', ['errors' => $validation->getErrors()]);
} else {
$model = new UserModel();
$result = $model->insert_user($this->request->getPost('name'), $this->request->getPost('email'), $this->request->getPost('phone'), $this->request->getPost('address'));
if($result) {
echo view('sp_view', ['success' => 'User information successfully saved']);
} else {
echo view('sp_view', ['errors' => 'Oops! something wrong']);
}
}
} else {
echo view('sp_view');
}
}
}View File
You need to provide a user interface in a view file. Therefore you will need to create a view file (for example, sp_view.php) under app/Views folder.
I have applied some basic styles for the web page. I am displaying error or success messages if found.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="shortcut icon" type="image/png" href="/favicon.ico"/>
<title>CodeIgniter 4 MySQL 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($errors)):
?>
<ul style="list-style: none; color: red;">
<?php foreach ($errors as $error) : ?>
<li><?= esc($error) ?></li>
<?php endforeach ?>
</ul>
<?php
endif;
?>
<?php
if(isset($success)):
?>
<ul style="list-style: none; color: green;">
<li><?= esc($success) ?></li>
</ul>
<?php
endif;
?>
<?= form_open('UserController') ?>
<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>Route Configuration
You also need to configure route to point to your own controller file instead of the default controller that comes with the framework.
Search for the line $routes->setDefaultController('Home'); and replace it by $routes->setDefaultController('userController');.
Search for the line $routes->get('/', 'Home::index'); and replace it by your controller name, for this example, $routes->get('/', 'UserController::index');.
These route configurations are done on the file app/Config/Routes.php.
Testing the Application
I am not going to use any external server but CLI command to run the application. Make sure you start the MySQL database server before you start your application. If you want to use external server to run your application you can use. Execute the following command on your project root directory to run your application.
php spark serveYour application will be running on localhost and port 8080.
The URL http://localhost:8080/ will show you the following page on the browser and you can input values and try to submit. On successful insertion you will see the following output:
If there is any error then you will see the following page with error messages:
That’s all about how to call stored procedure using CodeIgniter 4 framework.