URGENT coding help

Anon

Hi,
I hope someone will offer a prompt solution in this.

I have a news managment on my site.

Table news:
id smallint (5) zerofill not null auto_increment,
title varchar(255),
text blob,
date varchar(10),
image varchar(12),
hour char(5),
autor_id char(3)

The news are submited almost always with current date and time, but user can specify some other time or date for specific news to show up on webpage.

Date is submited in format: 20-10-2000
Hour is submited in format: 14:45

On webpage I get the hour and date in specific code and then I compare them with hours and dates inserted in MySQL table.

If the user specify some future date or time, how can I make the PHP code to display that news exactly on time that has been set manually by the user?

Anon

Are you storing the date in that same format?
Too bad, because it will make date-based queries
impractical. I.e. you can't say

  ... where (date >= '$begin' and date <= '$end')

and have it always work because the most-siginificant
part of the date is not on the left-hand side. Any chance
that column could be converted to an actual date type?
The same probably goes for the 'hour' column.

Anon

SQL statment that pulls the news stories needs to have a WHERE clause that basically says look for stories older than NOW. You will have to change the date and hour fields from a varchar to a timedate type. Something like this...

SELECT * FROM news WHERE date < DATE_FORMAT(NOW()) AND hour < DATE_FORMAT(NOW());

I know there is more parameters to DATE_FORMAT (if that is the actual function... my memory is fuzzy on mysql), but you can look those up in the reference.

Hope this helps some.

Anon

Actually, now that I think about it, why have the date +
time split into two separate columns in the first place?
Of course you'll want to have separate fields on the
web forms where you enter them, but then merge
them into a single column so you query based on
a single parameter.