[resolved] Search on date's in DB?

[Mr Chris]

Hi,

I have 2,000 stories saved in a database (Not my DB) and I want to perform a search on the [b]section[/b] of story and the date it [b]appeared[/b] on the website.

The trouble is the DB is dodgy and dates are saved in a format like:
[b] 2007-1-8[/b] //Today
Ie
[b] YYYY-M-D[/B]

Now what I want to do is perform a MySQL statement whereby I perform a search on the date in this format:

[b] 2007-1-8[/b] //Today
Ie
[b] YYYY-M-D[/B]

IE: "Y-n-j"

And then my SQL finds the date I asked for, but then

- Checks it’s not greater than today’s date (NOW) by using unix_timestamp on both the [b]appreared[/b] field and [b]published[/b] field.

And then output all the results to the screen.

Now I thought this may work:

[code=php:0]

$sql = "select id,headline,appeared from news_stories
where section = '$searchstring' AND
date_format('$appeared','%Y-%m-%d') =
date_format(appeared,'%Y-%m-%d') AND unix_timestamp(published) <=
unix_timestamp(now())";

[/code]

But it does not and outputs no stories found.  Any advice please

Many Thanks

Chris

[chronister]

MySql will search in the YYYY-M-D format. I have a library due items database for my wife that is also stored in that format and I have a query that searches for dates between sunday to saturday of current week.

The query is

[code]$query="SELECT * FROM lib WHERE due BETWEEN '$today' AND '$oneweek' or due < '$today' ORDER BY due asc";[/code]

I personally would do this.
[code]
$now=date("Y-m-d");  // returns a unix time string

$sql = "select id,headline,appeared from news_stories
where section = '$searchstring' AND appeared = '$appeared' AND published <= '$now'";

[/code]

No sense in converting everything into unix timestamps when mysql will recognize the YYYY-M-D format

lemme know if this helps

[Mr Chris]

Perfect - thank you!