PHP 抓MYSQL PROC OUTPUT

edited 三月 2014 in 資料庫
您好:我參考範例,建立proc GetCustomerLevel,可以執行

DELIMITER $$
drop procedure if EXISTS GetCustomerLevel $$
CREATE PROCEDURE GetCustomerLevel(
in p_customerNumber int(11),
out p_customerLevel varchar(10)
)
BEGIN

IF p_customerNumber > 50000 THEN
SET p_customerLevel = 'PLATINUM';
ELSEIF (p_customerNumber <= 50000 AND p_customerNumber >= 10000) THEN
SET p_customerLevel = 'GOLD';
ELSEIF p_customerNumber < 10000 THEN
SET p_customerLevel = 'SILVER';
END IF;

END$$

CALL GetCustomerLevel(50001,@level);
SELECT @level AS level;



接下來, PHP 要去取OUTPUT值,但卻沒有值?
我用print_r($r); 去顯示資料,也是沒有值?


<?php

require_once 'dbconfig.php';

$customerNumber = 103;
try {
$conn = new PDO(\"mysql:host=$host;dbname=$dbname\", $username, $password);
// execute the stored procedure
$sql = 'CALL GetCustomerLevel(:id,@level)';
$stmt = $conn->prepare($sql);

$stmt->bindParam(':id', $customerNumber, PDO::PARAM_INT);
$stmt->execute();
$stmt->closeCursor();
// execute the second query to get customer's level
$r = $conn->query(\"SELECT @level AS level\")->fetch(PDO::FETCH_ASSOC);
if ($r) {
echo sprintf('Customer #%d is %s', $customerNumber, $r['level']);
}
} catch (PDOException $pe) {
die(\"Error occurred:\" . $pe->getMessage());
}

請問者該如何解決?
謝謝!

評論

Sign In or Register to comment.