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](https://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html) In general you're using the delimiter incorrectly as shown. — Jay Blanchard, Mar 30 '15 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, Mar 30 '15 at 13:08

score 3 · Answer 1 · answered Apr 07 '15 at 11:54

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"=>"test@test.com","Password"=>"123456");

$stmt->execute($keyArray);

Robert · Accepted Answer · 2015-03-31T20:30:46.967

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 = 'rob@home.net';
$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? — Anto S, Mar 31 '15 at 08:15
You can use the suggestions here to make sure PDO and mysql for PDO is installed for PHP. http://stackoverflow.com/questions/3131411/php-code-to-test-pdo-is-available — Robert, Mar 31 '15 at 16:48

stored procedure returns empty result

2 Answers2

Code

Output: