Help with Database Query...
 

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

Re: Help with Database Query...
 

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)

Re: Help with Database Query...
 

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>

Re: Help with Database Query...
 

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

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

Re: Help with Database Query...
 

Hello,

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

krs,
tesu