Opening hours done right, in PHP and MySQL

7th February, 2015 - Posted by david

I often see different ways of storing and displaying opening hours for various businesses when browsing the web. Some seem to simply store them as an unstructured blob of plaintext and spit that back to the user. Others will store the exact times for each day and display all 7 days on 7 separate rows.

I think neither of these options are great and I’ve come up with what I think is the ideal solution. Basically, we want to store the exact time for each day, but group similar days together. So, you could have lines like ‘Mon – Thurs: 9 – 5’, but if it’s say Tuesday at 10am, you’d also know that the business is currently open. I also think you could have alternative text to display when the business is closed (indicated by open and closed being NULL), so you could have something like ‘Sat – Sun: Open by appointment’.

So, let’s start with our table layout, see below. The primary key is id, which is just your standard auto_increment value. business_id is if you have multiple businesses, each with their own opening hours, as was the case for me. You might want to build an index on this field too. If you’re just storing your own opening hours, dow could be the primary key and you could drop those last 2 fields. open and closed are just simple strings, to store the time in 24-hour ‘HH:MM’ format. Doing it this way, you can still to comparisons like WHERE open > '09:00' and get the result you were expecting.

1
2
3
4
5
6
7
8
9
10
+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | mediumint(9) unsigned | NO   | PRI | NULL    | auto_increment |
| business_id   | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| dow           | tinyint(1) unsigned   | NO   |     | NULL    |                |
| open          | char(5)               | YES  |     | 09:00   |                |
| closed        | char(5)               | YES  |     | 17:30   |                |
| optional_text | char(100)             | YES  |     | NULL    |                |
+---------------+-----------------------+------+-----+---------+----------------+

Next up, I want to show you a quick function to format the time. Being a programmer, the time ’13:30′ is easily translated to ‘1.30pm’, but for the general public it might not be so simple, so this function will display your time in a more human readable format, such as the example given above. Basically, we want to drop any leading 0s, any 0-value minutes, convert the time to a 12-hour version with ‘am’ and ‘pm’ and change the ‘:’ to a ‘.’ (this last bit is probably a bit region-specific). For midnight, we’ll store that as ’24:00′ (which I know is technically the start of the next day!) and display that to the user as ‘midnight’, instead of the slightly confusing ‘0am’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
 * Function to change an opening hour to a more human readable version,
 * e.g., 09:00 to 9am or 13:30 to 1.30pm
 *
 * @param   String  $time  Time to format, in HH:MM format
 *
 * @return  String         Formatted time
 *
 */

function format_opening_hour($time) {
  if ($time == '24:00') {
    $new_time = 'midnight';
  }
  else {
    list($hours, $minutes) = explode(':', $time);
    $hours = ltrim($hours, '0');
    $am_pm = ($hours >= 12) ? 'pm' : 'am';
    if ($hours > 12) $hours -= 12;
    $new_time = $hours;
    if ($minutes != '00') {
      $new_time .= '.'.$minutes;
    }
    $new_time .= $am_pm;
  }
  return $new_time;
}

OK, so displaying the data in a nice table, with similar days grouped together, is the next bit. We’ll have 2 columns, one for the day/days, the other for the time. If a day has a value for optional_text, then that value will be displayed and the times are ignored. I’m also going to add another block of optional text ($extra_text below) that will be displayed at the end of the table and is applied for all days, to be used for something like ‘phone anytime’. Finally, there’s a $short_day_names option, so you can choose between say ‘Mon’ and ‘Monday’.

I should also mention at this point: I’m returning a block of HTML here from a function, as well as mixing business logic with display logic; I realise this is generally a bad idea and some of this could be split into a function and a template, but seeing as it’s a simple 2-column table, I just kept it all together.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
/**
 * Function to generate a simple html table for a business' opening hours
 *
 * @param   Array   $opening_hours    Array of rows from opening_hours table, sorted by dow (0-indexed, starting with Monday)
 * @param   String  $extra_text       Extra block of generic text that applies to all days, goes at end of table
 * @param   String  $short_day_names  Whether to use e.g. 'Mon' or 'Monday'
 *
 * @return  String                    HTML <table> output
 *
 */

function opening_hours_table($opening_hours, $extra_text='', $short_day_names=false) {
  $dow = array(
    array('long' => 'Monday', 'short' => 'Mon'),
    array('long' => 'Tuesday', 'short' => 'Tue'),
    array('long' => 'Wednesday', 'short' => 'Wed'),
    array('long' => 'Thursday', 'short' => 'Thu'),
    array('long' => 'Friday', 'short' => 'Fri'),
    array('long' => 'Saturday', 'short' => 'Sat'),
    array('long' => 'Sunday', 'short' => 'Sun')
  );
  $key = ($short_day_names) ? 'short' : 'long';

  // first, find similar days and group them together
  if (!empty($opening_hours)) {
    $opening_short = array();
    // start with current day
    for ($i=0; $i<7; $i++) {
      $temp = array($i);
      // try to find matching adjacent days
      for ($j=$i+1;$j<7;$j++) {
        if (empty($opening_hours[$i]['optional_text']) &&
            empty($opening_hours[$j]['optional_text']) &&
            $opening_hours[$i]['open'] == $opening_hours[$j]['open'] &&
            $opening_hours[$i]['closed'] == $opening_hours[$j]['closed'] ||
            !empty($opening_hours[$i]['optional_text']) &&
            !empty($opening_hours[$j]['optional_text']) &&
            strtolower($opening_hours[$i]['optional_text']) == strtolower($opening_hours[$j]['optional_text']) ) {
          // we have a match, store the day
          $temp[] = $j;
          if ($j == 6) $i = 6; // edge case
        }
        else {
          // otherwise, move on to the next day
          $i = $j-1;
          $j = 7; // break
        }
      }
      $opening_short[] = $temp; // $temp will be an array of matching days (possibly only 1 day)
    }
  }

  $html = '<table>';
  $colspan = '';

  if (!empty($opening_short)) {
    $colspan = ' colspan="2"';
    foreach ($opening_short as $os) {
      $day_text = $dow[$os[0]][$key];
      if (count($os) > 1) { // if there's another, adjacent day with the same time
        $end = array_pop($os); // get the last one
        $end = $dow[$end][$key];
        $day_text = $day_text . ' - ' . $end; // append the day to the string
      }
      // at this point, $day_text will be something like 'Monday' or 'Monday - Thursday'
      if (!empty($opening_hours[$os[0]]['optional_text'])) {
        // optional string takes precedent over any opening hours that may be set
        $hours_text = htmlentities($opening_hours[$os[0]]['optional_text']);
      }
      elseif (!empty($opening_hours[$os[0]]['open'])) {
        // otherwise generate something like '9am - 5.30pm'
        $hours_text = format_opening_hour($opening_hours[$os[0]]['open']) . ' - ' .format_opening_hour($opening_hours[$os[0]]['closed']);
      }
      else {
        // if nothing, it must be closed on that day/days
        $hours_text = 'Closed';
      }
      // new row for our table
      $html .= '<tr>
        <td>'
.$day_text.':</td>
        <td>'
.$hours_text.'</td>
      </tr>'
;
    }
  }

  // append the extra block of text at the end of the table
  if (!empty($extra_text)) {
    $html .= '<tr>
      <td'
.$colspan.'>'.htmlentities($extra_text).'</td>
    </tr>'
;
  }

  $html .= '</table>';
  return $html;
}

So, with the following data…

1
2
3
4
5
6
7
8
9
10
11
+-----+-------+--------+----------------+
| dow | open  | closed | extra          |
+-----+-------+--------+----------------+
|   0 | 09:00 | 17:30  | NULL           |
|   1 | 09:00 | 17:30  | NULL           |
|   2 | 09:00 | 17:30  | NULL           |
|   3 | 09:30 | 19:00  | NULL           |
|   4 | 09:30 | 19:00  | NULL           |
|   5 | 09:30 | 16:00  | NULL           |
|   6 | NULL  | NULL   | By appointment |
+-----+-------+--------+----------------+

you should end up with the following table:

Monday – Wednesday 9am – 5.30pm
Thursday – Friday 9.30am – 7pm
Saturday 9.30am – 4pm
Sunday By appointment
Phone anytime before 11pm

Tags: mysql php | david | 7th Feb, 2015 at 11:22am | No Comments

No Comments

Leave a reply

You must be logged in to post a comment.