CODEWITHSUNDEEP
phpmysqlstored-procedurespdo
Pablo Tobar
Pablo Tobar

Reputation: 634

Handle output parameter from MySQL SP with PHP PDO

Could you please help me with this please, my question is about PHP, PDO and stored procedures in MySQL.

I have created a very simple stored procedure called selectAVG that returns the average grade of a subject via an output parameter, here is a stored procedure that I have created in order to test my code(I cannot post the code reffer to selectAVG because of my boss) but I hope with this you can catch up the whole idea.

DELIMITER $$

USE `ejemplo`$$

DROP PROCEDURE IF EXISTS `selectCount_sp`$$

CREATE DEFINER=`root`@`localhost`
PROCEDURE `selectCount_sp`(
OUT totalEmpleados INT    <-- out parameter
)
BEGIN  
      SELECT COUNT(Nombre) INTO totalEmpleados FROM empleado_php;
END$$

DELIMITER ;

and this is my script.php, what it does is to call the stored procedure and get the total of employees via the output parameter

try {   
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $sql = 'CALL selectCount_sp(@total)';
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    $stmt->closeCursor(); //permite limpiar y ejecutar la segunda query

    $r = $conn->query('select @total')->fetch();
        print_r ($r);
    }
catch (PDOException $pe) {
    die("Error occurred:" . $pe->getMessage());
}

my problem is that I cannot get the output value with a simple echo, I have to use print_r in order to get this: 

Array ( [@total] => 5 [0] => 5 )

but I just want to get a simple 5, what do I have to do to show for example: echo "the total is:" .$total and have this: the total is 5 ?

Upvotes: 1

Views: 983

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94662

You can do either of these.

This is simply how to address an item in an array.

echo $r[0];

or

echo $r['@total']

Or it might be better to do 

$r = $conn->query('select @total as total')->fetch();

echo $r['total']

as I am not sure if PHP would like an @ symbol in an array occurance.

Upvotes: 0

Kevin
Kevin

Reputation: 41885

You can use ->fetchColumn() in this case:

$r = $conn->query('select @total');
$total = $r->fetchColumn();

If you have PHP 5.4 or greater, then dereferences will also work:

$total = $conn->query('select @total')->fetch(PDO::FETCH_ASSOC)['@total'];

Upvotes: 2

Related Questions