CakePHP with Stored Procedure

Question

I am trying to create stored procedure in Cakephp. I have created Procedure in MYSQL and created global function in AppModel.php to hit procedure. The function in AppModel is sProcedure. Now I have 2 conditions I might have variable to return from procedure or may have direct resultset eg. I have created for Pagination. Though its shooting my procedure but not return any result. Is my function require modification?

    public function sProcedure($name = NULL, $inputParameter = array(), $outputParameter = array()) {
        $this->begin();
        $parameter = "";
        $outputParam = "";
        foreach ($inputParameter as $params) {
            $parameter .= $parameter == "" ? " '$params' " : ", '$params' ";
        }

        if (count($outputParameter) > 0) {
            foreach ($outputParameter as $prm) {
                $outputParam .= $outputParam == "" ? " @$prm " : ", @$prm ";
            }
        }
        $parameter = ($outputParam) ? $parameter . ", " . $outputParam : $parameter;
        $this->query("CALL `$name`($parameter);");
        $result = $this->query("SELECT $outputParam");
        $this->commit();
        return $result;
    }

    $sel_data = $this->ArticleNews->sProcedure("update_blank", $input_prameter, $output);
    debug($sel_data);

Answer 1

Breaking down the snippet

$this->query("CALL `$name`($parameter);");

calls stored procedure. Lets assume that the procedure's body is a simple SELECT

SELECT table.* FROM table;

This procedure returns a result set. Take a quick look at the code you provided

 $this->query("CALL `$name`($parameter);");

Yes, the procedure is called but the result/resource is not assigned to a variable to iterate over.

$proc_result = $this->query("CALL `$name`($parameter);");

$proc_data = array();

if (false !== $proc_result)
{
    while ($proc_row = mysqli_fetch_array($proc_result))
    {
        $proc_data[] = $proc_row; 
    }
}

if (!empty($proc_data))
{
    //do whatever with procedure data
}

Related documentation: connector-cpp-tutorials-stored-routines-statements