MTK MTK - 8 months ago 116
MySQL Question

php PDO result from stored procedure are empty

Here are one example:

SQL:

CREATE PROCEDURE my_sp(
IN in_var VARCHAR(32)
)
BEGIN

-- This is to avoiding the next problem:
-- If I USE bellow: EXECUTE USING in_var NOT WORK
-- But IF I USE: EXECUTE USING @user_invar WORKING GOOD
SET @user_invar = in_var;

SET @query = "SELECT * FROM my_table WHERE my_column = ? LIMIT 1;";

PREPARE stmt FROM @query;
EXECUTE stmt USING @user_invar;
SET @founded_rows = FOUND_ROWS();
DEALLOCATE PREPARE stmt;

IF(@founded_rows = 0)THEN
SELECT 'ZERO_REZULTS' AS RESULTS;
END IF;
END;


PHP:

$dbh = new PDO( $connection_params );

$sql = "CALL my_sp( :in_var )";
$stmt = $dbh->prepare( $sql );
$stmt->execute( array( ':in_var' => $_POST['in_var'] ) );
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

//No errors but $rows is an empty array if @founded_rows is 0
//espected RESULTS = ZERO_REZULTS


The strange thing is that if I execute the procedure my_sp() from phpmyadmin interface the procedure working as espected. I got:

RESULTS = ZERO_REZULTS


EDITED:

In addition of the @Bill Karwin answer, to working as espected the procedure must have the last
SELECT
inside stmt like that:


CREATE PROCEDURE my_sp(
IN in_var VARCHAR(32)
)
BEGIN

SET @user_invar = in_var;

SET @query = "SELECT * FROM my_table WHERE my_column = ? LIMIT 1;";

PREPARE stmt FROM @query;
EXECUTE stmt USING @user_invar;
SET @founded_rows = FOUND_ROWS();
IF(@founded_rows = 0)THEN
SELECT 'ZERO_REZULTS' AS RESULTS;
END IF;
DEALLOCATE PREPARE stmt;


END;

Answer Source

The suggestion that the queries need the same number of columns is not the problem. Stored procs generate multiple distinct result sets, not a UNION.

The first SELECT produces a result set of zero rows. You still have to fetchAll() to consume this rowset, even though it's "empty." Then you have to use nextRowset() to advance to the next rowset returned by the proc.

$dbh = new PDO( $connection_params );

$sql = "CALL my_sp( :in_var )";
$stmt = $dbh->prepare( $sql );
$stmt->execute( array( ':in_var' => $_POST['in_var'] ) );

do {
    $rowset = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($rowset) {
        ...do something with rowset...
    }
} while ($stmt->nextRowset());

See also: http://php.net/manual/en/pdostatement.nextrowset.php

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download