Printing an HTML table, based on available rooms in a booking system

Question

Good day everyone,

I am trying to print an HTML table; given two dates (1st July until 31st July, in the example), it will print the dates on the header. Then, every row will be a room. Then, when a room is booked that day, the cells will have a red background-color, if not, it will remain white.

I am getting lost in an IF to check if a room should be printed red or not. So far, I get this output: http://jsfiddle.net/KatsuroKurosaki/kCkJD/

And I am trying to achieve this one: http://jsfiddle.net/KatsuroKurosaki/kCkJD/1/

Based on the DataBase query output:

SELECT idsRoom, checkin, checkout
FROM bookings
WHERE checkout >= '2014-07-01' AND checkin <= '2014-07-31'

+---------+------------+------------+
| idsRoom | checkin    | checkout   |
+---------+------------+------------+
| 2       | 2014-06-27 | 2014-07-02 |
| 4       | 2014-07-08 | 2014-07-09 |
| 6,7,8   | 2014-07-18 | 2014-07-22 |
| 14      | 2014-07-31 | 2014-08-02 |
+---------+------------+------------+
4 rows in set (0.00 sec)

Yes, every booking has a checkin and a checkout, as well it must contain at least one room, or more (that's why the ids 6,7,8). I am using PHP 5.5.13, MySQLi prepared statements and Maria DB 10.0.12. This is the PHP snippet, that will print the output table:

<?php
/* This will be the received POST date in the future */
$desdeP = "2014-07-01";
$hastaP = "2014-07-31";

/* Database Connection and DateTime objects */
$conn = new MySQLi("localhost","user","password","database"); //Seriusly? Nope ;)
$desde = DateTime::createFromFormat("Y-m-d",$desdeP);
$hasta = DateTime::createFromFormat("Y-m-d",$hastaP);
?>
<!-- Table with date headers -->
<table border="1" cellpadding="0" cellspacing="0" style="min-width:100%;min-height:100%;">
<tr>
    <td>&nbsp;</td>
    <?php
    while($desde<=$hasta){
        echo '<td>'.$desde->format("d-m-Y").'</td>';
        $desde->modify("+1Day");
    }
    ?>
</tr>
<?php
/* Query all the rooms */
$stmt = $conn->prepare("SELECT id, name FROM rooms ORDER BY id;");
$stmt->execute();
$rooms = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

/* Query all the bookings in the given dates */
$stmt = $conn->prepare("SELECT idsRoom, checkin, checkout
FROM bookings
WHERE checkout >= ? AND checkin <= ?;");
$stmt->bind_param("ss",
    $desdeP,
    $hastaP);
$stmt->execute();
$bookings = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

foreach($rooms as $k=>$v){ // Every row is a room
    echo '<tr>';
    echo '<td>'.$v['name'].'</td>';
    $desde = DateTime::createFromFormat("Y-m-d",$desdeP);
    $hasta = DateTime::createFromFormat("Y-m-d",$hastaP);
    while($desde<=$hasta){ // For every row, check in the bookings list if available or not
        echo '<td';
        foreach ($bookings as $k2=>$v2){
            $checkin = DateTime::createFromFormat("Y-m-d",$v2['checkin']);
            $checkout = DateTime::createFromFormat("Y-m-d",$v2['checkout']);
            /* HERE remains my question: What mega IF do I need to paint background in red if the room is not available? */
            if( (strpos(",".$v2['idsRoom'].",",",".$v['id'].",")!==false) && 
                ($checkin < $hasta && $checkout > $desde) && 
                ($checkin >= $desde && $checkout <= $hasta)
            ){
                echo ' style="background-color:red;"';
            }
        }
        echo '>&nbsp;</td>';
        $desde->modify("+1Day");
    }
    echo '</tr>';
}
$conn->close();
?>
</table>

Thanks to all in advance, hope to find some help about it~

Regards!

Answer 1

After a quick look at the question i noticed that you have a comma separated values in id column. That is certainly causing you troubles, and will cause you more troubles in the future. I'm not a database design expert, when I started with programming I was doing the same thing. I wanted to do something quick and easy, without too much hassle with relationships, and it always turn out into a mess so that eventually had to start over from the beginning, and do it properly.

This design anti-pattern will make querying much more difficult, also, you have to write a lot of code to reinvent the built-in functionality of the database that is lost ( you have already started to do that, strpos(",".$v2['idsRoom'].",",",".$v['id'].",")!==false ).

I took some time to rewrite the example. For the range of dates I use the DatePeriod class. It can be used in foreach loop so it is suitable for calendars, etc. Also, here are two associative arrays ( $rooms and $bookings ) that are identical like in example. After converting the $bookings array to separate the idsRoom values it looks like:

Array
(
    [0] => Array ( ... )
    [1] => Array ( ... )
    [2] => Array
        (
            [idsRoom] => 6
            [checkin] => 2014-07-18
            [checkout] => 2014-07-22
        )

    [3] => Array
        (
            [idsRoom] => 7
            [checkin] => 2014-07-18
            [checkout] => 2014-07-22
        )

    [4] => Array
        (
            [idsRoom] => 8
            [checkin] => 2014-07-18
            [checkout] => 2014-07-22
        )

    [5] => Array (... )
)

And finally, loop to print the table:

<?php

$range = new DatePeriod( new DateTime( "2014-07-01" ), new DateInterval( 'P1D' ), new DateTime( "2014-08-01" ) );

$rooms = array_map( function( $k, $v ) { return array( 'name' => $v, 'id' => $k ); }, range( 1, 30 ), array_merge( range( 101, 110 ), range( 201, 210 ), range( 301, 310 ) ) );

$bookings_original = array( 
    array( 'idsRoom' => '2',    'checkin' => '2014-06-27', 'checkout' => '2014-07-02' ),
    array( 'idsRoom' => '4',    'checkin' => '2014-07-08', 'checkout' => '2014-07-09' ),
    array( 'idsRoom' => '6,7,8','checkin' => '2014-07-18', 'checkout' => '2014-07-22' ),
    array( 'idsRoom' => '14',   'checkin' => '2014-07-31', 'checkout' => '2014-08-02' )
);

$bookings = array();
foreach( $bookings_original as $item ) {
    foreach( explode( ',', $item['idsRoom'] ) as $room )
        $bookings[] = array_merge( $item, array( 'idsRoom' => $room ) );
}

?>
<table border="1" cellpadding="0" cellspacing="0" style="min-width:100%;min-height:100%;">
    <tr>
        <td>&nbsp;</td>
        <?php foreach( $range as $date ) : ?>
        <td><?php echo $date->format("d-m-Y"); ?></td>
        <?php endforeach; ?>
    </tr>
<?php

foreach( $rooms as $room ) {
    echo "<tr><td>$room[name]</td>" . PHP_EOL;

    foreach( $range as $date ) {
        if (
            array_filter( $bookings, function( $booking ) use ( $room, $date ) {
                return $booking[ 'idsRoom' ] == $room['id'] && $booking['checkin'] <= $date->format( "Y-m-d" ) && $booking['checkout'] > $date->format( "Y-m-d" );
            } ) 
        )
            echo '<td style="background-color:red;"></td>' . PHP_EOL;
        else
            echo '<td></td>' . PHP_EOL;
    }
    echo '</tr>';
}

?>
</table>

Here is one booking design example that might help you with database and relationships between tables.

Answer 2

Because your $desde is a running date in your while loop, so you should do the checking that

If $desde is within $checkin and $checkout, then the room is blocked (red)

So, by using this logic, this is the only condition you need:

if ((strpos(",".$v2['idsRoom'].",",",".$v['id'].",")!==false) &&
    ($checkin <= $desde && $checkout > $desde)
){
    echo ' style="background-color:red;"';
}

I ran this on my localhost and it works as what you wanted in your example. Hope it helps.