Script doesn't recognize if open past 12:00 am

[ptara1]

I have a script that looks at a database of hours for stores. The table is set up by ID, Day, Hour Open, Hour Closed. The time is in military hours, 1pm = 13:00:00 hours. Currently it doesn't work correctly. Wondering if anyone could just give me a pointer on how to fix the script.

date_default_timezone_set('America/New_York');

include 'connect.php';

$day = date('l');
$time = date('H:i:s');

$result=mysql_query("SELECT storehours.ID, store.ID, store.Name FROM storehours, store WHERE Day = '$day' AND '$time' BETWEEN Open AND Close AND storehours.ID = store.ID ORDER BY Name");

Thanks for any help

[mschroeder]

What are your datatypes for Open and Close?

[ptara1]

What are your datatypes for Open and Close?

Variable Characters aka Varchar

[ptara1]

I messed around with the data type but that didn't help... Do I just need to add rows to the table? So for example:
If the store is open from 12:00:00 to 2:00:00 set up to rows..
One 12:00:00 to 23:59:59
and the other
00:00:01 to 2:00:00?

Is there an easier way to do this?

[diafol]

$result=mysql_query("SELECT storehours.ID, store.ID, store.Name FROM storehours, store WHERE Day = '$day' AND '$time' BETWEEN Open AND Close AND storehours.ID = store.ID ORDER BY Name");

Does sql pass?

AND '$time' BETWEEN Open AND Close

what about?

...AND `Open` <= '$time' AND `Close` >= '$time'...

[madCoder]

I took a closer look at the mysql Time datatype (http://dev.mysql.com/doc/refman/5.0/en/time.html) Time represents elapsed time, not time of day; so the hours part can exceed 24. You could represent "02:00 the following day" as '26:00' for your closing time. Also, the day of the week can be represented as a number date('w') .

That means you can still use BETWEEN in your query but you may need a second condition that tests for the closing time of the previous day: ...AND ADDTIME($time, '24:00') BETWEEN '24:00' AND YesterdayCloseTime

[ptara1]

Thanks for those two responses. I tried to see how they would work and they don't appear to help. What I've been thinking would work is if I entered a clause in the sql statement that went something like this:

SELECT blah blah blah WHERE ('$time' BETWEEN Open AND Close AND Day = '$day' OR '$time' BETWEEN 00:00:00 AND Close AND DAY='$day'-1) AND hours.ID = store.ID ORDER BY Name

This doesn't work..but I don't know why. I think this is a logical way to do it.

[fobos]

I could be wrong with the below statement in green.

date_default_timezone_set('America/New_York'); 
include 'connect.php'; 
$day = date('l');
$time = date('H:i:s'); 
// How to compare times if you do not include it in the select statement? You only have ID,ID,name.. No time or day is selected, so sql is only going to use those fields.
$result=mysql_query("SELECT storehours.ID, store.ID, store.Name FROM storehours, store WHERE Day = '$day' AND '$time' BETWEEN Open AND Close AND storehours.ID = store.ID ORDER BY Name");

Try

$query = "SELECT * FROM storehours, store WHERE day = '$day' AND hour open <= '$time' AND hour closed <= '$time'";
$result = mysql_query($query);

or

$query = "SELECT * FROM storehours, store WHERE day = '$day'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
if($time => $row['open'] && $time <= $row['closed']) {
    echo "Open from".$row['open'].", Closed at ".$row['close'];
} else {
    echo "not open yet";
}

[diafol]

SELECT blah blah blah WHERE ('$time' BETWEEN Open AND Close AND Day = '$day' OR '$time' BETWEEN 00:00:00 AND Close AND DAY='$day'-1) AND hours.ID = store.ID ORDER BY Name

Did you try running this in phpmyadmin? Bet it gave you an error.

[ptara1]

Yeah it does. I didn't want to write out all the selects sorry...Will try the above suggestions. Thanks again.

SELECT blah blah blah WHERE ('$time' BETWEEN Open AND Close AND Day = '$day' OR '$time' BETWEEN 00:00:00 AND Close AND DAY='$day'-1) AND hours.ID = store.ID ORDER BY Name

Did you try running this in phpmyadmin? Bet it gave you an error.