A TEXT POST

CodeIgniter: mySQL and Stored Procedures

I gave myself a challenge. Creating a Stored Procedure in mySQL and calling it via CodeIgniter. This is PHP/mySQL based. It was an education.

I got this solution from CodeIgniter forums across several posts. I’m posting them all in one post here.

Your typical Stored Procedure call could be

DELIMITER $$

CREATE PROCEDURE `lab101db`.`sp_GetEmployees` ()
BEGIN
    SELECT * FROM dt_employees ORDER BY Surname;
END

You need to change your database driver. The standard mySQL driver used in CodeIgniter does not work. You need mySQLI. Open up your CodeIgniter directory and go to the database file at the following path

(CodeIgniter)\system\application\config\database.php

On line 44, add mysqli to the following line

$db[‘default’][‘dbdriver’] = “mysqli”;

Save the above. Then add the function, below, to your Model class e.g. EmployeeModel.

function getEmployees() {
        $sql = “CALL sp_GetEmployees”;
        $parameters = array();
        $query = $this->db->query($sql, $parameters);

        return $query->result();
    }

In your Controller, add the following call to your Model and store it into an array. Loading the view with the $data array will allow the webpage to loop the collection of employees..

function index()
    {
        $data[‘employees’] = $this->EmployeeModel->getEmployees();
      
        $this->load->view(‘lab_view’, $data);
    }

Finally, in your View, you can now loop through each employee.

foreach ($employees as $employee) {
    echo $employee->FirstName . ” ” .     $employee->LastName . “<br/>”;
}

My output would be displayed like so:

John Cleese
Russel Crow
Michael Ironside
Burt Reynolds

NB: You can send parameters to your Stored Procedure. Adapt the function in your Model to the following.

function getEmployeesByRoleSalary($Role, $Salary) {
        $sql = “CALL sp_GetEmployeesByRoleSalary(?,?)”;
        $parameters = array($Role, $Salary);
        $query = $this->db->query($sql, $parameters);

        return $query->result();
    }

Hopefully, this will point you in the following direction.

(Update: Made a mistake in the last piece of code. I should have added question marks with brackets in place of the parameters. Highlighted in bold)

  1. johnjowens posted this