php mysql 和嵌套循环

Question

我有一个包含以下条目的数据库:

Id | UserID | Date     | Time
1  | 1      | 06/29/15 | d
2  | 1      | 06/30/15 | n

我有以下 php 代码，它生成一个日历，然后根据数据库条目，用特定颜色标记指定的日期:

<?php
$cMonth = $_REQUEST["month"];
$cYear = $_REQUEST["year"];
$timestamp = mktime(0,0,0,$cMonth,1,$cYear);
$maxday = date("t",$timestamp);
$thismonth = getdate ($timestamp);
$startday = $thismonth['wday'];
$toddate = date("j");
$todmon = date("n");

for ($i=0; $i<($maxday+$startday); $i++)
{
    if (($i % 7) == 0)
        echo "<tr>";
    $getr = mysql_query("SELECT * FROM `reservations` WHERE `UserID` = 1");
    $datee = $i - $startday + 1;
    $nums = mysql_num_rows($getr);
    while ($row = mysql_fetch_assoc($getr))
    {
        $timestamp = strtotime($row['Date']);
        $bookeddate = date("d", $timestamp);
        $dates[$nums] = $bookeddate;
        $bookedmon = date("n", $timestamp);
        if ($cMonth == $bookedmon && $row['Time'] == 'd' && $bookeddate == $datee)
            echo "<td title='day booked' align='center' bgcolor='orange' valign='middle' height='20px'>". ($datee) . "</td>";
        else if ($cMonth == $bookedmon && $row['Time'] == 'n' && $bookeddate == $datee)
            echo "<td title='Night booked' align='center' bgcolor='black' color='white' valign='middle' height='20px'>". ($datee) . "</td>";
        else if ($cMonth == $bookedmon && $row['Time'] == 'c' && $bookeddate == $datee)
            echo "<td title='Completely booked' align='center' bgcolor='red' valign='middle' height='20px'>". ($datee) . "</td>";
    }
    if($i < $startday)
        echo "<td></td>";
    else if ($datee == $toddate && $cMonth == $todmon)
        echo "<td title='today' align='center' bgcolor='lime' valign='middle' height='20px'>". ($datee) . "</td>";
    else if ($datee != $bookeddate)
        echo "<td align='center' valign='middle' height='20px'>". ($datee) . "</td>";
    if(($i % 7) == 6)
        echo "</tr>";
}

问题是，它在数据库中显示第一个日期两次，而另一个日期仅显示一次。我如何设法只显示两个日期一次。

您可能需要在您的一端运行 php 代码。

Answer 1

该问题是由于使用超出范围的 $bookeddate 变量或弄乱了大括号而引起的。

这部分代码位于 while ($row = mysql_fetch_assoc($getr)) 循环之外:

    if($i < $startday) echo "<td></td>";
    else if ($datee == $toddate && $cMonth == $todmon) echo "<td title='today' align='center' bgcolor='lime' valign='middle' height='20px'>". ($datee) . "</td>";
    else if ($datee != $bookeddate) echo "<td align='center' valign='middle' height='20px'>". ($datee) . "</td>";
    if(($i % 7) == 6 ) echo "</tr>";

但是您在这里使用的是 $bookeddate，它是在循环内定义的。这里它具有查询最后一行的值并导致重复。

您可以采取以下措施来避免与一个附加变量重复，但您应该完全重构您的代码。

for ($i=0; $i<($maxday+$startday); $i++) {
    $getr = mysql_query("SELECT * FROM `reservations` WHERE `UserID` = 1");
    $datee = $i - $startday + 1;
    $nums = mysql_num_rows($getr);
    $displayed = false;
    while ($row = mysql_fetch_assoc($getr)) {
        $timestamp = strtotime($row['Date']);
        $bookeddate = date("d", $timestamp);
        $dates[] = $bookeddate;
        $bookedmon = date("n", $timestamp);
        if ($cMonth == $bookedmon && $row['Time'] == 'd' && $bookeddate == $datee) {
            echo "<td title='day booked' align='center' bgcolor='orange' valign='middle' height='20px'>". ($datee) . "</td>";
            $displayed = true;
        } else if ($cMonth == $bookedmon && $row['Time'] == 'n' && $bookeddate == $datee) {
            echo "<td title='Night booked' align='center' bgcolor='black' color='white' valign='middle' height='20px'>". ($datee) . "</td>";
            $displayed = true;
        } else if ($cMonth == $bookedmon && $row['Time'] == 'c' && $bookeddate == $datee) {
            echo "<td title='Completely booked' align='center' bgcolor='red' valign='middle' height='20px'>". ($datee) . "</td>";
            $displayed = true;
        }
    }

    if(!$displayed) {
        if($i < $startday) echo "<td></td>";
        else if ($datee == $toddate && $cMonth == $todmon) echo "<td title='today' align='center' bgcolor='lime' valign='middle' height='20px'>". ($datee) . "</td>";
        else if ($datee != $bookeddate) echo "<td align='center' valign='middle' height='20px'>". ($datee) . "</td>";
        if(($i % 7) == 6 ) echo "</tr>";
    }
}

这是更好、更简洁的方法。

$timestamp = mktime(0,0,0,$cMonth,1,$cYear);
$maxday = date("t",$timestamp);
$thismonth = getdate ($timestamp);
$startday = $thismonth['wday'];
$toddate = date("j");
$todmon = date("n");


const DATE_FREE = 0;
const DATE_DAY_BOOKED = 1;
const DATE_NIGHT_BOOKED = 2;
const DATE_COMPLETELY_BOOKED = 3;
const DATE_TODAY = 4;

for ($i=0; $i<($maxday+$startday); $i++) {
    if(($i % 7) == 0 ) echo "<tr>";

    if($i < $startday) {
        echo "<td></td>"; 
        continue;
    }

    $datee = $i - $startday + 1;
    $dateStatus = DATE_FREE;

    $getr = mysql_query("SELECT * FROM `reservations` WHERE `UserID` = 1");
    while ($row = mysql_fetch_assoc($getr)) {
        $timestamp = strtotime($row['Date']);
        $bookeddate = date("d", $timestamp);

        $bookedmon = date("n", $timestamp);
        if ($cMonth == $bookedmon && $row['Time'] == 'd' && $bookeddate == $datee) {
            $dateStatus = DATE_DAY_BOOKED;
        } else if ($cMonth == $bookedmon && $row['Time'] == 'n' && $bookeddate == $datee) {
            $dateStatus = DATE_NIGHT_BOOKED;
        } else if ($cMonth == $bookedmon && $row['Time'] == 'c' && $bookeddate == $datee) {
            $dateStatus = DATE_COMPLETELY_BOOKED;
        }
    }

    if (DATE_FREE == $dateStatus && $datee == $toddate && $cMonth == $todmon) {
        $dateStatus = DATE_TODAY;
    }

    echo "<td align='center' valign='middle' height='20px'";
    switch($dateStatus) {
        case DATE_DAY_BOOKED:
            echo " title='day booked' bgcolor='orange'";
            break;
        case DATE_NIGHT_BOOKED:
            echo " title='Night booked' bgcolor='black'";
            break;
        case DATE_COMPLETELY_BOOKED:
            echo " title='Completely booked' bgcolor='red'";
            break;
        case DATE_TODAY;
            echo " title='today' bgcolor='lime'";
            break;
    }
    echo ">". ($datee) . "</td>";

    if(($i % 7) == 6 ) echo "</tr>";

}