execute mysql stored procedures with CodeIgniter

[eluser]cjbuchmann[/eluser]
Just wanted to say that I found a decent solution that allows you to pass params and get values :

1. in 'system/application/config/database.php' - change the $db['default']['dbdriver'] to 'mysqli' - (mysql driver doesn't seem to support stored procedures)

2. I created a model for my stored procedure as follows, I put the whole thing in case anyone has any questions, but the focus is on the 'add_movie' function.


#START MODEL

Code:

<?php

class StoredProcedure_model extends Model {
    
    function __construct()
    {
        parent::Model();
    }
    
    function add_movie()
    {
        //post data
        $movie = $_POST['movie'];
        $star = $_POST['star'];
        $genre = $_POST['genre'];
        
        $query = " CALL add_movie(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        
        //the data to replace
        $data = array(
            $movie['name'],
            $movie['date'],
            $movie['director'],
            $movie['banner'],
            $movie['trailer'],
            $star['firstname'],
            $star['lastname'],
            $star['dob'],
            $star['photo'],
            $genre
        );
        
        $result = $this->db->query($query, $this->safe_escape($data));
        $data = "";
        
       foreach($result->result_array() as $row)
       {
            $data.= $row['results']."<br />\r\n";
       }
      
       return $data;
    }
    
    //escapes and adds single quotes
    //to each value of an array
    function safe_escape(&$data)
    {
        if(count($data) <= 0)
        {
            return $data;
        }
        
        foreach($data as $node)
        {
            $node = $this->db->escape($node);
        }
        
        return $data;
    }

}

#END MODEL

3. I then retrieved the data into a variable as I called the function and passed it to my view like so (Note, I'm using the extremely helpful 'Template' code, but you would add the data to a normal view in the same way) :

Code:

//Other Code...
if($this->form_validation->run())
{
    $this->load->model('storedProcedure_model', 'sp');
    
    $data = array();
    $data['result'] = $this->sp->add_movie();
    $this->template->write_view('content', 'display_movie_message', $data);
}
else
{
    $this->template->write_view('content', 'add_movie');    
}
//...Other Code

AND... thats pretty much it.

So to sum up:
1. Change database to mysqli (don't touch the driver class itself)
2. Create a Model class for your stored procedure and add a function to use it
3. Call the stored procedure method from that model class where needed and pass the data to your view in traditional CI form.

Note - My returned data was actually a single table that had a list of messages that were inserted as the procedure ran.


A few things ...
1. Sorry if my Codeigniter code isn't up to par. I'm two days new, so I hope you'll forgive any improper conventions.

2. I noticed that apart from changing the driver, everything else seems to be very standard codeigniter procedure. However, since I saw quite a few methods saying to do various things that weren't very standard, I thought I'd just display the entire process. The hope is that it will eliminate any confusion from other posts.

3. Hope this helps someone