stored procedure returns empty result

Question

Below is my stored procedure:

DELIMITER $$ -- -- Procedures -- DROP PROCEDURE IF EXISTS `checkLogin`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `checkLogin`(IN `uname` VARCHAR(255), IN `pwd` VARCHAR(255)) BEGIN SELECT a.id, a.role_id, b.name FROM userTable as a LEFT JOIN roleTable as b on b.id = a.role_id WHERE a.username = uname AND password = pwd; END$$ DELIMITER ;

Below is my execution code:

 $stmt = $this->dbCon->prepare("CALL checkLogin(?, ?)"); $stmt->bindParam(1, $email, PDO::PARAM_STR, 4000); $stmt->bindParam(2, $password, PDO::PARAM_STR, 4000); // call the stored procedure $stmt->execute(); //var_dump($res); $op = ( $stmt ) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : ''; echo '<pre>'; print_r($op); die;

in the above $this->dbCon is my PDO object.

When I execute this code I am getting only empty result. But when I run the procedure through phpmyadmin it is working fine.

Even it is working fine if I execute through command line:

enter image description here

I tried below method also (Received from Answer), But still its unsuccessful

$stmt = $db->prepare("Call checkLogin(?,?);"); $stmt->execute(array($email,$password)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); if (count($rows) == 0) echo 'Failed Login!'."\n"; else echo 'Logged In...'."\n".print_r($rows,true);

It is because your delimiter $$ is stopping the procedure on the DROP PROCEDURE line. MySQL Stored Procedures In general you're using the delimiter incorrectly as shown. — Jay Blanchard
– Jay Blanchard, Commented Mar 30, 2015 at 13:01
@JayBlanchard Problem not with creating stored procedure. Also, when I execute the procedure from phpmyadmin window, it shows the correct result. When I execute by pdo it is showing empty result.. — Anto S
– Anto S, Commented Mar 30, 2015 at 13:08

sandeep_kosta · Accepted Answer · 2015-04-07 11:54:27Z

you can try certain method, this is another way to pass the key value parameter in execute method directly and naming placeholder should be passed in procedure

$stmt = $this->dbCon->prepare("CALL checkLogin(:Email, :Password)");

$keyArray = array("Email"=>"[email protected]","Password"=>"123456");

$stmt->execute($keyArray);

Robert · Accepted Answer · 2015-03-31 20:30:46Z

This seems to be working for me. But I did not use Bind parameter. The bind seems to work for me also. I also added PDO testing.

Code

//-- Test if PDO is loaded properly. if (extension_loaded ('PDO' )) { echo 'PDO is loaded'."\n"; if (extension_loaded('pdo_mysql')) echo 'PDO mysql driver is loaded.'."\n"; else echo 'PDO mysql driver is NOT loaded.'."\n"; } else { echo 'PDO is NOT loaded!'."\n"; } //-- Run a test $email = '[email protected]'; $password = 'abc123!@#'; $db = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', 'toor'); $stmt = $db->prepare("Call checkLogin(?,?);"); $stmt->execute(array($email,$password)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); if (count($rows) == 0) echo 'Failed Login!'."\n"; else echo 'Logged In...'."\n".print_r($rows,true);

Output:

 PDO is loaded PDO mysql driver is loaded. Logged In... Array ( [0] => Array ( [id] => 1 [role_id] => 1 [name] => edit ) )

PDO working fine in my server, other than this any library to be installed?
You can use the suggestions here to make sure PDO and mysql for PDO is installed for PHP. stackoverflow.com/questions/3131411/…

Collectives™ on Stack Overflow

stored procedure returns empty result

2 Answers 2

Comments

Code

Output:

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Code

Output:

2 Comments

Linked

Related