Trouble forming MYSQL query properly

[jeffmc21]

I'm having trouble forming the query to my database properly to retrieve the info I'm needing. All I'm trying to get is a single result where the DAY column of the DB is the same as today's day name and the START column (which is a time field) is less than the current time.

Here's the code I'm using:

// Query the DB and retrieve the program information from the table


$result = mysql_query("SELECT * FROM `programming` WHERE DATE = DAYNAME() AND START < CURRENT_TIME() LIMIT 1");
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

while ($row = mysql_fetch_assoc($result)) {
echo '<p class="now"><strong>On Air Now:</strong> '.$row["NAME"].'<br />'.$row["HOST"].'</p>';
}


?>



And here's the error message I'm getting:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND START &lt; CURRENT_TIME() LIMIT 1' at line 1
Whole query:

[tomws]

You mention that you want to select form the "day" column, but you're not:
SELECT * FROM `programming` WHERE DATE = DAYNAME() AND START < CURRENT_TIME() LIMIT 1

DATE is also a data type in MySQL and may cause some problems the way you have it there.

The documentation for DAYNAME() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_dayname) shows that it requires a date argument. It's being used like this:
SELECT DAYNAME('2007-02-03');
That implies that you may also want to use CURDATE() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_curdate).


EDIT: By the way, you're using an undefined $query variable here:
$message .= 'Whole query: ' . $query;

[Old Pedant]

So are you saying you want all records where the day of the week is Tuesday (today is Tuesday, at least where I am) and where the time in the record is earlier than the current time?

So that would mean that a record with a `date` field of '2009-08-18 01:03:15' *would* be picked. That's also a Tuesday. And the time is earlier than right now. Also, '2008-12-02 00:31:22' would qualify. And so on.

Or does your `date` column *really* hold values such as "Tuesday" and "Friday" instead of actual dates???