基于MySQL的日程列表转表格及默认周历显示改造咨询

1. 将查询结果改为表格展示 #

修改display-data.php（数据接口） #

把原有列表输出替换为表格结构，直接返回可渲染的HTML：

<?php
// 假设原有数据库连接逻辑已存在
$selectedDate = $_GET['date'];
$selectedDate = date('Y-m-d', strtotime($selectedDate)); // 统一日期格式

$query = "SELECT nombre_pros FROM prospectos WHERE fechaev_pros = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $selectedDate);
$stmt->execute();
$result = $stmt->get_result();

// 输出表格结构
echo '<table border="1" cellpadding="8" cellspacing="0" style="width:100%">';
echo '<thead><tr><th>客户名称</th></tr></thead>';
echo '<tbody>';
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo '<tr><td>' . htmlspecialchars($row['nombre_pros']) . '</td></tr>';
    }
} else {
    echo '<tr><td colspan="1" style="text-align:center">该日期暂无日程记录</td></tr>';
}
echo '</tbody></table>';

$stmt->close();
$conn->close();
?>

修改display.php（前端页面） #

保留原有Datepicker逻辑，仅更新结果容器的填充方式，同时预留周历容器：

<!DOCTYPE html>
<html>
<head>
    <title>日程查询</title>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <style>
        #result-container { margin: 20px 0; }
        table { border-collapse: collapse; }
        th { background: #f5f5f5; }
        td, th { padding: 10px; border:1px solid #ddd; }
    </style>
</head>
<body>
    <h3>选择日期查询日程</h3>
    <p>日期: <input type="text" id="datepicker"></p>
    <div id="result-container"></div>

    <!-- 周历容器 -->
    <div id="weekly-calendar" style="margin-top:30px;"></div>

    <script>
        $(function() {
            $("#datepicker").datepicker({
                dateFormat: "yy-mm-dd",
                onSelect: function(dateText) {
                    $.ajax({
                        url: "display-data.php",
                        type: "GET",
                        data: { date: dateText },
                        success: function(response) {
                            $("#result-container").html(response);
                        }
                    });
                }
            });

            // 页面加载默认展示今日数据
            const today = $.datepicker.formatDate("yy-mm-dd", new Date());
            $.ajax({
                url: "display-data.php",
                type: "GET",
                data: { date: today },
                success: function(response) {
                    $("#result-container").html(response);
                }
            });
        });
    </script>
</body>
</html>

2. 实现基础HTML周历（带日程标记） #

第一步：添加周历样式 #

在display.php的<style>标签中补充：

.week-calendar { display: grid; grid-template-columns: repeat(7, 1fr); gap: 10px; margin-top:10px; }
.calendar-day { border:1px solid #ddd; padding:15px; text-align:center; border-radius:4px; cursor:pointer; }
.calendar-day.has-event { background:#e8f4f8; border-color:#2196F3; }
.calendar-header { font-weight:bold; background:#f0f0f0; padding:10px; text-align:center; }
.week-nav { display:flex; gap:10px; align-items:center; }
.week-nav button { padding:8px 12px; border:none; background:#f0f0f0; border-radius:4px; cursor:pointer; }
.week-nav button:hover { background:#ddd; }

第二步：添加周历生成逻辑 #

在display.php的<script>标签末尾添加：

// 获取指定日期所在周的所有日期
function getWeekDates(date) {
    const weekDates = [];
    const startOfWeek = new Date(date);
    // 以周日为一周起点，若要周一开头则改为：startOfWeek.setDate(date.getDate() - (date.getDay() || 7) + 1)
    startOfWeek.setDate(date.getDate() - date.getDay());

    for(let i=0; i<7; i++) {
        const day = new Date(startOfWeek);
        day.setDate(startOfWeek.getDate() + i);
        weekDates.push(day);
    }
    return weekDates;
}

// 查询一周内的日程数据
function fetchWeekEvents(weekDates) {
    const dateStrs = weekDates.map(d => $.datepicker.formatDate("yy-mm-dd", d));
    return $.ajax({
        url: "get-week-events.php",
        type: "GET",
        data: { dates: dateStrs.join(",") }
    });
}

// 渲染周历
function renderWeekCalendar(weekDates, eventsMap) {
    const weekNames = ['周日','周一','周二','周三','周四','周五','周六'];
    let html = '<div class="week-nav">';
    html += `<button onclick="renderWeek(new Date(${weekDates[0].getTime()} - 7*24*60*60*1000))">上一周</button>`;
    html += `<span>${$.datepicker.formatDate("yy年mm月dd日", weekDates[0])} - ${$.datepicker.formatDate("yy年mm月dd日", weekDates[6])}</span>`;
    html += `<button onclick="renderWeek(new Date(${weekDates[0].getTime()} + 7*24*60*60*1000))">下一周</button>`;
    html += '</div>';
    html += '<div class="week-calendar">';

    // 渲染表头
    weekNames.forEach(name => {
        html += `<div class="calendar-header">${name}</div>`;
    });

    // 渲染日期单元格
    weekDates.forEach(date => {
        const dateStr = $.datepicker.formatDate("yy-mm-dd", date);
        const hasEvent = eventsMap[dateStr] && eventsMap[dateStr].count > 0;
        html += `<div class="calendar-day ${hasEvent ? 'has-event' : ''}" onclick="selectCalendarDay('${dateStr}')">`;
        html += `<div>${date.getDate()}</div>`;
        if(hasEvent) {
            html += `<div style="font-size:12px; color:#2196F3; margin-top:5px;">${eventsMap[dateStr].count}条</div>`;
        }
        html += '</div>';
    });
    html += '</div>';
    $("#weekly-calendar").html(html);
}

// 点击周历日期触发查询
function selectCalendarDay(dateStr) {
    $("#datepicker").datepicker("setDate", dateStr);
    $.ajax({
        url: "display-data.php",
        type: "GET",
        data: { date: dateStr },
        success: function(response) {
            $("#result-container").html(response);
        }
    });
}

// 初始化周历
function renderWeek(date = new Date()) {
    const weekDates = getWeekDates(date);
    fetchWeekEvents(weekDates).done(eventsMap => {
        renderWeekCalendar(weekDates, eventsMap);
    });
}

// 页面加载时渲染当前周
$(document).ready(function() {
    renderWeek();
});

第三步：新增get-week-events.php接口 #

用于查询一周内各日期的日程数量：

<?php
// 数据库连接逻辑（与display-data.php一致）
$dates = explode(",", $_GET['dates']);
$placeholders = implode(',', array_fill(0, count($dates), '?'));

$query = "SELECT fechaev_pros, COUNT(nombre_pros) as count FROM prospectos WHERE fechaev_pros IN ($placeholders) GROUP BY fechaev_pros";
$stmt = $conn->prepare($query);

// 绑定参数
$types = str_repeat('s', count($dates));
$stmt->bind_param($types, ...$dates);
$stmt->execute();
$result = $stmt->get_result();

$eventsMap = [];
// 初始化所有日期的计数为0
foreach($dates as $date) {
    $eventsMap[$date] = ['count' => 0];
}

if($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $eventsMap[$row['fechaev_pros']]['count'] = $row['count'];
    }
}

echo json_encode($eventsMap);

$stmt->close();
$conn->close();
?>

核心说明 #

• 表格展示：通过修改数据接口的输出格式为HTML表格，前端直接插入即可，样式可按需调整。
• 基础周历：通过JS生成周日期网格，AJAX批量查询一周日程数，给有日程的日期添加高亮标记，点击日期可直接触发对应日期的查询。
• 格式统一：全程使用yy-mm-dd格式处理日期，避免数据库查询的格式不匹配问题。

内容的提问来源于stack exchange，提问作者4nt4rk