PHP+MySQL insert, depending on time

[Anon]

Hi

Perhaps someone out there has a solution to this problem. Here's the breakdown:

In my database, I have 4 records for 2001-12-06, and 4 different times for each, namely:
1) 10:00:00
2) 11:00:00
3) 14:00:00
4) 17:00:00

Here's the problem!!!
The time is now: 10:00:00. I do a select statement from the database, that checks to see if the current_time >= 'one_of_the_times_above'.

All 4 records will be selected.

However, i want record 1) to display until the current_time = 'the_11:00:00_timeslot', but at 10:01:00, record 1) no longer matches the current_time!!

What should my select statement be??
These are the one's i've come up with thus far:

1) select * from 'my_table' where active_date='current_date' and active_time >= 'current_time';

2) select * from 'my_table' where active_date = 'current_date' order by active_time desc;

3) oldtime = current_time + '60 minutes';
$sql = "select * from 'my_table' where active_date = 'current_date and active_time >= 'current_time' ";
$res = dbi_query( $sql );

if (mysql_num_rows($res) == 0 )
{
select * from 'my_table' where active_date <= current_date and active_time >= 'current_time' < 'oldtime';
}

Number 3 above, checks to see if there is a record to display, if not, revert to a new record, regardless of its a previous day or not, and that the time is now limited between the > 10:00:00 < 11:00:00

Hope this makes sense to someone out there. No-one in my department can help me, so be a pal and send some useful info my way. I've already been through the MySQL documentations, but came up with zip! The 'BETWEEN' function, did however, look promising...

A desperate plea for HELP!!
Tasneem

[[deleted]]

You problem is that the times-of-day in the database don't have a set interval.
So the only thing you can do is order them by time, and get the record thathas the 'earliest' time that is still later than the current time

something like

SELECT id, time
FROM table
WHERE time>current_time
ORDER BY time ASC.

But in a 'better' design, the records would not just have a time, they would have a start and end time, so you could just do

SELECT *
FROM table
WHERE current_time>start_time
AND current_time<end_time