Check for multiple rows - PHP & MySQL

Question

I am cycling through cells in a HTML table using PHP to color specific cells. I want to color the cells that return true to this statement:

if ($currentCell >= $reservationStartDayOfMonth && $currentCell <= $reservationEndDayOfMonth) {
    make red
} else {
    make normal color
}

This is not the exact statement, but the idea of it. I get the reservationEndDayOfMonth and reservationStartDayOfMonth from a MySQL server. For each row in the SQL server, there is a new reservation with a start and an end date. Along with the id (1...2...3...)

Basically I got a reservation in the current month. And it works when I just cycle through the cells with the data from ONE reservation. But I got lots of reservations in my database. I need to check and see if the current cell corresponds to any of the many reservations I have.

My code is long and bulky. It's basically just a table with all the checkers and parameters inside. Here you go:

<table border="1" id="calendar">


<?php

$dk_database = array("Januar","Februar","Marts","April","Maj","Juni","Juli","August","September","Oktober","November","December");
$dk_month = $dk_database[(int)date("m")-1];

ECHO '<caption><h1 style="color:#554100;">' . $dk_month . '</h1></caption>';



$sqldateStart = sqlRequest("SELECT res_datestart FROM spottrup_reservations WHERE res_id = 1");
$dateStartRaw = mysql_fetch_array($sqldateStart);
$dateStartArray = explode("-", $dateStartRaw[0]);
$dateStartDay = (int)$dateStartArray[2];
$dateStartMonth = (int)$dateStartArray[1];
$dateStartYear = (int)$dateStartArray[0];

$sqldateEnd = sqlRequest("SELECT res_dateend FROM spottrup_reservations WHERE res_id = 1");
$dateEndRaw = mysql_fetch_array($sqldateEnd);
$dateEndArray = explode("-", $dateEndRaw[0]);
$dateEndDay = (int)$dateEndArray[2];
$dateEndMonth = (int)$dateEndArray[1];
$dateEndYear = (int)$dateEndArray[0];

$currentYear = (int)date("y")+2000;
$currentDate = (int)date("d");
$currentMonth = (int)date("m");
$columnsInRow = 7;
$daysInMonth = date("t");

$currentDay = 1;
for ($i=0 ; $i < $daysInMonth ; $i++) {
    ECHO '<tr class="cal">';
    for ($j=0 ; $j < $columnsInRow && $currentDay <= $daysInMonth ; $j++) {
        if($currentDate==$currentDay) {
            if($currentDay>=$dateStartDay && $currentDay<=$dateEndDay && $currentMonth==$dateStartMonth && $currentYear==$dateStartYear) {
                ECHO '<div style="background:blue;height:100%;width:100%text-decoration:none;"><td class="cal"><a href="date_info.php?currentDay=' . $currentDay . '"><h2 style="color:red;">' . $currentDay . '</h2></a></td></div>';
            }else{
                ECHO '<div style="background:#D4BB6A;height:100%;width:100%text-decoration:none;"><td class="cal"><a href="date_info.php?currentDay=' . $currentDay . '"><h2 style="color:red;">' . $currentDay . '</h2></a></td></div>';
            }
        }else{
            if($currentDay>=$dateStartDay && $currentDay<=$dateEndDay && $currentMonth==$dateStartMonth && $currentYear==$dateStartYear) {
                ECHO '<div style="background-color:blue;height:100%;width:100%text-decoration:none;"><td class="cal" style="background-color:#FF8080;"><a href="date_info.php?currentDay=' . $currentDay . '"><h2 style="color:#554100;">' . $currentDay . '</h2></a></td></div>';
            }else{
                ECHO '<div style="background-color:#D4BB6A;height:100%;width:100%text-decoration:none;"><td class="cal"><a href="date_info.php?currentDay=' . $currentDay . '"><h2 style="color:#554100;">' . $currentDay . '</h2></a></td></div>';
            }
        }
        $currentDay++;
    }
    ECHO '</tr>';
}
?>

The sqlRequest(string); calls a function that returns the result of the query you give.

Please, [don't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — Jay Blanchard, Oct 02 '14 at 12:56

score 0 · Accepted Answer · answered Oct 02 '14 at 13:55

Not the most efficient way since lots of queries but it does reduce a for loop, but you could run a query for each day to see if there is a reservation for it. Simple to implement.

for ($i=0 ; $i < $daysInMonth ; $i++) 
{
   ...
  $query = "Select * from table where currentDate >= startDate and currentDate <= endDate"
   ...
}

Check for multiple rows - PHP & MySQL

1 Answers1