Stored procedure in codeigniter

Steps To Call Stored Procedure In Codeigniter:

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.

1. Create a stored procedure for select operation.


CREATE PROCEDURE Select_first ()
BEGIN
SELECT * FROM table_name;
END

Now that the Select_first procedure has been created and defined, we can call this procedure at any time 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.

To call this specific procedure within a query:-

       $data = $this->db->query(“CALL Select_first ()”);
       mysqli_next_result($this->db->conn_id);
       $result = $data->result();

2. Create a stored procedure for a delete operation


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_first(IN p_id int(10))
BEGIN
DELETE FROM table_name 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_first(), 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_first. But, now that we’ve created it, all we need to do is create a variable to call Delete_first() 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.

To call this specific procedure within a query:-

$id = ’13’;
$stored_procedure = “CALL Delete_first(?) “;

$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 for Insert operation.


 

First, we create a procedure with multiple parameters and call it add_insert. Here it’s an INSERT command for MySQL.

CREATE PROCEDURE add_insert(IN L_first VARCHAR(30), IN L_last VARCHAR(30), IN p_mood VARCHAR(30) )
BEGIN
INSERT INTO table_name (first,last,mood) VALUES (L_first,L_last,p_mood);
END

To call this specific procedure within a query:-

$a_procedure = “CALL add_insert(?,?,?)”;
$a_result = $this->db->query( $a_procedure, array(‘first’=>’Foo’,’last’=>’Bar’,’mood’=>’Testy’) );

This would add a row in the table table_name.

Thanks for reading this article.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s