Ad:

MS SQL Discussion Thread
Unsolved
Views: 1416

Jul 17th, 2008

Help with Database Query...

Expand Post »

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.

Similar Threads

SQL statement to query user input?? in MS Access and FileMaker Pro
Database Query Help in MS SQL
Error Executing Database Query on server in ColdFusion
Using PHP to connect to remote MSSQL database in PHP
conditional database query in ColdFusion

ncognito66

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jul 2008

Permalink

Jul 22nd, 2008

Re: Help with Database Query...

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

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

cutepinkbunnies

Reputation Points: 15

Solved Threads: 9

Junior Poster

Offline

143 posts
since Apr 2006

Permalink

Jul 22nd, 2008

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:

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:

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.

ncognito66

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jul 2008

Permalink

Jul 23rd, 2008

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.

cutepinkbunnies

Reputation Points: 15

Solved Threads: 9

Junior Poster

Offline

143 posts
since Apr 2006

Permalink

Jul 25th, 2008

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

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Stored Procedure to reliably update two tables.

Next Thread in MS SQL Forum Timeline: Data Type mismatch!! :( Breaking my head on this for days!!!

DaniWeb Message

Cancel Changes

User Name
Password