PHP mysqli：从面向对象到过程式风格的转换与最佳实践

// 面向对象风格
$stmt = $mysqli->prepare("SELECT * FROM bookings WHERE MONTH(event_date) = ? AND YEAR(event_date)=?");

// 过程式风格
$stmt = mysqli_prepare($mysqli, "SELECT * FROM bookings WHERE MONTH(event_date) = ? AND YEAR(event_date)=?");

function build_calendar_oo($month, $year){
    $mysqli = new mysqli('localhost', 'root', '', 'ems');
    $stmt = $mysqli->prepare("SELECT * FROM bookings WHERE MONTH(event_date) = ? AND YEAR(event_date)=?");
    $stmt->bind_param('ss', $month, $year);
    $bookings = array();
    if($stmt->execute()){
        $result = $stmt->get_result();
        if($result->num_rows > 0){
            while($row = $result->fetch_assoc()){
                $bookings[] = $row['event_date'];
            }
            $stmt->close();
        }
    }
    return $bookings; // 添加返回值
}

function build_calendar_procedural($month, $year)
{
    // 建立连接
    $mysqli = mysqli_connect('localhost', 'root', '', 'ems');
    if (!$mysqli) {
        die("连接失败: " . mysqli_connect_error());
    }

    // 准备语句
    $stmt = mysqli_prepare($mysqli, "SELECT * FROM bookings WHERE MONTH(event_date) = ? AND YEAR(event_date)=?");
    if (!$stmt) {
        die("语句准备失败: " . mysqli_error($mysqli));
    }

    // 绑定参数
    mysqli_stmt_bind_param($stmt, 'ss', $month, $year);

    $bookings = array();
    // 执行语句
    if (mysqli_stmt_execute($stmt)) {
        // 获取结果集
        $result = mysqli_stmt_get_result($stmt);
        if ($result && mysqli_num_rows($result) > 0) {
            // 遍历结果
            while ($row = mysqli_fetch_assoc($result)) {
                $bookings[] = $row['event_date'];
            }
            // 释放结果集
            mysqli_free_result($result);
        }
        // 关闭语句
        mysqli_stmt_close($stmt);
    } else {
        die("语句执行失败: " . mysqli_stmt_error($stmt));
    }

    // 关闭连接
    mysqli_close($mysqli);
    return $bookings;
}

// 错误的尝试
$connection = mysqli_connect('localhost', 'root', '', 'ems');
$query = "SELECT * FROM bookings WHERE MONTH(event_date) = ? AND YEAR(event_date)=?";
$get_dates = mysqli_query($connection, $query); // 错误点
while($row = mysqli_fetch_assoc($get_dates)){ // 导致 Fatal error
    $booked_dates[] = $row['event_date'];
}

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// 在代码的早期执行此配置，例如在连接数据库之前
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// 建立连接，通常在应用启动时一次性完成
$mysqli = new mysqli('localhost', 'root', '', 'ems');

/**
 * 从数据库中获取指定月份和年份的预订日期
 *
 * @param mysqli $mysqli 数据库连接对象
 * @param string $month 月份
 * @param string $year 年份
 * @return array 预订日期数组
 */
function build_calendar_optimized(mysqli $mysqli, string $month, string $year): array
{
    $stmt = $mysqli->prepare("SELECT event_date FROM bookings WHERE MONTH(event_date) = ? AND YEAR(event_date)=?");
    $stmt->bind_param('ss', $month, $year);
    $stmt->execute();
    $result = $stmt->get_result();
    // 使用 fetch_all(MYSQLI_ASSOC) 一次性获取所有结果
    // 注意：如果只需要 event_date 字段，可以只选择该字段
    $rows = $result->fetch_all(MYSQLI_ASSOC);

    // 从结果中提取 event_date 字段
    $bookings = array_column($rows, 'event_date');

    $stmt->close(); // 关闭语句
    return $bookings;
}

// 示例调用
try {
    $currentMonthBookings = build_calendar_optimized($mysqli, '01', '2023');
    print_r($currentMonthBookings);
} catch (mysqli_sql_exception $e) {
    echo "数据库操作失败: " . $e->getMessage();
} finally {
    // 确保在所有操作完成后关闭数据库连接
    if ($mysqli) {
        $mysqli->close();
    }
}