Jump to content

Searchable date format


Recommended Posts



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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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';


Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.