Codeigniter MySQL Stored Procedure CRUD Example

You may also like to read:

Here we will see Codeigniter MySQL Stored Procedure CRUD Example. In my previous example on Call Stored Procedure using Codeigniter, someone had requested me to write an example on Codeigniter MySQL Stored Procedure CRUD, so I am presenting here the same. CRUD means, Create, Read, Update Delete operations.

Prerequisites

Codeigniter 3.1.9

MySQL at least 5.5

Have installed and configured – XAMPP

Please go through the following steps to create the working example:

Step 1. 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 2. Open application/config/database.php file and make required changes for database configurations.

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

Step 3. 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 read/save/update/delete user information into user table.

Create below model class as application/models/spcrud_model.php

<?php

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

class SPCRUD_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;
    }

}

Step 4. Create Controller class that will handle request and response. It will also receive/send data from/to model for performing CRUD operations.

Create below controller class as application/controllers/spcrud.php

<?php

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

class SPCRUD extends CI_Controller {
	
	function __construct() {
        parent::__construct();
        $this->load->model('spcrud_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('/');
    }
}

In the above controller class, after we save user information to the database and immediately redirect user to the home page to show the updated list of users.

Similarly when we update or delete user information then also we redirect end user to the home page to show the updated user information.

Step 5. Here we need to create some view files for handling CRUD operations.

applications/views/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>

applications/views/create_user.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>

applications/views/update_user.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>

Step 6. Update the default controller in application/config/routes.php

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

MySQL table and Stored Procedure

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`roytuts` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `roytuts`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

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=6 DEFAULT CHARSET=latin1;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`email`,`phone`,`address`) values 
(1,'Soumitra Roy','sroy@gmail.com',2147483647,'Earth'),
(2,'Rahul Kumar','rahul@gmail.com',34256780,'Mars');

/* Procedure structure for procedure `sp_delete_user` */

/*!50003 DROP PROCEDURE IF EXISTS  `sp_delete_user` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_user`(IN user_id INT)
BEGIN
		delete from user where id = user_id;
	END */$$
DELIMITER ;

/* Procedure structure for procedure `sp_insert_user` */

/*!50003 DROP PROCEDURE IF EXISTS  `sp_insert_user` */;

DELIMITER $$

/*!50003 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 ;

/* Procedure structure for procedure `sp_read_user` */

/*!50003 DROP PROCEDURE IF EXISTS  `sp_read_user` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_read_user`(IN usr_id INT)
BEGIN
		select * from user where id = usr_id;
	END */$$
DELIMITER ;

/* Procedure structure for procedure `sp_read_users` */

/*!50003 DROP PROCEDURE IF EXISTS  `sp_read_users` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_read_users`()
BEGIN
		select * from user;
	END */$$
DELIMITER ;

/* Procedure structure for procedure `sp_update_user` */

/*!50003 DROP PROCEDURE IF EXISTS  `sp_update_user` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_update_user`(IN usr_id INT, IN fullname VARCHAR(255), IN email VARCHAR(255), IN phone INT, IN address VARCHAR(255))
BEGIN
		update user set name = fullname, email = email, phone = phone, address = address where id = usr_id;
	END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Testing The Application

When your table does not have any data

Codeigniter MySQL Stored Procedure CRUD Example

When your table has some data

Codeigniter MySQL Stored Procedure CRUD Example

When you create a new user information, if you do not fill any field’s value then you will get error.

Codeigniter MySQL Stored Procedure CRUD Example

After adding the new user’s information

Codeigniter MySQL Stored Procedure CRUD Example

When you delete record using Delete link, you will get a confirmation alert box whether you want to delete or not.

Codeigniter MySQL Stored Procedure CRUD Example

After deleting the record

Codeigniter MySQL Stored Procedure CRUD Example

When you update the existing record using the Update link

Codeigniter MySQL Stored Procedure CRUD Example

After updating the list of users becomes

Codeigniter MySQL Stored Procedure CRUD Example

Hope you got an idea on Codeigniter MySQL Stored Procedure CRUD Example.

Thanks for reading.

Codeigniter MySQL Stored Procedure CRUD Example

Leave a Reply

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

Scroll to top