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

Read more...

Include comments in search in The Bug Genie

4th April, 2013 - Posted by david

Back in July, I was given the task of finding a decent, preferably PHP-based, bug-tracking system and opted for The Bug Genie, as it had pretty much everything we wanted: email notifications, tickets grouped by project, various levels of user access and more. One thing we noticed however, was that when you searched all issues for a particular string, The Bug Genie only searched the main issue text and omitted any text in the comments. This wasn’t ideal, so I went through the source code, found where the searches were being performed and wrote a short hack to get what I wanted. I must stress, this is a hack and I’m sure could be done alot more elegantly! I just didn’t have the time to tidy it up.

What the code does is perform a simple LIKE on the comments table for your search term, gets each comment’s parent’s issue ID and includes any issues with the set of matched IDs in the main search results. The code snippet below is to go in function findIssues in core/classes/B2DB/TBGIssuesTable.class.php. I’ve included the few lines of code directly above where my code needs to be inserted:

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
// ... rest of function findIssues above
if ($filter_info['operator'] == '=')
{
    $ctn = $crit->returnCriterion(self::TITLE, $searchterm, Criteria::DB_LIKE);
    $ctn->addOr(self::DESCRIPTION, $searchterm, Criteria::DB_LIKE);
    $ctn->addOr(self::REPRODUCTION_STEPS, $searchterm, Criteria::DB_LIKE);
    $ctn->addOr(TBGIssueCustomFieldsTable::OPTION_VALUE, $searchterm, Criteria::DB_LIKE);
    //****** my code begins here
    // manually connect to DB
    $c = mysqli_connect(Core::getHost(), Core::getUname(), Core::getPasswd(), Core::getDBname());
    // search comments table for the text you're looking for
    $query = mysqli_query($c, 'SELECT target_id FROM tbg3_comments WHERE content LIKE \''.mysqli_real_escape_string($c, $searchterm).'\'');
    // if we've matches, build up an array
    $ids = array();
    while ($row = mysqli_fetch_row($query))
    {
        $ids[] = $row[0];
    }
    if (count($ids))
    {
        // add clause to map any found target_ids on the comments table to actual id's on the issues table
        $ctn->addOr(self::ID, $ids, Criteria::DB_IN);
    }
    //****** rest of function can continue on
}
else {
    // etc.

Read more...

MySQL reached max integer limit

23rd August, 2011 - Posted by david

Whenever anything generates a MySQL error at work, the whole technical team gets an email about it, with full debug info. We recently got one for an area that I look after, so it was up to me to investigate. The title of the error was a slightly cryptic

Error: Duplicate entry ‘0’ for key 1

Clearly this had something to do with the primary key, which was a simple unique integer ID. As this is a pretty large table, I had a feeling that we had reached the upper limit of what could be stored for the field type (MySQL’s MEDIUMINT, signed). Looking at the maximum value for the ID, I saw it was 8388607; according to this table on the MySQL website, this value is the maximum that can be stored in that type of field, so this was clearly the problem. (N.B. an auto-incrementing field shouldn’t ever be defined as signed, as you’ll never go into the negative indices, but that was done before I came along!)

The solution? Surely a simple readjusting of the key to be unsigned, or to further future proofing, changing to the larger INT… That’s what I thought and quickly (in test first, of course!) did a

1
ALTER TABLE table_name CHANGE COLUMN id id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT

This should then change the maximum value allowed in the ID field to 16777215. So, to test this, I went to insert a row, specifying NULL for the primary key value. However, I still got the same error. Doing a DESC on the table told me that the ID field had been changed to MEDIUMINT UNSIGNED correctly, so that wasn’t the issue. After further research I determined that what happend was that MySQL’s internal counter for that auto increment field was still set to 0, due to the rollover caused after reaching the maximum integer value. To overcome this, you need to point the internal counter back to where it should be, i.e. the current maximum value of your ID field, as follows:

1
ALTER TABLE table_name AUTO_INCREMENT = 8388608

So, you set it to a value that’s one greater than the current maximum ID.

Read more...

Inconsistent JSON arrays using PHP’s json_encode

14th September, 2010 - Posted by david

I’d be amazed if anyone ever has come across this problem before, but it’s one that stumped me recently and I feel would make a good first ‘real’ post for this new blog.

Firstly, a bit of background: in work, we have an API that uses the XML-based SOAP protocol over HTTP. You can request data either using a PHP SOAP plug-in, or by passing your parameters as encoded JSON over a HTTPS connection. The data you get back is usually an array of properties, with various fields set. When data is requested in JSON, it’s returned in JSON. On the main server, we use memcached, which gets checked for a given query before we hit the main database. Whenever a query is made to the API, if there’s nothing in the cache, we hit the database and then cache the result for 5 minutes, to reduce the impact on our primary database; pretty standard stuff for a big website.

Read more...