if newer than 7 days print 'New'

sgray

hi,

Im wondering what the easiest way to get my php script to list a mysql entry as 'new' if it had been added within 7 days of the current date?

The date of each entry in the db is in a 'text' field in the form of '10-sep-2002'. Im wondering if it would be very easy to get the script to check each one for 'new' status?

I imagine it might take some grunt to do this query on each and every entry in the db (300 entries, growing pretty quickly)

Any suggestions would be appreciated,

Stew

Lars_Berg

Don't store dates in textfields. Either use a datetime column or store it as a unix timestamp.

If it is a datetime column you can use a statement like

select case when to_days(current_date) - to_days(dateColumn) < 7 then 'New' else '' end from ogrish

If you use a unix-timestamp, Mysql has conversion function to and from datetime.

sgray

i realise that I should have used a date field type but its little late now. Is it possible to get a php script to assign the following:

'10-sep-2002'

$day=10
$month=sep
$year=year

I think it would be easy enough to figure it out if it were older than 7 days if I could assign the text date values to variables like above. any ideas?

ucbones

This is perhaps a little crazy, and the second is exceedingly inefficient, but I have two ideas...

Add a field to MySQL called expire new, inserting the date when the article should no longer display that it is new.
Explode the date/stamp, work out when it should expire by adding seven days, then checking if "today" is later than the expire date.

Hope that helps,

ucbones 😃

jeremuck

if you've only got about 300 records, it's really not too late to change the field type.

anyway, there several core functions in PHP to assist you.
I believe there is a function that is able to convert your data format directly into another, more manageable format.
You can read about the time and date functions in the manual.

Since there is a function that can understand your date quickly, it would not be hard to write a script that dumps your data into a table with proper field data types. That is what you should do.

sgray

ucbones: how would I go about doing this? my php skills dont extend too much further than being able to make the new 'expire' field!

Point me in the right direction please, I should be able to pick it up from there! 😛

would the code used be similar to what Lars Berg used above? how do I go about getting my code to make an entry in the date column automatically, at the moment I use '$date = date("M-d-y", time()); '

Regards,

Stewart

ucbones

I hate to admit to this Stewart, but my MySQL skills are exceptionally deficient (only had it a few weeks!!!) the method you are using for storing dates looks fine to me, I'm not quite sure to what extent MySQL can performs logic statements with fields other than integers.

If it can't, then I suggest you contemplate storing the date as a time stamp (time in seconds since some date in the 1970's or thereabouts!!!) and do something like...

$expire= (MySQL code to grab expire tstamp from table)
$currenttime=mktime (1,00,00,date("m") ,date("d"),date("Y"));

then, for every article, place the following sort of code

if ($currenttime > $expire) {
echo "<img src='http://www.yoursite.com/images/new.gif' alt='new article!'>";
}

Can anyone with a bit more MySQL knowledge offer some assistance!!!

ucbones

😃

sgray

looks like a step in the right direction, cheers.

Can anyone assist in how I would go about implementing this features with timestamp?

jeremuck

mysql offers a date data type in the format of YYYY-MM-DD.
It also provides a mySQL timestamp which is updated every time you update that record.
Also, you can use VARCHAR(8) to simulate the UNIX timestamp, which is the number of seconds since Jan 1, 1970.

mySQL is adept at comparing these.

I don't think it's too late to convert your > 300 records using a better date format.

www.mysql.com has a very useful manual.

sgray

jeremuck: I just made a field with vartype(8). I tried adding a couple of new entries but it didnt automatically enter the seconds from the start of the year. how can I get it to do this?

jeremuck

the time() function will supply you with the number of seconds since Jan. 1 1970