Displaying records from a MS Access database based on date.

dcdomain posted this at 23:39 — 3rd November 2001.

Joined: Oct 2001

I have events that I enter into a database, and along with these events, I have a field for when it takes place (date, time).

The view page that pulls these records out of the database has three categories. Upcoming events (today < event date < 7 days from today), future events (takes place in > than 7 days from today) and past events (took place < than today). I guess if I included the time into this equation it would be more of a mess, so for now I can just live with it being sorted by dates only.

So for now, I'm basically using similar code that I've used for my guestbook views page:

Quote:
<?php SQL="SELECT date, time, event, information FROM schedule ORDER BY date, time DESC" set conn = server.createobject("adodb.connection") conn.open("DBQ=" & server.mappath("database.mdb") & ";DRIVER={Microsoft Access Driver (*.mdb)};") set rs=conn.execute(SQL) ?>

And then in the body, I would use the

Quote:
<?php do while not rs.eof ?>
and
<?php rs.MoveNext loop ?>
tags

to go through the database and grab all the records.

So would I be using the above code at all? I mean, there would be multiple events falling within the date ranges of the three parts. How would I code it so that each section grabs the correct interval of records?

Thanks for any tutorials/links/code you guys can provide... =D

DC Domain r5

"What I had, I gave today.
What I saved, I lost forever."

Peter J. Boettcher posted this at 13:56 — 5th November 2001.

They have: 812 posts

Joined: Feb 2000

You could probably go ahead and use that same query. Then just use DateDiff inside you loop to build the different even sections.

Example:

<?php
 do while not rs.eof 
?>

<?php
 If DateDiff("d", Now, rs("Date")) > 7 Then
strEventTitle = "Future events" 
?>

<?php
 If strEventHolder <> strEventTitle Then Response.Write strEventTitle & "<br>"
?>

<?php
= rs("event") 'display stuff here 
?>

<?php
 ElseIf DateDiff("d", Now, rs("Date")) <= 7 Then
strEventTitle = "Upcoming Events 
?>

<?php
 If strEventHolder <> strEventTitle Then Response.Write strEventTitle & "<br>"
?>

<?php
= rs("event") 'display stuff here 
?>

<?php
 Else 
strEventTitle = "Past Events 
?>

<?php
 If strEventHolder <> strEventTitle Then Response.Write strEventTitle & "<br>"
?>

<?php
= rs("event") 'display stuff here 
?>

<?php
 End If 
?>

<?php
 strEventHolder = strEventTitle 
?>

<?php
rs.MoveNext loop 
?>

Because your query is sorted by date desc this should work.

PJ | Are we there yet?
pjboettcher.com