The way you call a stored procedure can depend on how you create it. It’s time to discuss three common ways to create and call a stored procedure in CodeIgniter, one with no parameters, one with a parameter, and one with multiple parameters.

Programming with CodeIgniter MVC

1. Create a stored procedure with no parameters.
In many cases, storing MySQL procedures to call on later is ideal if you do it more than once. Before you can call the procedure though you’ll need to create it, define the steps of the procedure. In this example, we’ll create a procedure that has no parameters to pass. The name of this MySQL procedure is Select_foo. We are pulling all the rows and all the fields in those rows from table creatively named table_foo. 

CREATE PROCEDURE Select_foo ()
BEGIN
SELECT * FROM table_foo;
END

Now that the Select_foo procedure has been created and defined, we can call on this procedure at anytime in CodeIgniter. In this example, we create a variable, $data, by pointing to the procedure above after loading and initializing the database class in CodeIgniter.

CodeIgniter for Rapid PHP Application Development

If you are interested in creating your own website using codeigniter framework and mysql as backend database, eknowledgetree linked with dreamhost and providing promo code for $47 discount, to avail this offer click below link How to avail $47 discount dreamhost promo code
To call this specific procedure within a query: 

$data = $this->db->query("CALL Select_foo()");
$result = $data->result();

Did you notice the command CALL? In order to call to any stored procedure, we use CALL followed by the procedure name. In this example, we’ve also placed the query result into the equally exciting variable, $result. $result now contains the all the records from table table_foo, and you can return $result anytime you need later on.

CodeIgniter Web Application Blueprints

2. Create a stored procedure with a single parameter.
What if you want to depend on some parameter to run a procedure? For example, let’s say we want to remove all records in the table table_foo that have a value of 13 for some column called id, simply because we’re superstitious like that.
With a stored procedure that depends on parameters, we can use IN/OUT keyword for passing data into the procedure. Let’s create a stored procedure that will delete a record that has a specific id equal to a variable.

CREATE PROCEDURE delete_bar(IN p_id int(10))
BEGIN
DELETE FROM table_foo WHERE id=p_id ;
END


In parameterized stored procedure you can use IN/OUT keyword for passing the data to the procedure. After IN, we are stating that the parameter value we pass to the procedure delete_bar(), whatever sits between ( and ) will then be known as p_id, which we also state is an integer. The variable p_id doesn’t mean anything to us outside of the procedure delete_bar. But, now that we’ve created it, all we need to do is create a variable to call delete_bar() and have the procedure ask for a parameter, which can be done by inserting a ? between the parentheses that follow the procedure name.
So let’s create a set of variables, one that holds our number 13 and one that calls the procedure to delete rows with a specific id. Just so you know, you can pass a parameter to the procedure from either the model or the controller.

SQL: Learn the Structured Query Language for the Most Popular Databases including Microsoft SQL Server, MySQL, MariaDB, PostgreSQL, and Oracle 
$id = '13';
$stored_procedure = "CALL delete_bar(?) ";

But this won’t work just by itself. As it stands, the value for $id is not passing into the stored procedure, $stored_procedure. We can do this by using CodeIgniter’s database query function to bring both the called stored procedure and the id in one command and do the work for us.

$result = $this->db->query($stored_pocedure,array('id'=>$id));

With this our task is done. The id value passes into the called stored procedure delete_bar and all the rows that contain a value of 13 are deleted from the table. Now you can simply change the value of $id and define $result again to delete any row with a specific value for the column ‘id’.

3. Create a stored procedure with multiple parameters.
So what if you need to call a stored procedure that needs more than one value passed, such as when you add a new row to the table? However many parameters you intend to pass need to be defined in the call to the stored procedure itself. So if, for example, you want to pass a total of three variables into a called stored procedure, then you have to apply a “?” for each parameter needed, separated by a comma, within the parentheses following the name of the stored procedure.
First we create a procedure with multiple parameters, and call it add_foobar. Here it’s an INSERT command for MySQL.
[sociallocker id=”3592″] 

CREATE PROCEDURE add_foobar(IN p_first VARCHAR(30), IN p_last VARCHAR(30), IN p_mood VARCHAR(30) )
BEGIN
INSERT INTO table_foo (first,last,mood) VALUES (p_first,p_last,p_mood);
END

And now to call the procedure add_foobar, and pass the values using an array.

$a_procedure = "CALL add_foobar (?,?,?)";
$a_result = $this->db->query( $a_procedure, array('first'=>'Foo','last'=>'Bar’,'mood'=>'Testy') );

[/sociallocker]
This would add a row in the table table_foo and add an entry for Foo Bar (who happens to be testy).
Already know about stored procedures with CodeIgniter? What are some unorthodox ways you use stored procedures?

Thanks for reading this article.