Get return value from mySQL stored procedure in PHP

I have a stored procedure that return list of users but when I call this procedure in php Just return me this message: {“status”:true,”data”:[{“userID”:0}]}

this is my S.P.

CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUsers`(in userID int )
BEGIN
select userID;
if (userID =0) then
select u.id, u.name, u.email from users u ;

else
select u.id, u.name, u.email from users u where u.id = userid;
end if;
END

and this is my PHP code

 function getAll(){
    $userID = 0;
    try{    
    $this->stmt = $this->pdo->prepare("call getAllUsers(:userID)");
    $this->stmt->bindParam(':userID',$userID, PDO::PARAM_INT);
    $this->stmt->execute();
    $users = $this->stmt->fetchAll();
    return count($users)==0 ? false : $users;
    }
    catch(Exception $ex)
    {
      echo 'Caught exception: ',  $ex->getMessage(), "n";
    }
  }

Edit: I used do/while to fetch all records but now I get extra data about fields spec. This is my new code

 function getAll(){
    $userID = 0;
    try{    
    $this->stmt = $this->pdo->prepare("call getAllUsers(:userID)");
    $this->stmt->bindParam(':userID',$userID, PDO::PARAM_INT);
    $this->stmt->execute();


    do {
      $users = $this->stmt->fetchAll();
      var_dump($users);
  } while ($this->stmt->nextRowset() && $this->stmt->columnCount());

    return count($users)==0 ? false : $users;
    }
    catch(Exception $ex)
    {
      echo 'Caught exception: ',  $ex->getMessage(), "n";
    }
  }

this is result:

array(1) { [0]=> array(1) { ["userID"]=> int(0) } } array(21) { [0]=> array(3) { ["id"]=> int(1) ["name"]=> string(8) "John Doe" ["email"]=> string(12) "john@doe.com" } [1]=> array(3) { ["id"]=> int(2) ["name"]=> string(8) "Jane Doe" ["email"]=> string(12) "jane@doe.com" } [2]=> array(3) { ["id"]=> int(3) ["name"]=> string(9) "Apple Doe" ["email"]=> string(13) "apple@doe.com" } [3]=> array(3) { ["id"]=> int(4) ["name"]=> string(8) "Beck Doe" ["email"]=> string(12) "beck@doe.com" } [4]=> array(3) { ["id"]=> int(5) ["name"]=> string(11) "Charlie Doe" ["email"]=> string(15) "charlie@doe.com" } [5]=> array(3) { ["id"]=> int(6) ["name"]=> string(11) "Charles Doe" ["email"]=> string(15) "charles@doe.com" } [6]=> array(3) { ["id"]=> int(7) ["name"]=> string(8) "Dion Doe" ["email"]=> string(12) "dion@doe.com" } [7]=> array(3) { ["id"]=> int(8) ["name"]=> string(7) "Dee Doe" ["email"]=> string(11) "dee@doe.com" } [8]=> array(3) { ["id"]=> int(9) ["name"]=> string(9) "Emily Doe" ["email"]=> string(13) "emily@doe.com" } [9]=> array(3) { ["id"]=> int(10) ["name"]=> string(9) "Ethan Doe" ["email"]=> string(13) "ethan@doe.com" } [10]=> array(3) { ["id"]=> int(11) ["name"]=> string(9) "Frank Doe" ["email"]=> string(13) "frank@doe.com" } [11]=> array(3) { ["id"]=> int(12) ["name"]=> string(8) "Gina Doe" ["email"]=> string(12) "gina@doe.com" } [12]=> array(3) { ["id"]=> int(13) ["name"]=> string(8) "Hela Doe" ["email"]=> string(12) "hela@doe.com" } [13]=> array(3) { ["id"]=> int(14) ["name"]=> string(10) "Hubert Doe" ["email"]=> string(14) "hubert@doe.com" } [14]=> array(3) { ["id"]=> int(15) ["name"]=> string(7) "Ivy Doe" ["email"]=> string(11) "ivy@doe.com" } [15]=> array(3) { ["id"]=> int(16) ["name"]=> string(10) "Ingrid Doe" ["email"]=> string(14) "ingrid@doe.com" } [16]=> array(3) { ["id"]=> int(17) ["name"]=> string(9) "James Doe" ["email"]=> string(13) "james@doe.com" } [17]=> array(3) { ["id"]=> int(18) ["name"]=> string(8) "Jace Doe" ["email"]=> string(12) "jace@doe.com" } [18]=> array(3) { ["id"]=> int(19) ["name"]=> string(8) "Kate Doe" ["email"]=> string(12) "kate@doe.com" } [19]=> array(3) { ["id"]=> int(20) ["name"]=> string(8) "Luke Doe" ["email"]=> string(12) "luke@doe.com" } [20]=> array(3) { ["id"]=> int(21) ["name"]=> string(4) "3213" ["email"]=> string(21) "heidary@saipacorp.com" } } {"status":true,"data":[{"id":1,"name":"John Doe","email":"john@doe.com"},{"id":2,"name":"Jane Doe","email":"jane@doe.com"},{"id":3,"name":"Apple Doe","email":"apple@doe.com"},{"id":4,"name":"Beck Doe","email":"beck@doe.com"},{"id":5,"name":"Charlie Doe","email":"charlie@doe.com"},{"id":6,"name":"Charles Doe","email":"charles@doe.com"},{"id":7,"name":"Dion Doe","email":"dion@doe.com"},{"id":8,"name":"Dee Doe","email":"dee@doe.com"},{"id":9,"name":"Emily Doe","email":"emily@doe.com"},{"id":10,"name":"Ethan Doe","email":"ethan@doe.com"},{"id":11,"name":"Frank Doe","email":"frank@doe.com"},{"id":12,"name":"Gina Doe","email":"gina@doe.com"},{"id":13,"name":"Hela Doe","email":"hela@doe.com"},{"id":14,"name":"Hubert Doe","email":"hubert@doe.com"},{"id":15,"name":"Ivy Doe","email":"ivy@doe.com"},{"id":16,"name":"Ingrid Doe","email":"ingrid@doe.com"},{"id":17,"name":"James Doe","email":"james@doe.com"},{"id":18,"name":"Jace Doe","email":"jace@doe.com"},{"id":19,"name":"Kate Doe","email":"kate@doe.com"},{"id":20,"name":"Luke Doe","email":"luke@doe.com"},

Answer

My edited code was working correctly. I just need to comment “var_dump($users);”

Advertisement

Answer