We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Stored procedure with FROM as a subquery

How can I fetch the result of a stored procedure which FROM clause is a subquery (that has 3 JOINS)? I did a model ('cause of FROM (...) AS tabla), but it only returns null (I'm using fetchAll()). And in MySQL the procedure works perfectly.



98.9k

Could you please post the code that is not working for you?



33.8k
edited Aug '14

Sorry, didn't remember to do that. It's very simple; I do the same por my login (the only part that doesn't remain equal is the fetchAll(), that is a fetch(), and so on). Tabla is the name that I set to the subquery clause of the FROM (FROM (...) AS tabla).

public static function buscarAlumnos($busqueda = '')
    {
        $sql = "CALL buscarAlumnos('$busqueda');";
        $tabla = new Tabla();
        $resultados = $tabla->getReadConnection()->query($sql)->fetchAll();

        return var_dump($resultados);
    }


33.8k
Accepted
answer

After trying again, it works perfectly. The problem was that (don't know how) I was sending a NULL string.

edited Oct '15

You can use this function to call to stored procedure, place it in your model: /*

  • Call Stored Procedure
  • @author: SirTam
  • @email: [email protected]
  • @param: name is the name of PROC
  • @param: data is the input data for PROC
  • @return: results */ public function callStored($name='',$data=[],$mode='all'){ $db = $this->getDI()->get('db'); foreach($data as $key=>$value){ $value = str_replace('"',"'",$value); $data[$key] = '"'.$value.'"'; } $query = $db->query( 'CALL '.$name.'('.implode(',',$data).')' ); $query->setFetchMode(\Phalcon\Db::FETCH_ASSOC); if($mode=='one'){ return $query->fetch(); } return $query->fetchAll(); }