Help with Database Query...

[ncognito66]

Sorry, I posted this in the wrong area (I'm using MySQL)

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

[cutepinkbunnies]

I'd suggest using a view or a stored procedure. The syntax would be something like the following.

SELECT Dayofweek, MAX(Timeslot) as 'Timeslot', MAX(AM/PM) as 'AM/PM', MAX(Description) as 'Description', MAX(Name) as 'Name', MAX(BIO) as 'Bio', MAX(Photo) as 'Photo'
FROM <TABLENAME>
GROUP BY Dayofweek
ORDER BY Dayofweek, MAX(Timeslot)

[ncognito66]

Thank you for your help, but I have other problems I guess.

Here's the form I'm using to input my data to the database:

<form action="<?=$PHP_SELF?>" method="post">
<br>
<b>Show Title</b>
<br><input type="text" name="Title" size="50">
<p><b>Day of Week</b>
<br><input type="radio" name="Showday" value="Monday" checked> Monday&nbsp;
<input type="radio" name="Showday" value="Tuesday"> Tuesday&nbsp;
<input type="radio" name="Showday" value="Wednesday"> Wednesday&nbsp;
<input type="radio" name="Showday" value="Thursday"> Thursday&nbsp;
<input type="radio" name="Showday" value="Friday"> Friday&nbsp;
<input type="radio" name="Showday" value="Saturday"> Saturday&nbsp;
<input type="radio" name="Showday" value="Sunday"> Sunday
<p><b>Show Date (2008-07-05)</b>
<br><input type="text" name="Showdate" value="0000-00-00" size="50">
<p><b>Show Time</b>
<br><SELECT NAME="Timeslot">
<option>01:00
<option>01:15
<option>01:30
<option>01:45
<option>02:00
<option>02:15
<option>02:30
<option>02:45
<option>03:00
<option>03:15
<option>03:30
<option>03:45
<option>04:00
<option>04:15
<option>04:30
<option>04:45
<option>05:00
<option>05:15
<option>05:30
<option>05:45
<option>06:00
<option>06:15
<option>06:30
<option>06:45
<option>07:00
<option>07:15
<option>07:30
<option>07:45
<option>08:00
<option>08:15
<option>08:30
<option>08:45
<option>09:00
<option>09:15
<option>09:30
<option>09:45
<option>10:00
<option>10:15
<option>10:30
<option>10:45
<option>11:00
<option>11:15
<option>11:30
<option>11:45
<option>12:00
<option>12:15
<option>12:30
<option>12:45
</SELECT>
<p><b>AM/PM</b>
<br><SELECT NAME="AMPM">
<option>AM
<option>PM
</SELECT>
<p><b>Show Duration</b>
<br><input type="text" name="Duration" value="0:00" size="50">
<p><b>Show Topic (Brief few word description)</b>
<br><input type="text" name="Topic" size="50">
<p><b>Show Description (Full length description)</b>
<br><textarea wrap="virtual" cols="50" rows="12" name="Description"></textarea>
<p><b>Host Name</b>
<br><input type="text" name="Name" size="50">
<p><b>Host Bio</b>
<br><textarea wrap="virtual" cols="50" rows="12" name="Bio"></textarea>
<p><b>Host Photo Filename</b>
<br><input type="text" name="Photo" size="50">
<br>
<br>
<br><input type="submit" name="submitshow" value="Submit Show"></form>

I insert the data with the following:

$sql = "INSERT INTO shows SET 
             	Title='$Title',
		Timeslot='$Timeslot',
		AMPM='$AMPM',
		Showday='$Showday',
		Showinfo='$Showinfo',
		Showdate='$Showdate',
		Duration='$Duration',
		Topic='$Topic',
		Description='$Description',
		Name='$Name',
		Bio='$Bio',
		Photo='$Photo'";

Notice I have separate fields for Showday (Day of Week), Showdate (Actual Date), Showtime (Time of show),and AM/PM. I guess my first problem is:

How do I merge those fields into a Date/Time form that MYSQL will recognize?

I originally had all the columns as TEXT which worked except 12:00 Noon came after 11:00PM, so I changed the Showdate column to DATE and Timeslot to TIME, but I still have the AM/PM problem.

Once I get the info inputted into MYSQL in the proper format, then I need to pull up schedules by week, day, and "Currently on the Air". But I need to know what I'm doing wrong with my data input first.

Thank you so much!!

Alan
<EMAIL SNIPPED>

[cutepinkbunnies]

It looks like your SQL syntax is incorrect. Read up on INSERT syntax here.

Please keep us updated on your progress/trial & error.

[tesuji]

Hello,

you may have a look at DATE_FORMAT(date, format), especially specifier %p to convert date and time values.

krs,
tesu