Handle output parameter from MySQL SP with PHP PDO

Question

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 ?

Kevin · Accepted Answer · 2015-04-16 23:59:06Z

2

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'];

answered Apr 16, 2015 at 23:59

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Over a year ago

@PabloTobar sure glad this hellped

RiggsFolly · Accepted Answer · 2015-04-17 00:27:13Z

0

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.

edited Apr 17, 2015 at 0:27

answered Apr 16, 2015 at 23:59

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

1 Comment

Pablo Tobar Over a year ago

It does accept the @, thanks for the answer, I guess this helps for more than one output parameter

Collectives™ on Stack Overflow

Handle output parameter from MySQL SP with PHP PDO

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related