Codeigniter Tutorials

Home » Codeigniter »

How to call stored procedure in codeigniter?

User Table :-

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`)
);

Controller Class:-

<?php

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

class SPCRUD extends CI_Controller {

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

public function index() {
$data['users'] = $this->spm->get_user_list();
$this->load->view('users', $data);
}

public function insert() {
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->spm->insert_user($this->input->post('name'), $this->input->post('email'), $this->input->post('phone'), $this->input->post('address'));
redirect('/');
} else {
$data['error'] = 'error occurred during saving data: all fields are required';
$this->load->view('create_user', $data);
}
} else {
$this->load->view('create_user');
}
}

public function update($id) {
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->spm->update_user($id, $this->input->post('name'), $this->input->post('email'), $this->input->post('phone'), $this->input->post('address'));
redirect('/');
} else {
$data['error'] = 'error occurred during saving data: all fields are mandatory';
$this->load->view('update_user', $data);
}
} else {
$data['user'] = $this->spm->get_user($id);
$this->load->view('update_user', $data);
}
}

public function delete($id) {
if ($id) {
$this->spm->delete_user($id);
}
redirect('/');
}
}

Model :-

File Name : Common_model

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

class Common_model extends CI_Model {

private $table_user = 'user';

function get_user_list() {
$sp_read_users = "CALL sp_read_users()";
$query = $this->db->query($sp_read_users);
if ($query) {
return $query->result();
}
return NULL;
}

function get_user($id) {
$sp_read_user = "CALL sp_read_user(?)";
$data = array('id' => $id);
$query = $this->db->query($sp_read_user, $data);
if ($query) {
return $query->row();
}
return NULL;
}

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

function update_user($id, $name, $email, $phone, $address) {
$sp_update_user = "CALL sp_update_user(?, ?, ?, ?, ?)";
$data = array('id' => $id, 'name' => $name, 'email' => $email, 'phone' => $phone, 'address' => $address);
$result = $this->db->query($sp_update_user, $data);
if ($result) {
return $result;
}
return NULL;
}

function delete_user($id) {
$sp_delete_user = "CALL sp_delete_user(?)";
$data = array('id' => $id);
$result = $this->db->query($sp_delete_user, $data);
if ($result) {
return $result;
}
return NULL;
}

}

view

File Name : users.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>CodeIgniter Stored Procedure CRUD Example</title>
</head>
<body>
<div>
<h1>CodeIgniter Stored Procedure Create/Read/Update/Delete Example</h1>
<div>
<?php echo anchor('/spcrud/insert', 'Create');?>
</div>
<div>
<?php
if ($users) {
?>
<table class="datatable">
<thead>
<tr>
<th>Name</th>
<th>Phone</th>
<th>Email</th>
<th>Address</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php
foreach ($users as $user) {
?>
<tr>
<td>
<?php echo $user->name; ?>
</td>
<td>
<?php echo $user->email; ?>
</td>
<td>
<?php echo $user->phone; ?>
</td>
<td>
<?php echo $user->address; ?>
</td>
<td>
<?php echo anchor('/spcrud/update/' . $user->id, 'Update'); ?>

<?php echo anchor('/spcrud/delete/' . $user->id, 'Delete', array('onclick' => "return confirm('Do you want delete this record')")); ?>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
} else {
echo '<div style="color:red;"><p>Record Not Found!</p></div>';
}
?>
</div>
</div>
</body>
</html>

User create

File Name : user_create.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>CodeIgniter Stored Procedure CRUD Example</title>
</head>
<body>
<div id="container">
<h1>CodeIgniter Stored Procedure Create Example</h1>
<div id="body">
<?php
if (isset($error)) {
echo '<p style="color:red;">' . $error . '</p>';
} 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>
</div>
</body>
</html>

update

File Name : user_update.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>CodeIgniter Stored Procedure CRUD Example</title>
</head>
<body>
<div id="container">
<h1>CodeIgniter Stored Procedure Update Example</h1>
<div id="body">
<?php
if (isset($error)) {
echo '<p style="color:red;">' . $error . '</p>';
} 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="<?php echo $user->name; ?>" size="50" />

<h5>Email Address</h5>
<input type="text" name="email" value="<?php echo $user->email; ?>" size="50" />

<h5>Phone No.</h5>
<input type="text" name="phone" value="<?php echo $user->phone; ?>" size="30" />

<h5>Contact Address</h5>
<textarea name="address" rows="5" cols="50"><?php echo $user->address; ?></textarea>

<p><input type="submit" name="submit" value="Submit"/></p>

<?php echo form_close(); ?>
</div>
</div>
</body>
</html>

Itechtuto