I have a mysql table (`news`) set up to hold news articles. It has 6
fields: `ID` (smallint - auto increment), `start` (date), `end` (date),
`headline` (varchar 255), `newsitem` (text), and `imp` (varchar 255 -
can be null). I want to get the headlines pulled from the table and
displayed on a page. I want them to display in ascending order by end
date and sub-ordered by the imp field until the date after the end date.
The imp field is used to push items that are farther off than the 8
selected to the top of the list for extra exposure.

Example: headline of "Free coffee Sept 28" carries an end date of
2006-09-28. I want that to stop displaying when today's date turns to
2006-09-29.

I can get the headline to display on the page properly but I seem to
have a mistake somewhere in getting it to stop displaying as of the
29th. The code I'm using is:

<?
mysql_connect("$dbhost","$dbuser","$dbpass"); //(host, username, password)
mysql_select_db("$dbase") or die("Unable to select database"); //select
database

$now = date("Y-m-d"); //today's date

$query = "SELECT * FROM `news` WHERE ($now < `end`) ORDER BY `imp`,`end`
LIMIT 8";

$result = mysql_query($query) or die(mysql_error());

//and then process the results.
?>

But the item that should have ceased being displayed on the 29th is
still displaying. What do I need to do to correct this?

JackM wrote:
> I have a mysql table (`news`) set up to hold news articles. It has 6
> fields: `ID` (smallint - auto increment), `start` (date), `end` (date),
> `headline` (varchar 255), `newsitem` (text), and `imp` (varchar 255 -
> can be null). I want to get the headlines pulled from the table and
> displayed on a page. I want them to display in ascending order by end
> date and sub-ordered by the imp field until the date after the end date.
> The imp field is used to push items that are farther off than the 8
> selected to the top of the list for extra exposure.
>
> Example: headline of "Free coffee Sept 28" carries an end date of
> 2006-09-28. I want that to stop displaying when today's date turns to
> 2006-09-29.
>
> I can get the headline to display on the page properly but I seem to
> have a mistake somewhere in getting it to stop displaying as of the
> 29th. The code I'm using is:
>
> <?
> mysql_connect("$dbhost","$dbuser","$dbpass"); //(host, username, password)
> mysql_select_db("$dbase") or die("Unable to select database"); //select
> database
>
> $now = date("Y-m-d"); //today's date
>
> $query = "SELECT * FROM `news` WHERE ($now < `end`) ORDER BY `imp`,`end`
> LIMIT 8";
>
> $result = mysql_query($query) or die(mysql_error());
>
> //and then process the results.
> ?>
>
> But the item that should have ceased being displayed on the 29th is
> still displaying. What do I need to do to correct this?

Hmm, you know MySQL knows when now is. Try this:

$query = "SELECT * FROM `news` WHERE (now() < `end`) ORDER BY
`imp`,`end`
LIMIT 8";

Otherwise maybe try sticking $now into inverted commas:
> $now = "'".date("Y-m-d")."'"; //today's date

strawberry wrote:

>>Example: headline of "Free coffee Sept 28" carries an end date of
>>2006-09-28. I want that to stop displaying when today's date turns to
>>2006-09-29.
>>
>>I can get the headline to display on the page properly but I seem to
>>have a mistake somewhere in getting it to stop displaying as of the
>>29th. The code I'm using is:
>>
>>$now = date("Y-m-d"); //today's date
>>
>>$query = "SELECT * FROM `news` WHERE ($now < `end`) ORDER BY `imp`,`end`
>>LIMIT 8";
>>?>

>
>
> Hmm, you know MySQL knows when now is. Try this:
>
> $query = "SELECT * FROM `news` WHERE (now() < `end`) ORDER BY
> `imp`,`end`
> LIMIT 8";

Well, duh! It never even occured to me that mysql knew when now is.
<slaps forehead> I'm so used to doing $now = mktime(); php statements
that I just carried it over to the mysql syntax. Thanks for putting me
straight on this. Your solution works perfectly and a lesson has been
learned.