Help with Database Query...

[ncognito66]

Hello all:

I'm currently building a website for a radio station and I want to setup a programming lineup. The fields I'm using are:

ID
Title
Dayofweek (Show Day)
Timeslot (Show Time)
AMPM (AM or PM)
Description
Name (Host Name)
Bio (Host Bio)
Photo (Host Photo)

I set these all up as TEXT as I wasn't sure how to do it correctly.

I want to be able to make a schedule listing the shows by Day of Week and then by Time, and I also want to make a box showing the current program on the air.

Any ideas for a newbie????

Thanks all!

Alan

[darkagn]

Couple of quick points:

ID fields should never be a text field. Use Unsigned INT or something similar. This helps efficiency and referential integrity of your tables.
Think about maybe consolidating the three columns Dayofweek, Timeslot and AMPM into one column, TimeOfDay, and making it a smalldatetime field, which is much nicer to work with than a straight text for dates and times.
Photo - you have a couple of choices, either this is a filename to the image of a photo, or better still make this a varbinary field and store the image's binary data directly in the database.

Ok, that said, for an SQL statement to list all shows by day and time, something like

SELECT * FROM TableName
ORDER BY TimeOfDay ASC

should do for a start. You can always refine this statement with a WHERE clause to limit to a specific date if you want:

SELECT * FROM TableName
WHERE TimeOfDay > '2008-07-17 00:00:00'
AND TimeOfDay < '2008-07-17 23:59:59'
ORDER BY TimeOfDay ASC

Hope this helps :)
darkagn

[ncognito66]

Couple of quick points:

ID fields should never be a text field. Use Unsigned INT or something similar. This helps efficiency and referential integrity of your tables.

Think about maybe consolidating the three columns Dayofweek, Timeslot and AMPM into one column, TimeOfDay, and making it a smalldatetime field, which is much nicer to work with than a straight text for dates and times.
Photo - you have a couple of choices, either this is a filename to the image of a photo, or better still make this a varbinary field and store the image's binary data directly in the database.

Ok, that said, for an SQL statement to list all shows by day and time, something like

SELECT * FROM TableName
ORDER BY TimeOfDay ASC

should do for a start. You can always refine this statement with a WHERE clause to limit to a specific date if you want:

SELECT * FROM TableName
WHERE TimeOfDay > '2008-07-17 00:00:00'
AND TimeOfDay < '2008-07-17 23:59:59'
ORDER BY TimeOfDay ASC

Hope this helps :)
darkagn

Yes, the ID field is an INT,auto-increment,but the rest are TEXT.

I was trying to make the form easy for the programming director to use, therefore the 3 different columns. I've never played with DATETIME items much, so I'm not sure how to combine them as you say.

What the query would have to do is check the current day of week and limit results to that day, then check current time and show the programming for the last timeslot. (Example: Monday at 2:17 PM would return listing for the program that started at 2:00PM on Mondays).

Photos I'm simply inputting file anem and manually uploaded to the server. I've never stored binary data as you say and unsure how to do it.

Thanks for your help!

Alan