PHP - Turn mysql time-stamps into display grid

Question

I am quite stuck on this one. I have a MySQL db full of "reservations", and I am trying to build a grid to display availability of appointments.

For each entry, I have a start time and end time in the format of: YYYY-MM-DD HH:MM:SS (i.e. 2015-06-17 12:00:00).

I am looking for methods of turning these entries into a display grid of the day, with booked times blocked out (by a color), and non booked times as a different color.

Example: If my sql query came back with one entry, with 12pm as start time, and 2pm as end time, the grid created would look something like this:

Bad example, but gets the idea across.

I would include examples of what I have tried, but to be honest, I dont know where to begin to accomplish this. - I can easylly pull the data, its getting it to display like my example that has me stuck.

I am not looking for finished code or for anyone to do my work for me, just a push in the right direction of how to turn times into something like my example.

Thank you very much!

Answer 1

Looks like you want to render a reservation times grid. Ok, let's go.

• I would start by defining the possible reservation times.
• Then i would check the request: did the user request a week or a day. In the example i use a week view.
• Then render_reservation_table() for week (or day) using the defined reservation times and while iterating these times, ask your database, if this week/day/time has an entry read_reservation().
• If an entry is present: render that (details) and color red, else render "Click to book" with color green.

The key is the comparison between rendering data and database data. Based on the rendering data (week, day, time) the database is asked. You have written that you "can easily pull the data". You would build timestamps using the render data and then query the database for an entry (or entries):

SELECT * FROM events WHERE `date` BETWEEN '2015-01-01 09:00:01' AND '2015-01-01 11:59:59'

There are lot's of ways to improve this. Especially the read_reservation() function would later contain the database select query. You could query a day or week on the first call of that function and than working on the result set for data and time lookups, when the function is called again during render.

This should get you started:

<?php

// (1) Define the possible reservation times. Syntax: 'TimeStart-TimeEnd'

$reservation_times = ['09-10', '10-11', '11-12', '12-13', '13-14', '14-15', '15-16', '16-17'];

// (2) What is the request? 
//     Week Display / Day display - $week = $_GET['week'];
//     Hardcoded for demo purpose.

$week = 20;

/**
 * Renders a reservation table for a certain week
 */
function render_reservation_table($week, $reservation_times)
{
    echo '<table><colgroup span="1" id="reservation_time_colgroup"></colgroup><colgroup span="7" id="reservation_day_colgroup"></colgroup>';

    foreach($reservation_times as $time)
    {
        echo '<tr><th class="reservation_time_th">' . $time . '</th>';

        $i = 0;

        while($i < 7)
        {
            $i++;

            echo '<td><div class="reservation_time_div">';
            echo '<div class="reservation_time_cell_div" id="div:' . $week . ':' . $i . ':' . $time . '" onclick="void(0)">';
            echo read_reservation($week, $i, $time);
            echo '</div></div></td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

function read_reservation($week, $i, $time)
{     
    // ==> ask database and compare

    // here hardcoded for demo: this is week 20, day 2, time 10-11
    if($week === 20 && $i === 2 && $time === '10-11') {

        // Booked
        echo '<div style="background:red;">Already booked.</div>';

    } else {

        // Not booked.
        echo '<div style="background:lightgreen;">Click to book.</div>';

    }
}

render_reservation_table($week, $reservation_times);

It looks like this (week view):