php mysql and nested loops

Question

I have a database with following entry:

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

And i have the following php code which generates a calendar then based on the database entry, it marks the specified days with specific color:

<?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>";
}

The problem is, it shows the first date in database two times and the other date is displayed only 1 time. How do I manage to display both dates only one time.

You may need to run the php code on your end.

First off: **[Stop using the `mysql_*` functions](http://stackoverflow.com/q/12859942/2302862)!** — Siguza, Jun 27 '15 at 20:22
You realize that you are executing the same query and going through the same data set over and over again? It is extremely inefficient. You have to turn over your loops - run sql query once, go through the data set once and print dates with specific color. — dmitreyg, Jun 27 '15 at 21:04

ptkoz · Accepted Answer · 2015-06-27T21:09:46.547

The problem is caused either by using $bookeddate variable out of scope or messing up with braces.

This part of code is outside of your while ($row = mysql_fetch_assoc($getr)) loop:

    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>";

But you're using $bookeddate here, which is defined inside the loop. Here it has the value of last row of the query and causes duplication.

Here's what you can do to avoid duplication with one additional variable, but you rather should refactor your code completely.

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>";
    }
}

Here's the better and cleaner approach.

$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>";

}

But if the code is included inside the while loop then every date is displayed twice. — Saifi, Jun 27 '15 at 20:54
It shouldn't be. Actually you should not output anything in while loop. The better aproach is to check for date state and save it into variable, then put echo only in one place inside for loop. I'll post it in my answer. — ptkoz, Jun 27 '15 at 20:58
@Saifi, check if you understand the concept I've posted above. — ptkoz, Jun 27 '15 at 21:10
There are also tons of possible enhancements. Can you use PHP's native DatePeriod class? — ptkoz, Jun 27 '15 at 21:18
Yes @pamelus I understand it now, Thanks a lot for your help! — Saifi, Jun 29 '15 at 19:13

php mysql and nested loops

1 Answers1