Yet another date problem: 'New' icon

[Anon]

Newbie PHP guy here with a date question. I've been doing some research but just can't make my date coding work.

I'm getting a date out of a MySQL query: yyyy-mm-dd.

Let's call it $row[5] 😉

I want to determine if $row[5] is less than 30 days old from today's date. If so, I'll display a new icon whose html is contained in $IconStr.

What would the code be?

Thanks for your help!

Johnn Four
[e-mail]
http://www.roleplayingtips.com

[Anon]

Ah I remember the days when I started using dates with MySQL. If it makes you feel any better, I also thought it was an unholy bitch to work with 🙂. Anyway here's your solution:
Here I'm solving your problem while you select the database records, instead of after. You can use MySQL's curdate() and unix_timestamp() functions to get the current system date and to turn a date into a timestamp, respectively, like so, where "date" is the field in which you store your date:
SELECT FROM myTable WHERE unix_timestamp(curdate()) - unix_timestamp(date) < 60602430
In this case every row you receive in the result set will have a date less than 30 days old. We're dealing in seconds here, so 60602430 gives us 30 days.
unix_timestamp(curdate()) - unix_timestamp(date) gives us the amount of seconds between the date in 'date' and the current system date, which we compare to 60602430. If it's less than 606024*30 MySQL selects the row, adding it to the result set

[Anon]

Hi Donncha,

Thanks for the fast reply!

I should have been clearer in my request, as I feel that my problem cannot be solved in SQL. I need to produce a page of all the articles in my database, and if any are less than 30 days old I want to flag them with a "New" icon.

So, I cannot use SQL to bring back a date restricted results set. It seems the date analysis must be done in PHP.

Any other ideas?

Cheers,
[e-mail]
http://www.roleplayingtips.com

[Anon]

I'm not convinced this would be the most efficient solution, but if you want to do it in php than this will give you the number of days elapsed between the current date and the stored date. Please test that it works...

$date = "2001-09-12";
preg_match("/([0-9]{4})-([0-9]{2})-([0-9]{2})/", $date, $array);
$y = $array[1];
$m = $array[2];
$d = $array[3];
$ts = mktime(0, 0, 0, $m, $d, $y);
$de = sprintf("%d",(time() - $ts)/60/60/24);
print $de;

[Anon]

Thanks Duble_E & Donncha,

I finally pieced together a solution that works, though I'm not sure if it's the best or most efficient:

$article_date = $row[5];
$newdate = mktime(0, 0, 0, date("m")-1,date("d"), date("Y"));

$article_date_array = explode("-", $article_date);
$article_year = $article_date_array[0];
$article_month = $article_date_array[1];
$article_day = $article_date_array[2];
$article_timestamp = mktime(0, 0, 0, $article_month , $article_day, $article_year);

if ($article_timestamp > $newdate) {
$newStr = "<img src='../images/new_icon.gif' width='31' height='12' hspace='0' vspace='0' border='0' align='middle' alt='new!'>";
} else {
$newStr = "";
}

Cheers,
[e-mail]
http://www.roleplayingtips.com

[Anon]

You'd be much better off using the strtotime() function, it'll give you a far more accurate timestamp than simply the start of last month. In your above example, the story could be up to 60 days old and still Just let $newdate = time ( ) - 606024*30;
and
$article_timestamp = strtotime ( $row[5] ).

So now your script looks like this:

$newdate = time ( ) - 606024*30;
$article_timestamp = strtotime ( $row[5] )

if ($article_timestamp > $newdate) {
$newStr = "<img src='../images/new_icon.gif' width='31' height='12' hspace='0' vspace='0' border='0' align='middle' alt='new!'>";
} else {
$newStr = "";
}

Simple eh 🙂

[Anon]

Wow, I tested that code out and it works beautifully. Much better now! Thank you very much.