Storing Operating Hours in Mysql table

[thara]

I'm working on a website involving local restaurants, and one thing I need to do is store hours of operation.

This is how I get operating hours from business owners.

// Sunday - Saturday 11AM - 6PM
$_POST['from'], $_POST['to'], $_POST['opening_time'], $_POST['closing_time']

Then I need to store these operating hours in `MySql` like below format.

- Sunday: 9am - 11pm
- Monday: 9am - 11pm
- Tuesday: 9am - 11pm
- Wednesday: 9am - 11pm
- Thursday: 9am - 11pm
- Friday: 9am - 11pm
- Saturday: 9am - 11pm

This is how my mysql table looks like.

    CREATE TABLE business_hours (
      id integer NOT NULL PRIMARY KEY,
      restaurant_id integer NOT NULL,
      day integer NOT NULL,
      open_time time,
      close_time time
    )

I am not sure How to do this in php. so can anybody pointed my to right direction here?

Any idea would be greatly appreciated.
Thank you.

[Ch0cu3r]

You don't want to store the openning hours as am/pm. You should store the times in 24 hour format (HH:MM:SS), eg 09:00:00 and 23:00:00

 

You can convert the hours to 9am and 11pm using PHP's date function or within the query using MySQL's date/time_format function when you get the hours from the database.

[thara]

@Ch0cu3r, Thank you for your answer. But My question is I am getting two days from users, like Monday, Saturday with opening and closing time. So Now I need to insert into mysql all the records between this two days.

Eg:
Monday,9am,10pm
Tuesday,9am,10pm
Wednesday, 9am, 10am and until Saturday (To user secondly selected date)

[Barand]

This basically boils down to the design of your input form. There will no doubt be a mix of human interpretation of the information given by restauranteurs and coding logic. How much of each will be up to you. However you do it, you need to end up with something like

+---------------+------+----------+------------+
| restaurant_id | day  |  open    |  close     |
+---------------+------+----------+------------+
|               |      |          |            | Mon closed all day (no record)
|     1         |   2  | 11:00:00 | 21:00:00   |
|     1         |   3  | 11:00:00 | 21:00:00   |
|     1         |   4  | 11:00:00 | 21:00:00   |
|     1         |   5  | 17:00:00 | 23:30:00   |
|     1         |   6  | 11:00:00 | 14:30:00   | open midday, closed 14:30 - 19:00
|     1         |   6  | 19:00:00 | 25:30:00   | open late Note 25:00 = 1am on next day
|     1         |   7  | 19:00:00 | 22:30:00   |
+---------------+------+----------+------------+

Note this allows for

• Days when the restaurant is not open
• Days when it closes during part of the day then reopens
• Is open past midnight

I used to do a lot of timetable work for the transport industry and all-night buses would begin around 11pm and finish around 7am. For data consistency of always finishing after it started, and maintaining correct arithmetic of duty lengths, we adopted a 32 hour clock, so 7am on a night service was 31:00:00. You may need a similar device for late-closing restaurants.

[thara]

@Barand, Thank you for your info. Here I have attached an image to see you how I going to use these date in my website.

 

http://www.tiikoni.com/tis/view/?id=570f668

 

And this is how I get these info from users

http://www.tiikoni.com/tis/view/?id=3d00416

 

 

So, can you tell me what is the best way to this?

 

Thank you.

[Barand]

Your second image above is fine for those opening on the same hours everyday. You may be lucky and get away with it, but Sod's Law dictates that someone will come along with a non-uniform pattern as in the example I gave before. When this happens you need to able to handle it.

[thara]

Yes I understand what you have said. Actually I am assuming opening hours is same for everyday. If I use different hours I may use different way to do this, but at this time I need to do this by assuming opening hours is same. 

 

If its so, can you tell me how can I make the Insert query for this?

 

Thank you.

[Ch0cu3r]

Sorry, had to pop out earlier. Would of replied sooner.

 

You would need to loop over your day indexes (1 through to 7, which I guess means Monday through to Sunday?) and setting the open/closing times for each row. Example code

$resturant_id = ...; // restaurant id value

// converts the user entered times into 24 hour time, eg 9am gets converted to 09:00
$open_time = date('H', strtotime($_POST['open_time']));
$close_time = date('H', strtotime($_POST['close_time']));

// loop over dayindex, and set the open and close time
$values = array();
for($dayInex = 1; $dayIndex <= 7; $dayIndex++)
{
   // sets values for each day index
   $values = "($restaurant_id, $dayIndex, '$open_time', '$close_time')";
}

// insert open/closing times foreach day index using one insert query
$sql = 'INSERT INTO business_hours VALUES ' . implode(', ', $values);

Problem is if there is a particular day that is closed, your form makes it hard for the code to know what day that is. Currently it will set the open/close time for every day.

 

This is why your need to either change your form so either each day has its own open/close time value or change your to/from dropdown menu to be either check boxes or make it a multiple select menu.

 

The code can then loop over the days the user has chosen and insert the open/close times for those days only.

[Barand]

IMHO something like this would give you the flexibility if you interpret the blanks as defaulting to first set of times given.

function daysandtimes()
{
    $out = '';
    $timeOptions = timeOptions();
    $days = [1=>'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
    foreach ($days as $dno => $dname) {
        $out .= <<<OUT
        <tr>
        <td>$dname</td>
        <td><select name='openclose[$dno][1]'>$timeOptions</select></td>
        <td><select name='openclose[$dno][2]'>$timeOptions</select></td>
        <td><select name='openclose[$dno][3]'>$timeOptions</select></td>
        <td><select name='openclose[$dno][4]'>$timeOptions</select></td>
        </tr>
OUT;
    }
    return $out;
}

function timeOptions()
{
    $opts = "<option value=''> </option>\n
             <option value='-1'>Closed</option>\n";
    $dt1 = new DateTime('06:00');
    $di = new DateInterval('PT30M');
    $dp = new DatePeriod($dt1, $di, 42);
    foreach ($dp as $d) {
        $v = $d->format('H:i');
        $t = $d->format('g:i a');
        $opts .= "<option value='$v'> $t</option>\n";
    }
    return $opts;
}
?>
<html>
<head>
<title>Example</title>
<style type='text/css'>
body, td, th {
    font-family: sans-serif;
    font-size: 10pt;
}
legend {
    background-color: black;
    color: white;
    padding: 2px;
}
</style>
</head>
<body>
<form>
    <fieldset>
    <legend>Operating Hours</legend>
    <table>
    <tr><th>Day</th><th>Open</th><th>Close</th><th>Open</th><th>Close</th></tr>
    <?=daysandtimes()?>
    </table>
    </fieldset>
    <input type='submit' name='btnSub' value='Submit'>
</form>
</body>
</html>

[thara]

@Barand, your solution is very flexibility. Great idea. Thank you very much.

 

Can you kindly tell me how to process this HTML form and how to make INSERT query?

 

Do I need to change my database structure?

 

Thank you.

[Barand]

Your table is OK.

 

When the data is submitted the $_POST[openclose] array will look like this

 

 

    [openclose] => Array
        (
            [1] => Array
                (
                    [1] => -1
                    [2] => 
                    [3] => 
                    [4] => 
                )

            [2] => Array
                (
                    [1] => 09:00
                    [2] => 22:30
                    [3] => 
                    [4] => 
                )

            [3] => Array
                (
                    [1] => 
                    [2] => 
                    [3] => 
                    [4] => 
                )

            [4] => Array
                (
                    [1] => 
                    [2] => 
                    [3] => 
                    [4] => 
                )

            [5] => Array
                (
                    [1] => 
                    [2] => 
                    [3] => 
                    [4] => 
                )

            [6] => Array
                (
                    [1] => 10:00
                    [2] => 14:00
                    [3] => 19:00
                    [4] => 01:00
                )

            [7] => Array
                (
                    [1] => 19:00
                    [2] => 22:30
                    [3] => 
                    [4] => 
                )

 

 

i.e. arrays of four times for each day of the week. Loop through the days using this logic

foreach day
    if "closed" (-1) 
        continue
    endif
    if array is empty
        set day to default values
    else
        store times as default
    endif
    insert record for times 1 and 2
    if times 3 and 4 not empty
        insert record for times 3 and 4
    endif
endforeach

Edited July 15, 2015 by Barand

[thara]

@Barand, I went on your logic. But I am confusing how to figure this out.

 

I tried it something like this.

    foreach ( $_POST['openclose'] as $day => $time) {
        //echo "$day and $time<br>";
        foreach ($time as $open => $close) {
            echo "$day - $open, $close<br>";
            
            if($open == '-1') {
            
            }
        }
    }

But, I am not sure how to create insert query for this and don't know how to insert "closed" day into mysql...

 

Hope you may help me out.

 

Thank you.

[Barand]

I wouldn't bother storing "closed" days but, if you want to, you could store both times as "00:00:00" so the code would be

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $restaurant_id = $_POST['rid'];
    $def_times = [];  // default
    $sql = "INSERT INTO business_hours (restaurant_id,day,open_time,close_time)
            VALUES (?,?,?,?)";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('iiss', $restaurant_id, $dayno, $ot, $ct);
    
    foreach ($_POST['openclose'] as $dayno => $times) {
        if ($times[1]==-1) { // closed
            $times[1] = $times[2] = '00:00:00';
        }
        elseif (empty(array_filter($times))) {
            $times = $def_times;  // set the times to the stored defaults
        }
        else {
            $def_times = $times;  // save the times as the default times
        }
        $ot = $times[1];
        $ct = $times[2];
        $stmt->execute();
        if ($times[3]!='') {
            $ot = $times[3];
            $ct = $times[4];
            $stmt->execute();
        }
    }
}

I leave the validation to you.

[thara]

@Barand, I can get a fetal error when I using above code.

 

This is my updated code:

if (isset($_POST['next-step'])) {
    
    $restaurantId = 2;
    $def_times = array('06:30:00', '10:30:00');  // default
    
    $sql = "INSERT INTO business_hours (  restaurant_id
                                                                            , day
                                                                            , open_time
                                                                            , close_time
                                                                         )
                                                                         VALUES (?,?,?,?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('iiss', $restaurantId, $dayNo, $openTime, $closeTime);
    
    foreach ($_POST['openclose'] as $dayNo => $times) {
        $dayNo = (int)$dayNo;
        
        if ($times[1]==-1) { // closed
                $times[1] = $times[2] = '00:00:00';
        }
        elseif (empty(array_filter($times))) {
            $times = $def_times;  // set the times to the stored defaults
        }
        else {
            $def_times = $times;  // save the times as the default times
        }
        $openTime = $times[1];
        $closeTime = $times[2];
        $stmt->execute();
        if ($times[3]!='') {
                $openTime  = $times[3];
                $closeTime = $times[4];
                $stmt->execute();
        }
    }
}

This is the error message:

Fatal error: Can't use function return value in write context in C:\wamp\www\restaurants\modules\opearating_hours.inc.php on line 24

 

line 24 is

$times = $def_times;  // set the times to the stored defaults

[Barand]

Are you using an old version of php that doesn't support empty(function())?

 

Try changing

elseif (empty(array_filter($times))) {

to

elseif (!array_filter($times)) {

[thara]

my php version is 5.3.10.

 

Problem was solved using second method you have provided.

 

Now I had another issue when I trying to use default time.

 

I set default time like this.

$def_times = array('06:00:00', '11:30:00');  // default

Then I can get this message:

An error occurred in script 'C:\wamp\www\restaurants\modules\opearating_hours.inc.php' on line 30: Undefined offset: 2

 

Line 30 is :

$closeTime = $times[2];

[thara]

I fixed it.

 

changed def-time() array to:

$def_times = array(1 => '06:00:00', '12:30:00');  // default

if (isset($times[3]) && $times[3]!='') {
                $openTime  = $times[3];
                $closeTime = $times[4];
                $stmt->execute();
        }

[thara]

@Barand, Can you tell me how to create select query for this. To have a output something similar to this : http://www.tiikoni.com/tis/view/?id=570f668

 

If there are two shifts how to display it in my page?

 

Thank you.

[Barand]

The query would be

SELECT day
, TIME_FORMAT(open_time, '%k:%i%p') as otime
, TIME_FORMAT(close_time, '%k:%i%p') as ctime
FROM business_hours
WHERE restaurant_id = ?
ORDER BY day, open_time

[thara]

@Barand, I got another problem when I try to update this "Business Operating Hours". Here I need to display these dropdowns in editing page with the existing values. If I use normal dropdown I can set the "selected" attribute for the chosen option/s. But here all 28 dropdowns populating with the use of daysandtimes() and timeOptions() functions. Thats the problem I have.

 

Actually I am not sure how to figure this out. Any help would be greatly appreciated.

 

Thank you.

[Barand]

I have rewritten the two functions so that you now pass an array of opening times for the restaurant to daysandtimes() function.

function daysandtimes(&$times)
{
    $out = '';
    $days = [1=>'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
    foreach ($days as $dno => $dname) {
        $hours = array_merge($times[$dno], array('','','','')); // ensure > 4 array elements
        $menu1 = timeOptions($hours[0]);
        $menu2 = timeOptions($hours[1]);
        $menu3 = timeOptions($hours[2]);
        $menu4 = timeOptions($hours[3]);
        $out .= <<<OUT
        <tr>
        <td>$dname</td>
        <td><select name='openclose[$dno][1]'>$menu1</select></td>
        <td><select name='openclose[$dno][2]'>$menu2</select></td>
        <td><select name='openclose[$dno][3]'>$menu3</select></td>
        <td><select name='openclose[$dno][4]'>$menu4</select></td>
        </tr>
OUT;
    }
    return $out;
}

function timeOptions($current)
{
    $selClosed = $current==-1 ? 'selected="selected"':'';
    $opts = "<option value=''> </option>\n
             <option $selClosed value='-1'>Closed</option>\n";
    $dt1 = new DateTime('06:00');
    $di = new DateInterval('PT30M');
    $dp = new DatePeriod($dt1, $di, 42);
    foreach ($dp as $d) {
        $v = $d->format('H:i:00');
        $t = $d->format('g:i a');
        $sel = ($v==$current) ? 'selected="selected"':'';
        $opts .= "<option $sel value='$v'> $t</option>\n";
    }
    return $opts;
}

To use it now, get the times from the database and store in an array, like this

$rid = intval($_GET['rid']);  // get the restaurant id
$sql = "SELECT day
        , open_time
        , close_time
        FROM business_hours
        WHERE restaurant_id = $rid
        ORDER BY day, open_time";
// create array of time for each day
$times = array();
$res = $db->query($sql);
while (list($day, $ot, $ct) = $res->fetch_row()) {
    if ($ot=='00:00:00' && $ct=='00:00:00') {
        $times[$day][0] = -1;
    }
    else {
        $times[$day][] = $ot;
        $times[$day][] = $ct;
    }
}

Then pass the array to the function to output the menus

<table>
<?=daysandtimes($times)?>
</table>

[thara]

@Barand, I used above modified functions to display existing business hours to a restaurant in my business hour update page. Its nicely working that mean all the dropdowns populating correctly with available values.

 

Then I tried to update "business hours" table, deleting existing values and inserting new values. Then I can get an error message inserting is not working but delete query is working.

 

This is how I tried it:

// Check for a form submission:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    //echo '<pre>', print_r($_POST).'</pre>';
    //echo '<pre>', print_r($_SESSION['errors']).'</pre>';

  // Sanitize and validate the data passed in form:
    
    // Check for the "Operating Hour selection":    
    if (isset($_POST['openclose'])) {    
        $operatingHours = $_POST['openclose'];         
    } else {
        $error_alert[] = "Please select opening and closing time for seven day.";
    }    
    
    if (empty($error_alert)) { // If everything's OK...
        // Delete old entries:
        $sqlDelete = 'DELETE FROM business_hours WHERE restaurant_id = ?';
        $stmtDelete = $mysqli->prepare($sqlDelete);
        $stmtDelete->bind_param('i', $restaurant_id);
        $stmtDelete->execute();
        $stmtDelete->close();
        unset($stmtDelete);  
        
        // Insert restaurant's operating hours into database:
        $def_times = array(1 => '06:00:00', '11:30:00');  // default
        $sql = "INSERT INTO business_hours (restaurant_id, day, open_time, close_time)
                        VALUES (?,?,?,?)";
        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param('iiss', $restaurant_id, $dayno, $open_time, $close_time);
        
        foreach ($operatingHours as $dayno => $times) {
            if ($times[1]==-1) { // closed
                $times[1] = $times[2] = '00:00:00';
            }
            elseif (!array_filter($times)) {
                $times = $def_times;  // set the times to the stored defaults
            }
            else {
                $def_times = $times;  // save the times as the default times
            }
            $open_time  = $times[1];
            $close_time = $times[2];
            $stmt->execute();
            if (isset($times[3]) && $times[3]!='') {
                    $open_time  = $times[3];
                    $close_time = $times[4];
                    $stmt->execute();
            }
        }
    }        
} // main IF condistion -- Form Submission --  

This is the error I am getting when running above script:

An error occurred in script 'C:\wamp\www\restaurant\includes\process_edit_operating_hours.php' on line 70: array_merge() [function.array-merge]: Argument #1 is not an array

 

 

Line number 70 is.            

$hours = array_merge($times[$dno], array('','','','')); // ensure > 4 array elements

in daysandtimes(&$times) function.

 

What would be the problem?

 

 

 

[thara]

Again I tried it. After inserting I set to reload the page. Then it fixed the error.

 

I added this code after insert query:

        if ($stmt->affected_rows >= 1) {
            // Success msg:
            $_SESSION['success'] = "Restaurant Operating Hours Updated successfuly.";
            // Redirect user
            $url = BASE_URL.BASE_URI."index.php?p=edit-operating-hours";        
            ob_end_clean(); // Delete the buffer.    
            // Define the URL.
            header("Location: $url");
            exit(); // Quit the script.    
        }