Schedule Problem

[buzzt]

I am displaying an events schedule with PHP querying my MySQL database. I need to be able to get the WHAT'S ON NOW to display the current event. I have been doing it this way:

// Second part of query for next day events starting at midnight to follow last current day's event
$query = mysql_query("select * from TABLE where (DATECOL = curdate() and STARTCOL >= curtime()) or (DATECOL = (curdate()+1) and STARTCOL >= curtime()) order by date,start limit 1") or die (mysql_error());

$num_results = mysql_num_rows($query);
if (!$num_results)
{
echo "Schedule to be updated soon.";
}
for ($i=0; $i<$num_results; $i++)
{
$row = mysql_fetch_array($query);
$id = $row['EVENTID'];
$id2 = $id-1;
$query = mysql_query("select * from TABLE where id='$id2'");
... And so on...

The problem is that by relying on auto increment id numbers, I can never insert an event between two other events.

Does anyone have a better way of doing this?

 

[sleipnir214]

he problem is that by relying on auto increment id numbers, I can never insert an event between two other events.

I don't understand. When talking about daa in a relational database, the concept of a piece of data being "between" two others only exists in the context of the arbitrary order imposed by the WHERE clause of a SELECT query.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[buzzt]

Huh? I'm still fairly new to this... What do you mean?

 

[buzzt]

The way I'm doing this is, if I have an event every hour on the hour (not necessarily so) and then want to insert an event on the half-hour somwhere that day, by using the auto incremnet id and the method I've chosen with my existing code (to get the current event - find the next event and subtract 1 from its id) the new event (on the half-hour) would not display.

Make sense? This is why my code is not too good.

 

[sleipnir214]

Sorry. Scratch "WHERE", and replace "ORDER BY" in my previous post.


Relational databases are based on set theory. A set is an unordered collection of objects with similar properties.

Suppose you have a table which consists of an auto_increment integer, a string, and a date.

If I issue the query "SELECT * FROM foo ORDER BY pk_id", I get:
[tt]+-------+----------+---------------------+
| pk_id | a_string | a_date |
+-------+----------+---------------------+
| 1 | e | 2004-02-02 02:02:02 |
| 2 | a | 2004-05-05 05:05:05 |
| 3 | d | 2004-01-01 01:01:01 |
| 4 | b | 2004-03-03 03:03:03 |
| 5 | c | 2004-04-04 04:04:04 |
+-------+----------+---------------------+
[/tt]

The string "d" is between "a" and "b".

If I issue the query "SELECT * FROM foo ORDER BY a_string", I get:

[tt]+-------+----------+---------------------+
| pk_id | a_string | a_date |
+-------+----------+---------------------+
| 2 | a | 2004-05-05 05:05:05 |
| 4 | b | 2004-03-03 03:03:03 |
| 5 | c | 2004-04-04 04:04:04 |
| 3 | d | 2004-01-01 01:01:01 |
| 1 | e | 2004-02-02 02:02:02 |
+-------+----------+---------------------+
[/tt]

"c" is now between "b" and "d".

If I issue the query "SELECT * FROM foo ORDER BY a_date", I get:
[tt]+-------+----------+---------------------+
| pk_id | a_string | a_date |
+-------+----------+---------------------+
| 3 | d | 2004-01-01 01:01:01 |
| 1 | e | 2004-02-02 02:02:02 |
| 4 | b | 2004-03-03 03:03:03 |
| 5 | c | 2004-04-04 04:04:04 |
| 2 | a | 2004-05-05 05:05:05 |
+-------+----------+---------------------+
[/tt]

"b" is now between "e" and "c".

"Between" is an arbitrary designation, determined by how you fetch the data. You can add the data in any arbitrary order -- it's the "ORDER BY" clause that determines what's between what.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[buzzt]

I understand that. I don't think that you understand my problem though. I am trying to find a better way to display the event going on at THIS TIME. The problem I have, is that I can only track that event down by going to the next event, and subtracting 1 (from the id). Basically, I am relying on the ID numbers to find my event that's going on NOW.

Now if I add and event after I have already added all the events for that day, the id will be greater that the last one for that day. For example, here is my table...

id startdate starttime eventname
1 2004-03-01 08:00:00 Event 1
2 2004-03-01 09:00:00 Event 2
3 2004-03-01 10:00:00 Event 3
4 2004-03-01 11:00:00 Event 4

If it is currently 09:20:00 and I want to display what's going now, I need to use starttime>=curtime() limit 1 to get id 2. I then need to subtract 1 from the id (with my existing code), to finally get the event that's currently going on. Make sense?

You can then see my dilemma. If I add an event scheduled for 09:30:00, it will be assigned id 5. If I then check the events at 09:40:00, the code will subtract 1 from 5(the id of the 09:30:00 entry) and display the item with id 4.

id startdate starttime eventname
1 2004-03-01 08:00:00 Event 1
2 2004-03-01 09:00:00 Event 2
3 2004-03-01 10:00:00 Event 3
4 2004-03-01 11:00:00 Event 4
5 2004-03-01 09:30:00 NEW EVENT

 

[sleipnir214]

I wouldn't recommend using the IDs to traverse the record even if out-of-order entries weren't a problem.

Instead, find that record which has the greatest starttime that is less than the current time.

The query:
SELECT * FROM foo WHERE starttime <= now() ORDER BY starttime DESC LIMIT 1

will always return the record for that event which was the last to start before the current time.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[buzzt]

Perfect! Thanks!