php - Find is a business is open: MySQL hours calculation -

i have list of business stored in locations table, , stored in table hours business opens , closes:

location `mon_1_open` `mon_1_closed` `tue_1_open` `tue_1_closed` `wed_1_open` `wed_1_closed` 

ect...

i store times in full hours , minutes, business open 9:00am 5:30pm on monday.. mon_1_open = '900' , mon_1_closed = '1730'.

i can't seem figure out way find day of week , output if business else open or closed based on time of day.

any suggestions?

this not answer question, may in long run.

your database scheme seems flawed. not normalized. address before becomes big issue, have noticed makes hard locate businesses hours. here draft scheme might better suiting.

table: locations     id int auto_increment primary key     name varchar(50)   table: location_hours     id int auto_increment primary key     location_id int  -  foreign key references locations table     day char(3) - (examples: mon, tue, wed, thu, fri, sat, sun)     hours varchar(4) - (could int) 

then todays date, can done in mysql date_format %a, example query:

select locations.name, location_hours.hours  locations      join location_hours on locations.id = location_hours.location_id  location_hours.day = date_format(now(), '%a')      , location.name = 'someway business' order location_hours.hour 

you should not need open / close given the order by knows 0900 < 1430 since varchar (although int should know how sort well), code when adding businesses either need update record or need field active signify if row should used in query. remember use 24 hour time. again mock up, created on spot use improvements, better doing hack have current code.

update

addressing comment finding if open or close:

just use php date function , call date('hi') pull out current time in 24-hour time, simple if statement see if between that, if is, opened.

ie:

$sql = "select locations.name, location_hours.hours  locations      join location_hours on locations.id = location_hours.location_id  location_hours.day = date_format(now(), '%a')      , location.name = 'someway business' order location_hours.hour";  $result = mysql_query($sql) or trigger_error("sql failed error: " . mysql_error());  $times = array(); while ($row = mysql_fetch_assoc($result)) {     if (empty($times['open'])) {         $times['open'] = $row['hours'];     }else {         $times['closed'] = $row['hours'];     } }  $currenttime = date('hi');  if ($times['open'] <= $currenttime          && $times['closed'] > $currenttime) {     echo "open"; }else {     echo "closed"; } 

given logic correct. again, pseudo code example of usage. given wrote on spot. above assumes querying 1 business @ time.