Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

  1. Home
  2. ORM

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.



97.7k

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



32.3k
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);
    }


32.3k
Accepted
answer

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



252
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(); }