MS SQL

Help with Database Query...

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2008

Posts: 4

Reputation: ncognito66 is an unknown quantity at this point

Solved Threads: 0

ncognito66 ncognito66 is offline

Offline

Newbie Poster

Help with Database Query...

Jul 17th, 2008

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

Last edited by ncognito66; Jul 17th, 2008 at 1:14 am.

•

Join Date: Apr 2006

Posts: 143

Reputation: cutepinkbunnies is an unknown quantity at this point

Solved Threads: 8

cutepinkbunnies cutepinkbunnies is offline

Offline

Junior Poster

Re: Help with Database Query...

Jul 22nd, 2008

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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)
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)

Last edited by peter_budo; Jul 23rd, 2008 at 8:25 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Jul 2008

Posts: 4

Reputation: ncognito66 is an unknown quantity at this point

Solved Threads: 0

ncognito66 ncognito66 is offline

Offline

Newbie Poster

Re: Help with Database Query...

Jul 22nd, 2008

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
<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>
<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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
$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'";
$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>

Last edited by peter_budo; Jul 23rd, 2008 at 8:27 pm. Reason: Keep It On Site - Do not post asking for an answer to be sent to you via email or PM.

•

Join Date: Apr 2006

Posts: 143

Reputation: cutepinkbunnies is an unknown quantity at this point

Solved Threads: 8

cutepinkbunnies cutepinkbunnies is offline

Offline

Junior Poster

Re: Help with Database Query...

Jul 23rd, 2008

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

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

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: Help with Database Query...

Jul 25th, 2008

Hello,

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

krs,
tesu

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.