การใช้ PDO กับ Stored Procedure

ในบทนี้ เราจะมาเรียนรู้วิธีการใช้งาน Stored Procedure ร่วมกับ PDO ใน PHP Stored Procedure คือชุดคำสั่ง SQL ที่ถูกจัดเก็บไว้ในฐานข้อมูล ซึ่งสามารถเรียกใช้งานได้จากโปรแกรมต่างๆ รวมถึง PHP การใช้ Stored Procedure ช่วยให้เราสามารถลดปริมาณโค้ด SQL ที่ต้องเขียนใน PHP และเพิ่มความปลอดภัยของฐานข้อมูลได้

Stored Procedure คืออะไร?

Stored Procedure คือกลุ่มของคำสั่ง SQL ที่ถูกคอมไพล์และจัดเก็บไว้ในฐานข้อมูล เปรียบเสมือนฟังก์ชันในภาษาโปรแกรม เมื่อเราต้องการใช้งาน Stored Procedure เราเพียงแค่เรียกชื่อ Stored Procedure พร้อมกับส่ง Parameter ที่จำเป็น Stored Procedure จะทำการประมวลผลและส่งผลลัพธ์กลับมา

ข้อดีของการใช้ Stored Procedure

ประสิทธิภาพ: Stored Procedure ถูกคอมไพล์และจัดเก็บไว้ในฐานข้อมูล ทำให้การเรียกใช้งานเร็วกว่าการส่งคำสั่ง SQL แต่ละครั้ง
ความปลอดภัย: Stored Procedure สามารถควบคุมสิทธิ์การเข้าถึงข้อมูลได้ ทำให้ลดความเสี่ยงจากการถูกโจมตีด้วย SQL Injection
การบำรุงรักษา: การแก้ไขคำสั่ง SQL ใน Stored Procedure ทำได้ง่ายกว่าการแก้ไขโค้ด PHP จำนวนมาก
ลดปริมาณ Network Traffic: เนื่องจากคำสั่ง SQL ถูกประมวลผลบน Server ของ Database ทำให้ลดปริมาณข้อมูลที่ต้องส่งผ่าน Network

ขั้นตอนการใช้งาน Stored Procedure กับ PDO

สร้าง Stored Procedure ในฐานข้อมูล
เชื่อมต่อฐานข้อมูลด้วย PDO
เตรียม Statement สำหรับเรียก Stored Procedure
ผูก Parameter (ถ้ามี)
Execute Statement
ดึงผลลัพธ์ (ถ้ามี)

1. การสร้าง Stored Procedure

ก่อนอื่น เราต้องสร้าง Stored Procedure ในฐานข้อมูลก่อน ตัวอย่างนี้เราจะสร้าง Stored Procedure สำหรับดึงข้อมูลผู้ใช้ตาม ID โดยใช้ MySQL:

DELIMITER //

CREATE PROCEDURE GetUserById (
    IN userId INT
)
BEGIN
    SELECT id, username, email
    FROM users
    WHERE id = userId;
END //

DELIMITER ;

โค้ดนี้สร้าง Stored Procedure ชื่อ `GetUserById` ที่รับ Parameter ชื่อ `userId` เป็น Integer และทำการ SELECT ข้อมูล `id`, `username`, `email` จากตาราง `users` โดยมีเงื่อนไข `id = userId`

คำอธิบาย:

`DELIMITER //` และ `DELIMITER ;` คือการกำหนด Delimiter ใหม่ เพื่อให้ MySQL สามารถแยกแยะคำสั่ง CREATE PROCEDURE ได้อย่างถูกต้อง
`CREATE PROCEDURE GetUserById (IN userId INT)` คือการสร้าง Stored Procedure ชื่อ `GetUserById` ที่รับ Parameter ชื่อ `userId` เป็น Integer โดย `IN` หมายถึง Input Parameter
`BEGIN` และ `END` คือจุดเริ่มต้นและสิ้นสุดของ Stored Procedure
`SELECT id, username, email FROM users WHERE id = userId;` คือคำสั่ง SQL ที่ทำการดึงข้อมูล

2. การเชื่อมต่อฐานข้อมูลด้วย PDO

ขั้นตอนต่อไปคือการเชื่อมต่อฐานข้อมูลด้วย PDO เหมือนกับที่เราทำในบทก่อนๆ:

<?php
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

อย่าลืมเปลี่ยนค่า `$host`, `$dbname`, `$username`, และ `$password` ให้ถูกต้องตามการตั้งค่าฐานข้อมูลของคุณ

3. การเตรียม Statement สำหรับเรียก Stored Procedure

ในการเรียก Stored Procedure เราจะใช้คำสั่ง `CALL` ใน SQL:

<?php
try {
    // ... (โค้ดเชื่อมต่อฐานข้อมูลจากด้านบน) ...

    $stmt = $pdo->prepare("CALL GetUserById(:userId)");
} catch (PDOException $e) {
    echo "Error preparing statement: " . $e->getMessage();
}
?>

โค้ดนี้เตรียม Statement สำหรับเรียก Stored Procedure `GetUserById` โดยใช้ Named Parameter `:userId`

4. การผูก Parameter

เราต้องผูกค่าให้กับ Parameter `:userId` ก่อนที่จะ Execute Statement:

<?php
try {
    // ... (โค้ดเชื่อมต่อฐานข้อมูลและเตรียม Statement จากด้านบน) ...

    $userId = 1; // ตัวอย่าง ID ผู้ใช้
    $stmt->bindParam(':userId', $userId, PDO::PARAM_INT);
} catch (PDOException $e) {
    echo "Error binding parameter: " . $e->getMessage();
}
?>

โค้ดนี้ผูกค่า `$userId` ให้กับ Parameter `:userId` โดยกำหนด Type เป็น `PDO::PARAM_INT` (Integer)

5. Execute Statement

เมื่อผูก Parameter เรียบร้อยแล้ว เราก็สามารถ Execute Statement ได้:

<?php
try {
    // ... (โค้ดเชื่อมต่อฐานข้อมูล, เตรียม Statement, และผูก Parameter จากด้านบน) ...

    $stmt->execute();
} catch (PDOException $e) {
    echo "Error executing statement: " . $e->getMessage();
}
?>

6. ดึงผลลัพธ์

หลังจาก Execute Statement แล้ว เราสามารถดึงผลลัพธ์ได้โดยใช้ `fetch()` หรือ `fetchAll()`:

<?php
try {
    // ... (โค้ดเชื่อมต่อฐานข้อมูล, เตรียม Statement, ผูก Parameter, และ Execute Statement จากด้านบน) ...

    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        echo "User ID: " . $result['id'] . "<br>";
        echo "Username: " . $result['username'] . "<br>";
        echo "Email: " . $result['email'] . "<br>";
    } else {
        echo "User not found.";
    }
} catch (PDOException $e) {
    echo "Error fetching data: " . $e->getMessage();
}
?>

โค้ดนี้ดึงข้อมูลแถวแรกจากผลลัพธ์ และแสดงข้อมูล `id`, `username`, และ `email` ถ้าไม่พบข้อมูล จะแสดงข้อความ "User not found."

ตัวอย่างการใช้งาน Stored Procedure ที่มี Output Parameter

Stored Procedure สามารถมี Output Parameter ได้ ซึ่งเราสามารถใช้เพื่อส่งค่ากลับจาก Stored Procedure ตัวอย่างเช่น เราจะสร้าง Stored Procedure ที่นับจำนวนผู้ใช้ทั้งหมดในตาราง:

DELIMITER //

CREATE PROCEDURE GetTotalUsers (
    OUT total INT
)
BEGIN
    SELECT COUNT(*) INTO total
    FROM users;
END //

DELIMITER ;

โค้ดนี้สร้าง Stored Procedure ชื่อ `GetTotalUsers` ที่มี Output Parameter ชื่อ `total` ซึ่งจะเก็บจำนวนผู้ใช้ทั้งหมด

ในการเรียก Stored Procedure ที่มี Output Parameter เราต้องใช้ Variable ใน PHP เพื่อเก็บค่า:

<?php
try {
    // ... (โค้ดเชื่อมต่อฐานข้อมูลจากด้านบน) ...

    $stmt = $pdo->prepare("CALL GetTotalUsers(@total)");
    $stmt->execute();

    $stmt = $pdo->query("SELECT @total");
    $totalUsers = $stmt->fetch(PDO::FETCH_COLUMN);

    echo "Total users: " . $totalUsers;
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

คำอธิบาย:

เราใช้ `@total` เป็น Variable ใน MySQL เพื่อรับค่าจาก Output Parameter
หลังจาก Execute Statement แล้ว เราใช้ `SELECT @total` เพื่อดึงค่าจาก Variable
`$stmt->fetch(PDO::FETCH_COLUMN)` จะดึงค่าจาก Column แรกของผลลัพธ์

ข้อผิดพลาดที่พบบ่อย

Syntax Error ใน Stored Procedure: ตรวจสอบ Syntax ของ Stored Procedure ให้ถูกต้อง
Parameter Type Mismatch: ตรวจสอบให้แน่ใจว่า Type ของ Parameter ที่ส่งจาก PHP ตรงกับ Type ที่กำหนดใน Stored Procedure
Permissions: ตรวจสอบว่า User ที่ใช้เชื่อมต่อฐานข้อมูลมีสิทธิ์ในการ Execute Stored Procedure
Incorrect Number of Parameters: ตรวจสอบว่าจำนวน Parameter ที่ส่งจาก PHP ตรงกับจำนวน Parameter ที่กำหนดใน Stored Procedure

สรุป

ในบทนี้ เราได้เรียนรู้วิธีการใช้งาน Stored Procedure ร่วมกับ PDO ใน PHP การใช้ Stored Procedure ช่วยให้เราสามารถลดปริมาณโค้ด SQL ที่ต้องเขียนใน PHP และเพิ่มความปลอดภัยของฐานข้อมูลได้ อย่าลืมทดลองเขียน Stored Procedure และเรียกใช้งานจาก PHP เพื่อให้เข้าใจการทำงานอย่างแท้จริง ในบทต่อไป เราจะมาเรียนรู้เกี่ยวกับการใช้ PDO กับ Auto-Increment