ตอนที่ 19 : PHP เรียกใช้ Call MySQL Stored Procedure และการส่งค่า Parameters ด้วย mysqli |
ตอนที่ 19 : PHP เรียกใช้ Call MySQL Stored Procedure และการส่งค่า Parameters ด้วย mysqli ในหัวข้อนี้เราจะมาเรียนรู้วิธีการเรียกใช้งาน Stored Procedure ของ MySQL Database ด้วย PHP ซึ่งในการเรียกใช้งาน Stored Procedure จะต้องใช้ function ของ mysqli หรือในฟังก์ชั่นรุ่นใหม่ ๆ เช่น PDO ส่วนการเรียกนั้น เราสามารถใช้คำสั่ง CALL procedure_name() ได้ทันที ถ้าหากมีค่า Parameters ก็จะต้องทำการ Pass ค่าไปกับ Query ด้วย หลังจากนั้นก็สามารถใช้คำสั่ง mysqli_query() ข้อมูลได้เลย พร้อมกับอ่านค่า Result ที่ Stored Procedure นั้นได้ส่งกลับมาให้
Call MySQL Stored Procedure
CALL procedure_name(agr1,agr2,...)
โครงสร้างของตาราง customer
Table : customer
Example 1 : การใช้ PHP เรียก Stored Procedure แบบ Query ข้อมูลออกมา
Stored Procedure Name : getCustomer()
DROP PROCEDURE IF EXISTS getCustomer;
DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
BEGIN
SELECT * FROM customer WHERE COUNTRY_CODE = pCountryCode;
END //
DELIMITER ;
Code 1 : การเรียกผ่าน CALL
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "root";
$userPassword = "root";
$dbName = "mydatabase";
$conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);
$sql = "CALL getCustomer('US')";
$query = mysqli_query($conn,$sql);
?>
<table width="600" border="1">
<tr>
<th width="91"> <div align="center">CustomerID </div></th>
<th width="98"> <div align="center">Name </div></th>
<th width="198"> <div align="center">Email </div></th>
<th width="97"> <div align="center">CountryCode </div></th>
<th width="59"> <div align="center">Budget </div></th>
<th width="71"> <div align="center">Used </div></th>
</tr>
<?php
while($result=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
?>
<tr>
<td><div align="center"><?php echo $result["CUSTOMER_ID"];?></div></td>
<td><?php echo $result["NAME"];?></td>
<td><?php echo $result["EMAIL"];?></td>
<td><div align="center"><?php echo $result["COUNTRY_CODE"];?></div></td>
<td align="right"><?php echo $result["BUDGET"];?></td>
<td align="right"><?php echo $result["USED"];?></td>
</tr>
<?php
}
?>
</table>
<?php
mysqli_close($conn);
?>
</body>
</html>
Result
ผลลัพธ์ทีไ่ด้ สามารถประยุกต์ใช้ได้กับการ Prepare Statement ของ mysqli ได้
Code 1 : การเรียกผ่าน CALL และการทำ Prepare Statement
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "root";
$userPassword = "root";
$dbName = "mydatabase";
$conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);
$strCountryCode = "US";
$sql = "CALL getCustomer(?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $strCountryCode); // s - string, b - blob, i - int, etc
$stmt ->execute();
$result = $stmt->get_result();
?>
<table width="600" border="1">
<tr>
<th width="91"> <div align="center">CustomerID </div></th>
<th width="98"> <div align="center">Name </div></th>
<th width="198"> <div align="center">Email </div></th>
<th width="97"> <div align="center">CountryCode </div></th>
<th width="59"> <div align="center">Budget </div></th>
<th width="71"> <div align="center">Used </div></th>
</tr>
<?php
while($row = $result->fetch_assoc())
{
?>
<tr>
<td><div align="center"><?php echo $row["CUSTOMER_ID"];?></div></td>
<td><?php echo $row["NAME"];?></td>
<td><?php echo $row["EMAIL"];?></td>
<td><div align="center"><?php echo $row["COUNTRY_CODE"];?></div></td>
<td align="right"><?php echo $row["BUDGET"];?></td>
<td align="right"><?php echo $row["USED"];?></td>
</tr>
<?php
}
?>
</table>
<?php
mysqli_close($conn);
?>
</body>
</html>
Example 2 : การใช้ PHP เรียก Stored Procedure แบบ Query เพื่อ Insert ข้อมูล
Stored Procedure Name : insertCustomer()
DROP PROCEDURE IF EXISTS insertCustomer;
DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
IN pName VARCHAR(150),
IN pEmail VARCHAR(150),
IN pCountryCode VARCHAR(2),
IN pBudget DECIMAL(18,2),
IN pUsed DECIMAL(18,2))
BEGIN
INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
END //
DELIMITER ;
add.php
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<form action="save.php" name="frmAdd" method="post">
<table width="284" border="1">
<tr>
<th width="120">CustomerID</th>
<td width="238"><input type="text" name="txtCustomerID" size="5"></td>
</tr>
<tr>
<th width="120">Name</th>
<td><input type="text" name="txtName" size="20"></td>
</tr>
<tr>
<th width="120">Email</th>
<td><input type="text" name="txtEmail" size="20"></td>
</tr>
<tr>
<th width="120">CountryCode</th>
<td><input type="text" name="txtCountryCode" size="2"></td>
</tr>
<tr>
<th width="120">Budget</th>
<td><input type="text" name="txtBudget" size="5"></td>
</tr>
<tr>
<th width="120">Used</th>
<td><input type="text" name="txtUsed" size="5"></td>
</tr>
</table>
<input type="submit" name="submit" value="submit">
</form>
</body>
</html>
save.php
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "root";
$userPassword = "root";
$dbName = "mydatabase";
$conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);
$strCustomerID = $_POST["txtCustomerID"];
$strName = $_POST["txtName"];
$strEmail = $_POST["txtEmail"];
$strCountryCode = $_POST["txtCountryCode"];
$strBudget = $_POST["txtBudget"];
$strUsed = $_POST["txtUsed"];
$sql = "CALL insertCustomer('$strCustomerID', '$strName', '$strEmail', '$strCountryCode', '$strBudget', '$strUsed')";
$query = mysqli_query($conn,$sql);
if(!$query) {
echo mysqli_error($conn);
}
else
{
echo "Record add successfully";
}
mysqli_close($conn);
?>
</body>
</html>
Result
Insert ข้อมูลและ Save
ผลลัพธ์ที่ได้
สามารถประยุกต์ในรูปแบบของการ Prepare Statement
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "root";
$userPassword = "root";
$dbName = "mydatabase";
$conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);
$strCustomerID = $_POST["txtCustomerID"];
$strName = $_POST["txtName"];
$strEmail = $_POST["txtEmail"];
$strCountryCode = $_POST["txtCountryCode"];
$strBudget = $_POST["txtBudget"];
$strUsed = $_POST["txtUsed"];
$sql = "CALL insertCustomer(?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssii', $strCustomerID, $strName, $strEmail, $strCountryCode, $strBudget, $strUsed); // s - string, b - blob, i - int, etc
$stmt ->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
$conn->close();
?>
</body>
</html>
ส่วนวิธีการ UPDATE และ DELETE ก็ใช้หลักการเดียวกับการ INSERT ข้อมูล
สำหรับตัวอย่างที่ 3 และ 4 จะเป็นการอ่านจาก OUTPUT หรือ OUT
ตอนที่ 9 : การใช้พารามิเตอร์ชนิด OUT เพื่อส่งค่ากลับ (MySQL : Stored Procedure)
Example 3 : การใช้ PHP เรียก Stored Procedure และอ่านค่า OUTPUT พร้อมกับ SELECT ข้อมูล
Stored Procedure Name : getCustomer()
DROP PROCEDURE IF EXISTS getCustomer;
DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2),
OUT pRowFound INT)
BEGIN
# Return SELECT
SELECT * FROM customer WHERE COUNTRY_CODE = sCountryCode;
# Return pRowFound
SET pRowFound = FOUND_ROWS();
END //
DELIMITER ;
Code (PHP)
$serverName = "localhost";
$userName = "root";
$userPassword = "root";
$dbName = "mydatabase";
$mysqli = new mysqli($serverName, $userName, $userPassword,$dbName);
if(!$mysqli) die('Error : ' . $mysqli->connect_error);
$strCountryCode = "US";
$mysqli->query("SET @pCountryCode = '".$strCountryCode."'"); // IN
$mysqli->query("SET @pRowFound = 0"); // OUT
$res = $mysqli->multi_query("CALL getCustomer(@pCountryCode,@pRowFound); SELECT @pRowFound");
if(!$res) die("Error : " . $mysqli->error);
/*
// Get all Result
if( $res ) {
$results = 0;
do {
if ($result = $mysqli->store_result()) {
printf( "<b>Result #%u</b>:<br/>", ++$results );
while( $row = $result->fetch_row() ) {
foreach( $row as $cell ) echo $cell, " ";
}
$result->close();
if( $mysqli->more_results() ) echo "<br/>";
}
} while ($mysqli->more_results() && $mysqli->next_result());
}
**/
// Result 1
$result = $mysqli->store_result();
while( $row = $result->fetch_row() ) {
foreach( $row as $cell ) echo $cell, " ";
}
$result->free();
$mysqli->next_result();
echo "<br><br>";
// Result 2
$mysqli->next_result();
$result = $mysqli->store_result();
while( $row = $result->fetch_row() ) {
foreach( $row as $cell ) echo $cell, " ";
}
$mysqli->close();
การอ่านค่า OUTPUT ที่ถูกส่งกลับ อ่านได้ทั้งค่า Parameters และค่าที่ได้จากการ SELECT
Code (PHP) : แสดงผลบน Table
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "root";
$userPassword = "root";
$dbName = "mydatabase";
$mysqli = new mysqli($serverName, $userName, $userPassword,$dbName);
if(!$mysqli) die('Error : ' . $mysqli->connect_error);
$strCountryCode = "US";
$mysqli->query("SET @pCountryCode = '".$strCountryCode."'"); // IN
$mysqli->query("SET @pRowFound = 0"); // OUT
$res = $mysqli->multi_query("CALL getCustomer(@pCountryCode,@pRowFound); SELECT @pRowFound");
if(!$res) die("Error : " . $mysqli->error);
echo "Result from SELECT";
?>
<table width="600" border="1">
<tr>
<th width="91"> <div align="center">CustomerID </div></th>
<th width="98"> <div align="center">Name </div></th>
<th width="198"> <div align="center">Email </div></th>
<th width="97"> <div align="center">CountryCode </div></th>
<th width="59"> <div align="center">Budget </div></th>
<th width="71"> <div align="center">Used </div></th>
</tr>
<?php
$result = $mysqli->store_result();
while($row = $result->fetch_assoc())
{
?>
<tr>
<td><div align="center"><?php echo $row["CUSTOMER_ID"];?></div></td>
<td><?php echo $row["NAME"];?></td>
<td><?php echo $row["EMAIL"];?></td>
<td><div align="center"><?php echo $row["COUNTRY_CODE"];?></div></td>
<td align="right"><?php echo $row["BUDGET"];?></td>
<td align="right"><?php echo $row["USED"];?></td>
</tr>
<?php
}
$result->free();
$mysqli->next_result();
?>
</table><br />
<?php
echo "Result from @pRowFound : ";
$mysqli->next_result();
$result = $mysqli->store_result();
$row = $result->fetch_assoc();
echo $row["@pRowFound"];
$mysqli->close();
?>
</body>
</html>
แสดงผลบน Table
Example 4 : การใช้ PHP เรียก Stored Procedure และอ่านค่า OUTPUT ที่มีตั้งแต่ 2 ค่าขึ้นไป
Stored Procedure Name : insertCustomer()
DROP PROCEDURE IF EXISTS insertCustomer;
DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
IN pName VARCHAR(150),
IN pEmail VARCHAR(150),
IN pCountryCode VARCHAR(2),
IN pBudget DECIMAL(18,2),
IN pUsed DECIMAL(18,2),
OUT pResult INT,
OUT pMessage VARCHAR(500))
BEGIN
# Declare Variable
DECLARE errCode CHAR(5) DEFAULT '00000';
DECLARE errMsg TEXT;
DECLARE effRows INT;
# Declare Handler Exception
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
errCode = RETURNED_SQLSTATE, errMsg = MESSAGE_TEXT;
END;
# Statement
INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
# Set Result
IF errCode = '00000' THEN
SET pResult = 1;
SET pMessage = 'Insert Data Successfully';
ELSE
SET pResult = 0;
SET pMessage = CONCAT('Error, Code = ',errCode,', Message = ',errMsg);
END IF;
END //
DELIMITER ;
กรณีที่ Insert ข้อมูลสำเร็จ
กรณีที่ Insert ข้อมูลไม่สำเร็จ
Code (PHP)
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "root";
$userPassword = "root";
$dbName = "mydatabase";
$mysqli = new mysqli($serverName, $userName, $userPassword,$dbName);
if(!$mysqli) die('Error : ' . $mysqli->connect_error);
$strCustomerID = "C005";
$strName = "Fun Wipa";
$strEmail = "[email protected]";
$strCountryCode = "TH";
$strBudget = 1000000.00;
$strUsed = 0.00;
$mysqli->query("SET @pCustomerID = '".$strCustomerID."'"); // IN
$mysqli->query("SET @pName = '".$strName."'"); // IN
$mysqli->query("SET @pEmail = '".$strEmail."'"); // IN
$mysqli->query("SET @pCountryCode = '".$strCountryCode."'"); // IN
$mysqli->query("SET @pBudget = '".$strBudget."'"); // IN
$mysqli->query("SET @pUsed = '".$strUsed."'"); // IN
$mysqli->query("SET @pResult = 0"); // OUT
$mysqli->query("SET @pMessage = 0"); // OUT
$res = $mysqli->multi_query("CALL insertCustomer(@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed,@pResult,@pMessage);
SELECT @pResult,@pMessage");
if(!$res) die("Error : " . $mysqli->error);
/*
// Get all Result
if( $res ) {
$results = 0;
do {
if ($result = $mysqli->store_result()) {
printf( "<b>Result #%u</b>:<br/>", ++$results );
while( $row = $result->fetch_row() ) {
foreach( $row as $cell ) echo $cell, " ";
}
$result->close();
if( $mysqli->more_results() ) echo "<br/>";
}
} while ($mysqli->more_results() && $mysqli->next_result());
}
**/
$pResult = "";
$pMessage = "";
if($res) {
$results = 0;
do {
if ($result = $mysqli->store_result()) {
$row = $result->fetch_assoc();
$pResult = $row["@pResult"];
$pMessage = $row["@pMessage"];
$result->close();
if( $mysqli->more_results() ) echo "<br/>";
}
} while ($mysqli->more_results() && $mysqli->next_result());
}
echo "@pResult : ". $row["@pResult"];
echo "<br>";
echo "@pMessage : ". $row["@pMessage"];
$mysqli->close();
?>
</body>
</html>
กรณีที่ Insert ข้อมูลสำเร็จ
กรณีที่ Insert ข้อมูลไม่สำเร็จ
เขียนได้อีกวิธีคือ
Code (PHP)
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL (mysqli)</title>
</head>
<body>
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "root";
$userPassword = "";
$dbName = "mydatabase";
$conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);
$strCustomerID = "C005";
$strName = "Fun Wipa";
$strEmail = "[email protected]";
$strCountryCode = "TH";
$strBudget = 1000000.00;
$strUsed = 0.00;
$stmt = mysqli_prepare($conn, 'CALL insertCustomer(?, ?, ?, ?, ?, ?, @pResult, @pMessage)');
mysqli_stmt_bind_param($stmt, 'ssssii', $strCustomerID, $strName, $strEmail, $strCountryCode, $strBudget, $strUsed); //s - string, b - blob, i - int, etc
mysqli_stmt_execute($stmt);
$query = mysqli_query($conn, 'SELECT @pResult, @pMessage');
$result = mysqli_fetch_assoc($query);
$strResult = $result['@pResult'];
$strMessage = $result['@pMessage'];
echo "@pResult : ". $strResult;
echo "<br>";
echo "@pMessage : ". $strMessage;
$conn->close();
?>
</body>
</html>
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 05
