Using CodeIgniter and MySQL Stored Procedure

1. Create the Product object: Create a file under "application/libraries" named Lproduct.php
   

   <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
   
   class Lproduct {
       private $_id = "";
       private $_category = "";
       private $_category_id = "";
       private $_brand = "";
       private $_brand_id = "";
       private $_product_desc = "";
   
       public function get_id() {
           return $this->_id;
       }
   
       public function set_id($_id) {
           $this->_id = $_id;
       }
   
       public function get_category() {
           return $this->_category;
       }
   
       public function set_category($_category) {
           $this->_category = $_category;
       }
   
       public function get_category_id() {
           return $this->_category_id;
       }
   
       public function set_category_id($_category_id) {
           $this->_category_id = $_category_id;
       }
   
       public function get_brand() {
           return $this->_brand;
       }
   
       public function set_brand($_brand) {
           $this->_brand = $_brand;
       }
   
       public function get_brand_id() {
           return $this->_brand_id;
       }
   
       public function set_brand_id($_brand_id) {
           $this->_brand_id = $_brand_id;
       }
   
       public function get_product_desc() {
           return $this->_product_desc;
       }
   
       public function set_product_desc($_product_desc) {
           $this->_product_desc = $_product_desc;
       }
   }
   

2. Create the Model: Create a file under "application/models" named mproduct.php
   

   <?php
   class Mproduct extends CI_Model {
   
       private $_product = null;
   
       public function set_product(Lproduct $product)
       {
           $result = FALSE;
   
           $temp = $product->get_id();
           if ( empty($temp) )
           {
               $data = array(
                   $product->get_product_desc(),
                   $product->get_category_id(),
                   $product->get_brand_id()
               );
   
               try
               {
                   // fix for multi-calls
                   $this->db->reconnect();
   
                   // call the stored procedure
                   $this->db->query("CALL addProduct(?,?,?,@pProdId)", $data);
                   
                   // get the stored procedure returned output
                   $query = $this->db->query('SELECT @pProdId AS product_id');
                   $row = $query->row();
                   if(!empty($row->product_id))
                   {
                       $result = TRUE;
                   }
               }
               catch ( Exception $error_string )
               {
   
               }
           }
           else
           {
               $data = array(
                   $product->get_id(),
                   $product->get_product_desc(),
                   $product->get_category_id(),
                   $product->get_brand_id()
               );
   
               try
               {
                   // fix for multi-calls
                   $this->db->reconnect();
   
                   // call the stored procedure
                   $this->db->query("CALL updateProduct(?,?,?,?,@pResult)", $data);
   
                   // get the stored procedure returned output
                   $query = $this->db->query('SELECT @pResult AS result');
                   $row = $query->row();
                   if(!empty($row->result))
                   {
                       $result = TRUE;
                   }
               }
               catch ( Exception $error_string )
               {
   
               }
           }
   
           $this->_product = $product;
   
           return $result;
       }
   }