การตรวจสอบห้องว่างด้วย MySQL

| อัพเดทล่าสุด:

ในบทความนี้จะเป็นการใช้คำสั่ง MySQL (หรือผู้ใช้ MariaDB ก็ใช้ได้) เพื่อตรวจหาห้องพักที่ว่างหรือยังไม่ถูกจอง. เงื่อนไขพิเศษที่แตกต่างจากที่พบทั่วไปก็คือ ผู้เข้าพักสามารถกำหนดได้ว่าจะระบุวันที่เช็กเอาต์หรือไม่ก็ได้, ถ้าหากไม่ระบุนั่นหมายความว่าจะยังคงเข้าพักอยู่ต่อไปเรื่อยๆ. วิธีการแบบนี้สามารถประยุกต์ใช้กับระบบการเช่าห้องพักระยะยาวได้ เช่น เช่าบ้าน, เช่าคอนโดหรือหอพักทั่วไป ที่ผู้เข้าพักจะต่อสัญญาไปเรื่อยๆ ผู้ดูแลก็ยังไม่ต้องระบุวันเช็กเอาต์ก็ได้.

คำสั่ง SQL จะแบ่งเงื่อนไขผ่านตัวภาษาโปรแกรมก่อน ว่าผู้เข้าพักจะระบุวันที่เช็กเอาต์หรือไม่. ในกรณีตัวอย่างนี้จะใช้ PHP ดังนั้นโค้ดโดยย่อเฉพาะใจความสำคัญจะเป็นดังนี้.

if (empty($checkout)) {
    // if not enter check out date.
    $sql .= '(
        (
            `checkin` >= :checkin 
            AND (`checkout` >= :checkin OR `checkout` IS NULL)
        )
        OR
        (
            `checkin` <= :checkin
            AND (`checkout` >= :checkin OR `checkout` IS NULL)
        )
    )';
} else {
    // if enter check out date.
    $sql .= '(
        (
            `checkout` IS NULL
            AND 
            (
                (
                    `checkin` >= :checkin 
                    AND `checkin` <= :checkout
                )
                OR
                (
                    `checkin` <= :checkin
                )
            )
        )
        OR
        (
            (
                `checkin` >= :checkin
                AND `checkin` <= :checkout
            )
            OR
            (
                `checkin` <= :checkin
                AND `checkout` >= :checkin
            )
        )
    )';
}

โค้ดเต็ม

ในโค้ดเต็มๆต่อไปนี้จะเขียนในภาษา PHP โดยจะใช้การเชื่อมต่อฐานข้อมูลผ่าน PDO ทั้งนี้หากผู้อ่านจะใช้การเชื่อมต่ออื่น เช่น mysqli ก็จะต้องหาวิธีแปลงด้วยตัวเอง.

/**
 * Check availability.
 *
 * @param string $room Room number.
 * @param string $checkin Check in date.
 * @param string $checkout Check out date. It can be null or empty if there is no plan to check out yet.
 * @return bool Return `true` if selected room and dates is available. Return `false` if unavailable or occupied.
 */
function checkAvailability($room, $checkin, $checkout = null)
{
    global $dbh;

    $sql = 'SELECT * FROM `room_availability` WHERE `room` = :room';
    $sql .= ' AND ';
    if (empty($checkout)) {
        // if not enter check out date.
        $sql .= '(
            (
                `checkin` >= :checkin 
                AND (`checkout` >= :checkin OR `checkout` IS NULL)
            )
            OR
            (
                `checkin` <= :checkin
                AND (`checkout` >= :checkin OR `checkout` IS NULL)
            )
        )';
    } else {
        // if enter check out date.
        $sql .= '(
            (
                `checkout` IS NULL
                AND 
                (
                    (
                        `checkin` >= :checkin 
                        AND `checkin` <= :checkout
                    )
                    OR
                    (
                        `checkin` <= :checkin
                    )
                )
            )
            OR
            (
                (
                    `checkin` >= :checkin
                    AND `checkin` <= :checkout
                )
                OR
                (
                    `checkin` <= :checkin
                    AND `checkout` >= :checkin
                )
            )
        )';
    }
    $sth = $dbh->prepare($sql);
    $sth->bindValue(':room', $room);
    $sth->bindValue(':checkin', $checkin);
    if (!empty($checkout)) {
        $sth->bindValue(':checkout', $checkout);
    }
    $sth->execute();
    $result = $sth->fetchAll();
    $sth->closeCursor();
    unset($sql);
    if ((is_object($result) || is_array($result)) && !empty($result)) {
        // if found occupied, unavailable.
        // mark availability as false.
        return false;
    } else {
        return true;
    }
}// endfunciton; checkAvailability

ฟังก์ชั่นดังกล่าวจะเป็นการตรวจหาห้อง และ วันที่เช็กอิน และอาจจะรวมถึงวันที่เช็กเอาต์ด้วย แล้วตรวจหาว่ามีห้องว่างในวันดังกล่าวหรือไม่ โดยใช้วิธีการตรวจหาให้พบ, ถ้าหากพบแสดงว่าห้องนั้นไม่ว่าง และจะคืนค่า false แต่ถ้าหากไม่พบแสดงว่าห้องนั้นว่างอยู่ และจะคืนค่า true กลับไป.

ในการเก็บข้อมูลแบบนี้ ถ้าหากผู้เข้าพักไม่ระบุวันที่เช็กเอาต์ การเก็บข้อมูลวันที่เช็กเอาต์จะต้องเป็น null เท่านั้น! ไม่สามารถเก็บเป็นสตริงว่างเปล่าได้ เพราะในเงื่อนไขการตรวจสอบจะใช้ IS NULL ร่วมด้วย.

การทดสอบ.

ใช้โค้ดชุดต่อไปนี้ในการทดสอบ.

config.php

<?php

$dsn = 'mysql:host=127.0.0.1;dbname=myDBName;charset=utf8mb4';
$username = '';
$password = '';
$pdoOptions = [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ,
    \PDO::ATTR_STRINGIFY_FETCHES => true,
];

create-table.php

<?php

require 'config.php';


$dbh = new \PDO($dsn, $username, $password, $pdoOptions);


$sql = 'CREATE TABLE IF NOT EXISTS `room_availability` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `room` varchar(5) DEFAULT NULL COMMENT \'room number\',
    `checkin` date DEFAULT NULL COMMENT \'check in date\',
    `checkout` date DEFAULT NULL COMMENT \'check out date\',
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1';
$result = $dbh->exec($sql);
echo '<pre>' . $sql . '</pre>';
var_export($result);

$sql = 'SELECT `id` FROM `room_availability`';
$sth = $dbh->prepare($sql);
unset($sql);
$sth->execute();
$result = $sth->fetchAll();
if (empty($result)) {
    $sqlArray = [
        'INSERT INTO `room_availability` (`room`, `checkin`, `checkout`) VALUES (\'101\', \'2023-02-01\', \'2023-02-28\');',
        'INSERT INTO `room_availability` (`room`, `checkin`, `checkout`) VALUES (\'101\', \'2023-03-01\', \'2023-04-30\');',
        'INSERT INTO `room_availability` (`room`, `checkin`, `checkout`) VALUES (\'101\', \'2023-06-01\', null);',// no plan to check out.
        'INSERT INTO `room_availability` (`room`, `checkin`, `checkout`) VALUES (\'102\', \'2023-02-01\', \'2023-04-30\');',
        'INSERT INTO `room_availability` (`room`, `checkin`, `checkout`) VALUES (\'102\', \'2023-05-01\', \'2023-07-31\');',
        'INSERT INTO `room_availability` (`room`, `checkin`, `checkout`) VALUES (\'102\', \'2023-09-01\', \'2023-11-30\');',
    ];
    foreach ($sqlArray as $sql) {
        $result = $dbh->exec($sql);
        echo '<pre>' . $sql . '</pre>';
        var_export($result);
    }
    unset($sql, $sqlArray);
}


unset($dbh, $result, $sth);

list-all.php

<?php

require 'config.php';


$dbh = new \PDO($dsn, $username, $password, $pdoOptions);


$sql = 'SELECT * FROM `room_availability`';
$sth = $dbh->prepare($sql);
unset($sql);
$sth->execute();
$result = $sth->fetchAll();

echo '<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@materializecss/materialize@2.0.0-alpha/dist/css/materialize.min.css">' . PHP_EOL;

if (is_iterable($result)) {
    echo '<table>' . PHP_EOL;
    echo '<thead><tr><th>ID</th><th>room</th><th>check in</th><th>check out</th></tr></thead>' . PHP_EOL;
    echo '<tbody>' . PHP_EOL;
    foreach ($result as $row) {
        echo '<tr>' . PHP_EOL;
        echo '<td>' . $row->id . '</td>';
        echo '<td>' . (!is_null($row->room) ? $row->room : '<em>NULL</em>') . '</td>';
        echo '<td>' . (!is_null($row->checkin) ? $row->checkin : '<em>NULL</em>') . '</td>';
        echo '<td>' . (!is_null($row->checkout) ? $row->checkout : '<em>NULL</em>') . '</td>';
        echo '</tr>' . PHP_EOL;
    }
    unset($row);
    echo '</tbody>' . PHP_EOL;
    echo '</table>' . PHP_EOL;
}
unset($result);
$sth->closeCursor();


unset($dbh, $sth);

tests.php

<?php

require 'config.php';


$dbh = new \PDO($dsn, $username, $password, $pdoOptions);


ini_set('zend.assertions', 1);
ini_set('assert.exception', 0);
assert_options(ASSERT_BAIL, false);
// test on room 101
$room = '101';
$testsArray = [
    // tests with check out date specified. -------------------------
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-01-31',
        'expect' => true,// true means available, fase means unavailable.
    ],
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-02-28',
        'expect' => false,
    ],// check out stuck
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-05-05',
        'expect' => false,
    ],
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-12-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-02-01',
        'checkout' => '2023-05-31',
        'expect' => false,
    ],// check in stuck
    [
        'checkin' => '2023-03-01',
        'checkout' => '2023-05-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-04-01',
        'checkout' => '2023-05-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-05-01',
        'checkout' => '2023-05-31',
        'expect' => true,
    ],
    [
        'checkin' => '2023-05-01',
        'checkout' => '2023-06-01',
        'expect' => false,
    ],// check out stuck with 'no plan to check out' on db.
    [
        'checkin' => '2023-05-01',
        'checkout' => '2023-08-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-06-01',
        'checkout' => '2023-10-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-07-01',
        'checkout' => '2023-10-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-12-01',
        'checkout' => '2023-12-31',
        'expect' => false,
    ],
    [
        'checkin' => '2024-01-01',
        'checkout' => '2024-12-31',
        'expect' => false,
    ],
    // test with no plan to check out from user input. --------------
    [
        'checkin' => '2023-01-01',
        'expect' => false,
    ],// stuck with 'no plan to check out' on db.
    [
        'checkin' => '2023-05-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-08-01',
        'expect' => false,
    ],
];
runTests($room, $testsArray);

// test on room 102
$room = '102';
$testsArray = [
    // tests with check out date specified. -------------------------
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-01-31',
        'expect' => true,
    ],
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-02-28',
        'expect' => false,
    ],// check out stuck
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-08-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-02-01',
        'checkout' => '2023-03-31',
        'expect' => false,
    ],// check in stuck
    [
        'checkin' => '2023-03-01',
        'checkout' => '2023-08-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-04-01',
        'checkout' => '2023-08-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-05-01',
        'checkout' => '2023-08-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-06-01',
        'checkout' => '2023-08-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-07-01',
        'checkout' => '2023-08-31',
        'expect' => false,
    ],
    [
        'checkin' => '2023-08-01',
        'checkout' => '2023-08-31',
        'expect' => true,
    ],
    [
        'checkin' => '2023-08-01',
        'checkout' => '2023-12-31',
        'expect' => false,
    ],// check out stuck
    [
        'checkin' => '2023-11-01',
        'checkout' => '2023-12-31',
        'expect' => false,
    ],// check in stuck
    [
        'checkin' => '2023-12-01',
        'checkout' => '2023-12-31',
        'expect' => true,
    ],
    // test with no plan to check out from user input. --------------
    [
        'checkin' => '2023-01-01',
        'expect' => false,
    ],// check in stuck
    [
        'checkin' => '2023-02-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-03-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-04-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-05-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-06-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-07-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-08-01',
        'expect' => false,
    ],// user input 'no plan to check out' stuck with checked in on id 6
    [
        'checkin' => '2023-09-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-10-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-11-01',
        'expect' => false,
    ],
    [
        'checkin' => '2023-12-01',
        'expect' => true,
    ],
    [
        'checkin' => '2024-01-01',
        'expect' => true,
    ],
];
runTests($room, $testsArray);

// test on room 103 (never checked in)
$room = '103';
$testsArray = [
    [
        'checkin' => '2023-01-01',
        'checkout' => '2023-12-31',
        'expect' => true,
    ],
    [
        'checkin' => '2023-01-01',
        'expect' => true,
    ],
];
runTests($room, $testsArray);


/**
 * Run tests with selected room and check in/check out date in the `$testsArray` data.
 *
 * @param string $room Room number.
 * @param array $testsArray Tests array data.
 * @return void
 */
function runTests($room, $testsArray)
{
    foreach ($testsArray as $testItem) {
        $checkin = $testItem['checkin'];
        $checkout = ($testItem['checkout'] ?? null);
        $expect = $testItem['expect'];
        $testResult = checkAvailability($room, $checkin, $checkout);
        echo 'room ' . $room 
            . ' on ' . $checkin . ' to ' . (!empty($checkout) ? $checkout : '<em><small>no plan to check out</small></em>') 
            . ' expect ' . (true === $expect ? '<span style="color: green; font-weight: bold;">' : '<span style="color: red;">') . var_export($expect, true) . '</span>'
            . ', actual result ' . var_export($testResult, true) . '.';
        echo '<br>' . PHP_EOL;
        assert($testResult === $expect);
        unset($checkin, $checkout, $expect, $testResult);
    }
    unset($testItem);
}// endfunction; runTests
unset($room, $testsArray);


/**
 * Check availability.
 *
 * @param string $room Room number.
 * @param string $checkin Check in date.
 * @param string $checkout Check out date. It can be null or empty if there is no plan to check out yet.
 * @return bool Return `true` if selected room and dates is available. Return `false` if unavailable or occupied.
 */
function checkAvailability($room, $checkin, $checkout = null)
{
    global $dbh;

    $sql = 'SELECT * FROM `room_availability` WHERE `room` = :room';
    $sql .= ' AND ';
    if (empty($checkout)) {
        // if not enter check out date.
        $sql .= '(
            (
                `checkin` >= :checkin 
                AND (`checkout` >= :checkin OR `checkout` IS NULL)
            )
            OR
            (
                `checkin` <= :checkin
                AND (`checkout` >= :checkin OR `checkout` IS NULL)
            )
        )';
    } else {
        // if enter check out date.
        $sql .= '(
            (
                `checkout` IS NULL
                AND 
                (
                    (
                        `checkin` >= :checkin 
                        AND `checkin` <= :checkout
                    )
                    OR
                    (
                        `checkin` <= :checkin
                    )
                )
            )
            OR
            (
                (
                    `checkin` >= :checkin
                    AND `checkin` <= :checkout
                )
                OR
                (
                    `checkin` <= :checkin
                    AND `checkout` >= :checkin
                )
            )
        )';
    }
    $sth = $dbh->prepare($sql);
    $sth->bindValue(':room', $room);
    $sth->bindValue(':checkin', $checkin);
    if (!empty($checkout)) {
        $sth->bindValue(':checkout', $checkout);
    }
    $sth->execute();
    $result = $sth->fetchAll();
    $sth->closeCursor();
    unset($sql);
    if ((is_object($result) || is_array($result)) && !empty($result)) {
        // if found occupied, unavailable.
        // mark availability as false.
        return false;
    } else {
        return true;
    }
}// endfunciton; checkAvailability


unset($dbh);


echo '<hr>' . PHP_EOL;
echo '<h3>Display all data on DB.</h3>' . PHP_EOL;
include 'list-all.php';

PHP รุ่นที่ผู้เขียนใช้ในการทดสอบนั้นจะเป็นรุ่น PHP 8.2 แต่ทั้งนี้พบว่าสามารถทำงานได้ตั้งแต่ PHP 7.1 เรื่อยมา.

ใส่ความเห็น

อีเมลของคุณจะไม่แสดงให้คนอื่นเห็น ช่องข้อมูลจำเป็นถูกทำเครื่องหมาย *

คุณอาจใช้แท็กHTMLและแอททริบิวต์เหล่านี้: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>