Need tips for querying DB for a calendar

Hatch

I'm creating a calendar. I am also going to be displaying the names of guides that will be available on those days of the month that the user selects.

What I am thinking is to query the database for every day of the month to see what guides are available for that day. This sounds like alot of querying though and was wondering if anyone has run into something similiar and found a better way to do it.

TheDefender

Hey, is this the same Hatch that I know from the Adobe XChange boards?!?

OK, OK, I will stop digressing now...

A couple of questions for you.
1) How are you showing the dates to begin with? Is this a monthly calendar, weekly, etc.?

Unless you are dealing with thousands and thousands and thousands of records, the looping query won't be as bad as you think... especially if you index your data properly anyhow.

One way I did things on a calendar script I wrote was to just count the number of events for a day, and reporting that (if it was greater than 0, and then when the day is selected, the events are shown. (You still get a lot of queries, but all you are doing is counting them based on the date, and with the date field indexed, it didn't slow down at all.)

You can simplify your code by looping through the days too, so you don't have to hand write the queries for each day (but I am sure you knew that)

Anyhow, good luck. Post some code, so we can take a looksie.

Hatch

First, no I don't think I'm that Hatch. I don't ever visit there.

I am showing the dates by month. Just like a hanging calendar on a wall. So I'll be doing around 30 queries (one for each day).

I've had experience using SQL and am comfortable with it, but I don't think I have indexed anything. I've heard of indexing but I've never needed to learn how (until now maybe).

My calendar does use loops to go through the days and such. It's kinda messy but it works.

I guess I could see how many rows get returned for each day first and if there are rows then do another query to grab those guides, but then I'm doing two queries instead of one. I might as well just do the query and then if there are rows then show them.

Let me know what you think.

TheDefender

You don't have to run a second query...

All you have to do is compare the date listed for that guide to the currently displaying date and run an if/then to display the guides...

<?
// START LOOP CODE TO CREATE THE CALENDAR


// Then Nest something like this within the loop so it runs for each date
IF($thisdate == $guidedate):  // $guidedate is build during the actual loop
  echo "Guide Info Here";
ENDIF;

// END LOOP CODE

?>

Anyhow, it's kinda tough to be more specific unless you post some code.