Help with MYSQL query...

[ncognito66]

Hello!

 

What I'm working on is a programming schedule for a local radio station. Brain dead people need to be able to fill in a form to add shows, and most shows will repeat on a weekly basis, not just on one certain date, which is where I'm having problems with the Date & Time.

 

I need to pull up a full weekly schedule, a listing for today only, and then an "Currently On The Air" spot for just the current time period (divided into 15 minute timeslots, but some go for 30 minutes, some for 1 hour).

 

The info is inputted into the database in TEXT format as follows:

 

Title

Showday <--Day of week show airs

Timeslot <--Time of Day show airs

AMPM <--Morning or Afternoon

and others...

 

I realize I should use proper date/time functions, but I was unsure how to do it, so I did it this way. I actually have this working as follows:

 

putenv("TZ=US/Eastern"); //Set Eastern Timezone

$today = date(l); // Determines Day of Week

$meridian = date(A); //Determines AM or PM

$time = date("g:00"); //Determines Hour

 

$result = @mysql_query("SELECT * FROM `shows` WHERE 1 AND Showday = '$today' AND Timeslot = '$time' AND AMPM = '$meridian'");

 

My problem is 12:00 Noon comes after 11:00 PM in the listings, so I guess I should start from scratch with proper datetime fields huh?

 

How do I combine 3 fields (Day of Week, Show Time, and AM/PM) in to one proper field?

 

Then how do I get the shows to show up EVERY Monday, Tuesday, etc..,not just on one certain date?

 

If a show goes for a hour, how do I get it to span the appropriate 15 minute timeslots?

 

How do I set a default when no programming is listed?

 

I appreciate any and all help as I am just a volunteer at our local, non-profit, community radio station and learning as I go!

 

Take care!!

 

Alan

[email protected]

 

[rameshfaj]

Why dont you use single date/time field to get the schedule?This doesnot require you to get the fields separately.You can use now() function calls.Try with this one.

[ncognito66]

Why dont you use single date/time field to get the schedule?This doesnot require you to get the fields separately.You can use now() function calls.Try with this one.

 

How do I take 3 separate inputs from the entry form (Mondays, 12:00, PM) and combine them into one date/time field?

 

And that will enter a specific date correct? I need to pull it up EVERY Monday.

 

I'm a real newbie, so I need lots of explaining! 

 

Alan

ncognito66#comcast.net