การเชื่อมต่อฐานข้อมูล MySQL บน PHP

Fatal error: Uncaught Error: Call to undefined function mysql_connect()

PHP7 ได้กล่าวไว้

ถ้าคุณเพิ่งจะหัดเขียน PHP แล้วเจอข้อผิดพลาดข้างบน นั่นเป็นเพราะว่าตั้งแต่ PHP 7.0 เป็นต้นมา (ถ้าไม่มีเหตุจำเป็นจริงๆ ไม่มีใครใช้ PHP5 กันอีกแล้วเนอะ) ส่วนขยาย mysql ได้ถูกนำออกไปจาก PHP โดยสมบูรณ์ ทำให้ฟังก์ชันกลุ่ม mysql ทั้งหมด เช่น mysql_connect(), mysql_pconnect(), mysql_query(), mysql_fetch_assoc() และอีกสารพัด จะไม่สามารถใช้งานได้แล้วนั่นเอง

แต่อย่าเพิ่งตกใจไป PHP ได้เอาส่วนเสริมตัวใหม่สำหรับ MySQL เข้ามาแทน นั่นคือ mysqli หรือ MySQL Improved ที่ compatible กับโค้ดเดิมเกือบจะ 100% เพียงแค่เปลี่ยนชื่อฟังก์ชันจาก mysql_xxx เป็น mysqli_xxx แทน และต้องแนบ link identifier ตามไปด้วยทุกครั้ง (ตัวแปรที่เก็บ mysqli_connect() นั่นแหละ)

นอกจาก mysqli แล้วก็ยังมี PDO หรือ PHP Data Object อีกตัวที่ใช้เชื่อมต่อกับฐานข้อมูล MySQL ได้ แต่ PDO ยังสามารถเชื่อมต่อกับฐานข้อมูลประเภทอื่นๆ ได้อีกทั้งหมดเพียบ (ขึ้นอยู่กับว่ามีไดรเวอร์หรือเปล่า) ซึ่งจริงๆ PDO มันก็มีมาตั้งแต่สมัย PHP 5.1 แล้ว แต่เพิ่งมาได้เฉิดฉายกันจริงๆ ก็ตอน PHP7 ที่คนจำเป็นต้องเลิกใช้ mysql กันอย่างเลี่ยงไม่ได้

ในบทความนี้จะพาไปดูการเชื่อมต่อฐานข้อมูลทั้งสามรูปแบบ นั่นคือ Procedural mysqli, OOP mysqli, และ PDO

• MySQL หมายถึงระบบฐานข้อมูล MySQL หรือในที่นี้จะหมายถึง MariaDB ด้วยก็ได้ เอาที่สะดวก
• mysql หมายถึงส่วนเสริม mysql ของ PHP หรือก็คือฟังก์ชันที่ขึ้นต้นด้วย mysql_ ทั้งหลายแหล่
• mysqli หมายถึงส่วนเสริม mysqli ของ PHP หรือก็คือฟังก์ชันที่ขึ้นต้นด้วย mysqli_ ทั้งหลายแหล่

PDO vs mysqli

เป็นคำถามคลาสสิคว่าในเมื่อมันมีทั้ง PDO และ mysqli แล้วแบบนี้จะเลือกใช้อะไรดี? ว่ากันจริงๆ แล้วทั้งสองอย่างนั้นมีข้อดีข้อเสียที่ต่างกันออกไป เช่น

PDO

• รองรับการเชื่อมต่อฐานข้อมูลหลายตัวมากกว่า
• สามารถตั้งชื่อพารามิเตอร์ใน prepared statement ได้
• รองรับ lazy binding สามารถ bind parameter ได้ด้วยโค้ดที่สั้นกว่า
• สามารถเขียนได้เฉพาะแบบ OOP เท่านั้น

mysqli

• เร็วกว่าอยู่เล็กน้อย ประมาณ 5-6% ในเว็บทั่วไปอาจจะไม่เห็นความต่าง แต่ถ้าเป็นเว็บที่ต้องรีดประสิทธิภาพทุกเม็ดทุกหน่วย อาจจะเหมาะกว่า
• สามารถเขียนได้ทั้งแบบ OOP และ procedural ทำให้การ migrate จาก mysql ทำได้ง่ายกว่า
• ในหลายๆ กรณี โค้ดของ mysqli อ่านเข้าใจง่ายกว่า

การเชื่อมต่อฐานข้อมูล

ฝั่ง mysqli ทั้งแบบ Procedural และ OOP จะระบุแยกระหว่าง $hostname, $username, $password, และ $database ทั้งคู่ ในขณะที่ PDO จะระบุเป็น $dsn, $username, $password, และสามารถมี $options เป็นพารามิเตอร์สุดท้าย เพื่อกำหนดออปชันของ connection นั้นๆ ได้ด้วย

$host = 'vvv.test';
$username = 'external';
$password = 'external';
$database = 'sampledb';

// PDO
$pdo = new PDO( "mysql:host={$host}; dbname={$database}", $username, $password, [ PDO::ATTR_EMULATE_PREPARES => false ] );
// OOP mysqli
$mysqli = new mysqli( $host, $username, $password, $database );
// Procedural mysqli
$mysqli_p = mysqli_connect( $host, $username, $password, $database );

ในสตัวอย่างจะมีการตั้งค่าออปชัน PDO::ATTR_EMULATE_PREPARES เป็น false เพื่อปิดการทำงานของ prepare emulation

การคิวรี่ข้อมูล

จริงๆ Query จะออกเสียงว่า เควอรี่ หรือ เควียรี่ นะ แล้วแต่สำเนียง แต่ผมถนัดออกเสียงว่าคิวรี่ในภาษาไทยมากกว่า

การคิวรี่ข้อมูลนั้นจะมีสองวิธีคือ

1. คิวรีมันไปตรงๆ ผ่านฟังก์ชัน/เมท็อด query
2. ใช้ prepared statement

และจริงๆ ยังมีการคิวรี่แบบ transaction อีกแบบหนึ่งที่เราจะไว้พูดถึงในโพสต์อื่น และในหัวข้อนี้เราจะพูดถึงการคิวรี่ตรงๆ ผ่านฟังก์ชัน/เมท็อด query กันก่อน ดังอย่างโค้ดด้านล่างนี้

$statement = "SELECT * FROM `person`";

// PDO
$pdo->query( $statement );
// OOP mysqli
$mysqli->query( $statement );
// Procedural mysqli
$mysqli_query = mysqli_query( $mysqli_p, $statement );

การสั่งคิวรี่ฐานข้อมูลโดยตรงนั้นมีความเสี่ยงต่อการถูกโจมตีจากข้อมูลที่ทำการ sanitize ได้ไม่ดีพอ (หรือที่เราเรียกกันว่า sql injection) หรือแม้แต่การเขียนคิวรี่ผิดเพราะสับสนการ concat ระหว่างข้อความและตัวแปร ซึ่งเราสามารถแก้ปัญหานี้ได้ด้วยการเปลี่ยนไปใช้ prepared statement แทนการคิวรี่ฐานข้อมูลโดยตรง

จากโค้ดข้างบนจะสังเกตได้ว่าการเขียนแบบ procedural นั้นโค้ดจะเริ่มยาวกว่าอย่างเห็นได้ชัด เพราะต้องแนบ link identifier ไปในฟังก์ชันด้วย (จะคิวรี่ลอยๆ แบบเดิมไม่ได้แล้ว) และยังต้องเอาผลลัพธ์ไปพักไว้ในตัวแปรใหม่ด้วย ในขณะที่แบบ OOP นั้นสั่งคิวรี่ไปได้เลย เพราะทั้ง link identifier และผลลัพธ์นั้นถูกเก็บเอาไว้ในออพเจ็กท์ของมันเองแล้ว

Prepared statement

การคิวรี่ด้วย Prepared statement นั้นจะปลอดภัยกว่าการคิวรี่ตรงๆ อยู่ระดับหนึ่ง หลักการง่ายๆ ของมันคือเราจะสร้าง query template ขึ้นมาก่อน และเว้นว่างส่วนที่จะเป็น value ที่เราต้องการคิวรี่เอาไว้ เช่นปกติเราจะคิวรี่ด้วย WHERE id = 1 เราก็จะเปลี่ยนมาเป็น WHERE id = ? แทน แล้วเราจะแทนที่ ? ด้วยการ bind parameters ซึ่งการ bind parameters นี้จะทำการ escape string ให้อัตโนมัติ ทำให้เราไม่จำเป็นต้องมานั่งครอบ mysqli_real_escape_string( $value ) ซ้ำอีกรอบอีก

แบบ PDO

// PDO
$pdo_statement = $pdo->prepare( 'SELECT * FROM `person` WHERE `country` = :country AND `state` = :state AND `city` = :city' );
$pdo_statement->execute([
  ':country' => 'United States',
  ':state' => 'Washington',
  ':city' => 'Seattle'
]);
$result = $pdo_statement->fetch();

while ( $result ) {
  var_dump( $result );
}

แบบ OOP mysqli

// OOP mysqli
$mysqli_statement = $mysqli->prepare( 'SELECT * FROM `person` WHERE `country` = ? AND `state` = ? AND `city` = ?' );
$mysqli_statement->bind_param( 'sss', $country, $state, $city );

$country = 'United States';
$state = 'Washington';
$city = 'Seattle';
$mysqli_statement->execute();
$result = $mysqli_statement->get_result();

while ( $row = $result->fetch_array() ) {
  var_dump( $row );
}

แบบ Procedural mysqli

// Procedural mysqli
$procedural_statement = mysqli_prepare( $mysqli_p, 'SELECT * FROM `person` WHERE `country` = ? AND `state` = ? AND `city` = ?' );
mysqli_stmt_bind_param($procedural_statement, 'sss', $country, $state, $city);

$country = 'United States';
$state = 'Washington';
$city = 'Seattle';
$result = mysqli_stmt_get_result( $procedural_statement );

while ( $row = mysqli_fetch_array( $result ) ) {
  var_dump( $row );
}

นอกจากนี้ การที่ prepared statement นั้นจะทำการส่งตัว prepared statement และพารามิเตอร์ต่างๆ แยกกัน ทำให้เรายังสามารถทำการ prepare statement แค่รอบเดียว จากนั้นเปลี่ยนค่าพารามิเตอร์ต่างๆ ใหม่ แล้วสั่งคิวรี่อีกรอบได้ทันทีโดยไม่จำเป็นต้องเขียน sql statement ใหม่ทั้งหมด

การ bind parameters

ในตัวอย่างของการใช้ prepared statement จะเห็นว่ามีการ bind parameters เข้าไปด้วย (เพื่อแทนค่า :param ใน PDO หรือ ? ใน mysqli) ซึ่งระหว่าง mysqli และ PDO นั้นจะมีวิธีการ bind ต่างกันอยู่เล็กน้อย

การ bind parameters บน mysqli

ใน mysqli จะมีเมท็อด mysqli_stmt::bind_param() (หรือฟังก์ชัน mysqli_stmt_bind_param() อีกตัวหนึ่ง ที่รับพารามิเตอร์เหมือนกัน ต่างกันที่ต้องส่ง identifier เข้ามาด้วย) เมท็อดนี้จะรับค่าเข้าไป 1+n ตัวคือ

1. data type เป็นสตริงบอก data type ของ parameter แต่ละตัวใน prepared statement (เครื่องหมาย ? นั่นแหละ) โดยเรียงลำดับตามลำดับใน statement
2. variables เป็นตัวแปรที่จะใช้เก็บข้อมูลที่เราจะเอามา bind ใส่ไปตามจำนวนพารามิเตอร์ใน prepared statement

Data type ของ mysqli มีอยู่ 4 ตัวคือ

ตัวอย่างจากข้างบนคือเรามี Prepared statement ดังนี้

$mysqli_statement = $mysqli->prepare( 'SELECT * FROM `person` WHERE `country` = ? AND `state` = ? AND `city` = ?' );

เท่ากับว่าจะมีพารามิเตอร์ 3 ตัว คือ ? ของ country, state, และ city ตามลำดับ ทั้งสามค่าจะรับค่า string ธรรมดาเข้ามา ดังนั้นค่าของ data type ก็จะเป็น s สามตัวเรียงกัน คือ sss

ตัวแปรที่จะใช้ในการ bind นั้น เราจะใช้ค่าจาก $country สำหรับ country, $state สำหรับ state, และ $city สำหรับ city ดังนั้นเราจะเขียนคำสั่ง bind_param() ได้ดังนี้

// OOP mysqli
$mysqli_statement->bind_param( 'sss', $country, $state, $city );

// Procedural mysqli
mysqli_stmt_bind_param($procedural_statement, 'sss', $country, $state, $city);

หลังจากเรากำหนดค่าการ bind parameter เรียบร้อยแล้ว ก็จัดการใส่ค่าให้ตัวแปรและสั่ง execute() และ get_result() ออกมา

// OOP mysqli
$country = 'Thailand';
$state = 'Bangkok';
$city = 'Sathorn';
$mysqli_statement->execute();
$result = $mysqli_statement->get_result();

// Procedural mysqli
$country = 'Thailand';
$state = 'Bangkok';
$city = 'Sathorn';
mysqli_stmt_execute( $procedural_statement );
$result = mysqli_stmt_get_result( $procedural_statement );

การ bind parameters บน PDO

ในเรื่องของการ bind parameter นั้น PDO จะมีวิธีที่สะดวกกว่า mysqli อยู่เล็กน้อย อย่างแรกคือเราสามารถส่งค่า bind เข้าไปยังเมท็อด PDOStatement::execute() ได้เลย (เรียกว่า lazy binding) และอีกข้อคือเราสามารถ bind parameters แบบตั้งชื่อให้พารามิเตอร์แต่ละตัวได้ด้วย (เรียกว่า named parameter) โดย named parameter จะขึ้นต้นด้วยโคลอนและตามด้วชื่อพารามิเตอร์ เช่น :country

ข้อควรระวังอย่างหนึ่งคือ PDO จะเปิด emulation mode เป็นค่าเริ่มต้น ซึ่งส่งผลให้ค่าทั้งหมดที่ส่งเข้าไปผ่าน lazy binding จะถูกมองว่าเป็น string ทั้งหมด (ปัญหาหลักที่เจอคือจะทำให้คำสั่ง LIMIT ใน sql statement นั้นพัง) การปิด emulation mode จะเป็นการโยนค่าตัวแปรไปให้ MySQL จัดการกับประเภทข้อมูลเอง และเปิดให้สามารถทำ prepared statement รอบเดียว แล้ว execute หลายๆ รอบโดยเปลี่ยนค่าพารามิเตอร์ได้ด้วย

การส่งค่าเข้าไปใน PDOStatement::execute() โดยตรงนั้น ถ้าเราไม่ได้กำหนดชื่อพารามิเตอร์ เราสามารถส่งอาเรย์ชุดข้อมูลที่ต้องการเข้าไปได้เลย หรือถ้าเรากำหนดชื่อพารามิเตอร์เอาไว้ ก็ส่งเข้าไปเป็น associated array (เป็น $key => $value) เช่น

// Unnamed parameters
$pdo_statement = $pdo->prepare( 'SELECT * FROM `person` WHERE `country` = ? AND `state` = ? AND `city` = ?' );
$pdo_statement->execute(['Thailand', 'Bangkok', 'Sathorn']);
$result = $pdo_statement->fetch();

// Named parameters
$pdo_statement_named = $pdo->prepare( 'SELECT * FROM `person` WHERE `country` = :country AND `state` = :state AND `city` = :city' );
$pdo_statement_named->execute([
  ':country' => 'Thailand',
  ':state' => 'Bangkok',
  ':city' => 'Sathorn'
]);
$result = $pdo_statement_named->fetch();

นับ num_rows

ฟีเจอร์หนึ่งที่เรามักจะใช้ร่วมกับการ SELECT นั่นคือการนับว่าคิวรี่ข้อมูลออกมาได้กี่แถว ใน mysqli และ PDO สามารถทำได้ดังนี้

// PDO
$result = $pdo_statement->fetchAll();
$num_rows = count( $result );

// OOP mysqli
$result = $mysqli_statement->get_result();
$num_rows = $result->num_rows;

// Procedural mysqli
$result = mysqli_stmt_get_result( $procedural_statement );
$num_rows = mysqli_num_rows( $result );

ใน PDO นั้นจะไม่มีพร็อพเพอร์ตี้หรือเมท็อดสำหรับเก็บจำนวนแถวให้ในตัว แต่เราสามารถใช้เมท็อด PDOStatement::fetchAll() เพื่อดึงข้อมูลทั้งหมดที่คิวรี่ได้ออกมา จากนั้นใช้ count() เพื่อนับอีกทีก็จะได้จำนวนแถวทั้งหมดออกมา และหลังจากนั้นเราก็สามารถเอา $result ไป foreach เพื่อแสดงผลได้เลย

affected rows และ insert id

การคิวรี่แบบ SELECT นั้นไม่ใช่เรื่องน่าปวดหัวอะไรมากนัก เพราะเราสามารถตรวจสอบผลลัพธิ์ได้ง่ายๆ ด้วยการลูปคำสั่ง PDOStatement::fetch() หรือ mysqli_stmt::get_result() ออกมาเรื่อยๆ จนกว่าค่าจะกลายเป็น false ก็จะถือว่าหมด result ที่เจอ

แต่ในกรณีคำสั่งอื่นเช่น INSERT, UPDATE, หรือ DELETE นั้นเรามักจะมีการใช้ค่า affected rows หรือจำนวนแถวที่คิวรี่นั้นๆ มีผลด้วย และในคำสั่ง INSERT ก็จะมีการใช้ insert id เพื่อดึงค่า primary key ของข้อมูลล่าสุดที่เพิ่ง insert ลงฐานข้อมูลกลับออกมาใช้งาน

ทางฝั่ง mysqli จะเตรียมพร็อพเพอร์ตี้ mysqli_stmt::$affected_rows และ mysqli_stmt::$insert_id เอาไว้ให้แล้ว สามารถเรียกใช้ได้ทันที ในขณะที่ฝั่ง PDO นั้นจะงงกว่าอยู่เล็กน้อย เพราะแม้ว่า PDO จะเตรียมเมท็อดสำหรับทั้งสองค่าเอาไว้ให้ แต่ทั้งสองเมท็อดจะอยู่คนละออพเจ็กท์กัน โดยเราจะใช้ PDO::lastInsertId() สำหรับดึง id ข้อมูลล่าสุดที่เพิ่ง insert ลงฐานข้อมูล และจะใช้ PDOStatement::rowCount() สำหรับนับจำนวน affected rows

แบบ PDO

// Last insert ID
$pdo_insert = $pdo->prepare( 'INSERT INTO `person` (`firtname`, `lastname`) VALUES ( ?, ? )' );
$pdo_insert->execute(['John', 'Doe']);

$insert_id = $pdo->lastInsertId();

// Affected rows
$pdo_delete = $pdo->prepare( 'DELETE FROM `person` WHERE `state` = ?');
$pdo_delete->execute(['Bangkok']);

$affected_rows = $pdo_delete->rowCount();

สังเกตว่าเราเรียก lastInsertId() จาก $pdo ที่เก็บออพเจ็กท์ PDO เอาไว้ และเรียก rowCount() จาก $pdo_delete ที่เก็บออพเจ็กท์ PDOStatement ที่ได้มาจาก $pdo->prepare() เอาไว้

แบบ OOP mysqli

// Last insert ID
$mysqli_statement = $mysqli->prepare( 'INSERT INTO `person` (`firtname`, `lastname`) VALUES ( ?, ? )' );
$mysqli_statement->bind_param( 'ss', $firstname, $lastname );
$firstname = 'John';
$lastname = 'Doe';
$mysqli_statement->execute();

$insert_id = $mysqli_statement->insert_id;

// Afftected rows
$mysqli_statement = $mysqli->prepare( 'DELETE FROM `person` WHERE `state` = ?' );
$mysqli_statement->bind_param( 's', $state );
$state = 'Bangkok';
$mysqli_statement->execute();

$affected_rows = $mysqli_statement->affected_rows;

สังเกตว่าใน mysqli เราจะเรียกทั้ง affected rows และ last insert id ออกมาจากตัวแปรเดียวกันคือ $mysqli_statement ที่เป็นออพเจ็กท์ mysqli_stmt

แบบ Procedural mysqli

// Last insert ID
$mysqli_statement = mysqli_prepare( $mysqli_p, 'INSERT INTO `person` (`firtname`, `lastname`) VALUES ( ?, ? )' );
mysqli_stmt_bind_param( $mysqli_statement, 'ss', $firstname, $lastname );
$firstname = 'John';
$lastname = 'Doe';
mysqli_stmt_execute( $mysqli_statement );

$insert_id = mysqli_stmt_insert_id( $mysqli_statement );

// Afftected rows
$mysqli_statement = mysqli_prepare( $mysqli_p, 'DELETE FROM `person` WHERE `state` = ?' );
mysqli_stmt_bind_param( $mysqli_statement, 's', $state );
$state = 'Bangkok';
mysqli_stmt_execute( $mysqli_statement );

$affected_rows = mysqli_stmt_affected_rows( $mysqli_statement );

เช่นเดียวกับการเขียนแบบ OOP ที่เราจะใช้ $mysqli_statement เป็น link identifier

ตอนไหนควรใช้ query ตอนไหนควรใช้ prepare

ตอบแบบง่ายๆ เลยก็คือเราจะใช้ query โดยตรงก็ต่อเมื่อเป็นคิวรี่ที่เราเขียน statement เองทั้งหมดโดยไม่มีการรับอินพุทใดๆ เข้ามาทั้งสิ้น ส่วนถ้ามีการรับอินพุทใดๆ เข้ามาด้วยนั้น แนะนำอย่างยิ่งว่าควรใช้ Prepared statement จะดีกว่าเพื่อความปลอดภัย

จริงๆ แล้วในการสั่งคิวรี่ตรงๆ โดยไม่ทำการ prepare นั้น เรายังมีฟังก์ชัน mysqli_real_escape_string() หรือเมท็อด mysqli::real_escape_string() ให้ใช้งานอยู่ด้วย แต่โดยส่วนตัวแล้วแนะนำว่าการทำ prepared statement นั้นสะดวกกว่ามาก