Searchable date format

[TEENFRONT]

Hey

 

Whats the best searchable date format for php mysql ? Im after only showing news on and after the publish date specified.

 

So i first need to know which date() i should be using..

 

Iv researched date() and got date("Y-m-d"); , but is this searchable?

 

Also would need to know how to show only articles that have todays date or previous dates ( not future dates etc )

 

Any help?

[john010117]

What do you mean by asking "is it searchable"?

[TEENFRONT]

as i mentioned, i only wish to show news thats set as todays date or previous dates.

 

So if i set up "news item 1" and set it to display on "2-1-07" and then everyday after that date, but not show up on dates before that ( so wont show on 1-1-07 etc )  surley i cannot do a mysql select based on 2-1-07 ... how can this be searchable, its just numbers?

[radar]

Me personally I won't use a unix timestamp anymore, in my database there is a type called DATETIME -- this is what I use now as it comes out to be something like...

 

0000:00:00 00:00:00  or something similar to that, and so you can search down to the second if you really want to.  DATETIME for me is easier to work with than a regular timestamp for one reason -- i can tell what the heck it says before any manipulation has happened...  with a timestamp you have to manipulate it before you can read it....  hope this info helped...  and as i say to everyone, if you need any help i am only a pm away.

 

So add a field to your table, for the date -- make it a datetime field, and use that to search....  OR in your case it might be easier to run it as a regular timestamp by using the time() function...  because then you can do something like this for your query

 

$time = time();

 

$query = mysql_query("SELECT * FROM news WHERE date <= '$time'");

 

wouldn't really need any math stuff to go with it...  time(); is the current date as of the time the script was run for that viewer...  and so, you take everything if date is less than or equal to $time, go ahead and pull that info and leave the rest alone....  that'd prolly be the easier way to do it for your application.

[yzerman]

As long as your date is stored in the database in a field typed date - you will be able to use:

 

SELECT * FROM tbl WHERE date < NOW() AND date > '2007-02-01';

 

This will give you the results where the date is less then todays date (say you input a news post that you dont want shown til the release date) and greater then the date you specify.

 

You can also do date ranges, this will select all the news posts with dates between today, and 3 months ago.

 

$datecutoff = $lastmonth = date("Y-m-d", mktime(0, 0, 0, date("Y"), date("m")-3,  date("Y")));

SELECT * FROM tbl WHERE date < NOW() AND date > '$datecutoff';