How I store business hours in a mysql table

Question

I'm working on a website involving local restaurants, and one thing I need to do is store Restaurant Operating Hours in mysql.

Data comes from HTML to PHP is look like this.

// Eg: from Monday to Saturday 8am to 10pm
$_POST['from'], $_POST['to'], $_POST['opening_time'], $_POST['closing_time']

My sql table structure is something like this.

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
)

My question is I am getting two days from users like above with its opening and closing time. So Now I need to insert into business_hours table with all the records between this two days.

Eg: Monday, Friday (if the opening and closing period of a week)

Then I need to store these operating hours in my table like this

Monday,9am,11pm
Tuesday,9am,11pm
Wednesday,9am,11pm
Thursday,9am,11pm
Friday,9am,11pm

Can anybody tell me how can I do this?

Any idea would be greatly appreciated.

Thank you.

Answer 1

After much research, trial and error this is what finally worked for me. The data is all based on the entries of a restaurant for dynamic structured markup.

The SQL for creating the table:

CREATE TABLE tbl_businesshrs (
`id` int(11) NOT NULL,
`tbl_index_id` int(11) NOT NULL,
`DoW` varchar(128) NOT NULL,
`status` enum('Open','Closed') NOT NULL COMMENT '0 is Cosed',
`start_time` varchar(128) NOT NULL,
`end_time` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The tbl_index_id is the id of the restaurant in the restaurant table and it is an INDEX and DoW is day of the week.

I also included AJAX functions to update the business hours table when the hours are changed. Then on the restaurant setting php page I included a record source query for every day of the week that updates the select fields with the values stored in the database.

The query:

<?php 
$colname_rs_mondayshrs = "-1"; 
if (isset($_GET['restaurantid'])) { 
  $colname_rs_mondayshrs = $_GET['restaurantid']; 
} 
mysqli_select_db( $restaurant, $database_restaurant); 
$query_rs_mondayshrs = sprintf("SELECT * FROM tbl_businesshrs WHERE tbl_index_id = %s 
AND DoW='Monday'", GetSQLValueString($colname_rs_mondayshrs, "int")); 
$rs_mondayshrs = mysqli_query( $restaurant, $query_rs_mondayshrs) or 
die(mysqli_error($GLOBALS["___mysqli_ston"])); 
$row_rs_mondayshrs = mysqli_fetch_assoc($rs_mondayshrs); 
$totalRows_rs_mondayshrs = mysqli_num_rows($rs_mondayshrs);    

?> 

And so on for every day of the week. Then the magnificent PHP stcrcmp function compares the open and closed entries from the database and fills the select options with the relevant entry:

<select id="mondaystatus" onChange="update_mondaystatus()">
                      <option selected value="" <?php if (!(strcmp("", 
$row_rs_mondayshrs['status']))) {echo "selected=\"selected\"";} ?>>Open or Closed? 
</option>
                      <option value="Open" <?php if (!(strcmp("Open", 
$row_rs_mondayshrs['status']))) {echo "selected=\"selected\"";} ?>>Open</option>
                      <option value="Closed" <?php if (!(strcmp("Closed", 
$row_rs_mondayshrs['status']))) {echo "selected=\"selected\"";} ?>>Closed</option>
                    </select>

The result:

and the result in the db:

I can add the AJAX function if anyone is interested or if it could help save someone some valuable time.