Page 1 of 1
getting days between dates
Posted: Fri Aug 06, 2010 10:24 am
by nite4000
Hey all I have a simple error I am trying to fix.
I am working on a site that has listings and I need to display how long each one has been on the site in teh table i have a date_added field
This is my code
Code: Select all
<?php $q = mysql_query("SELECT * FROM table") or die(mysql_error());
$info = mysql_fetch_array($q, MYSQL_ASSOC);
@mysql_free_result($q);
$date1=date('Y-m-d');
$date2=$info['date_added'];;
echo (round($diff=strtotime($date1)-strtotime($date2)))/86400;
?>
I am not sure how to get it to work.
Any help would be nice.
Re: getting days between dates
Posted: Fri Aug 06, 2010 10:46 am
by pickle
It looks like your `info` field is in the MySQL Date format. If so, you can easily use the
MySQL DATEDIFF function function to do the calculation right in MySQL.
Re: getting days between dates
Posted: Fri Aug 06, 2010 11:00 am
by nite4000
I am not sure I follow what you mean.
Re: getting days between dates
Posted: Fri Aug 06, 2010 11:32 am
by pickle
Check the documentation on the link I sent. It shows how you can use the DATEDIFF function. One of your parameters to the DATEDIFF function in your query can be your `info` column. The other parameter can be a string you provide that is your second, manually created date.
Re: getting days between dates
Posted: Fri Aug 06, 2010 12:01 pm
by nite4000
ok well i tried but maybe i am not thinking on this right
here is what i have
Code: Select all
$q = mysql_query("SELECT DATEDIFF (date1, date_added) FROM listings") or die(mysql_error());
$info = mysql_fetch_array($q, MYSQL_ASSOC);
@mysql_free_result($q);
$date1=date('Y-m-d'); //todays date
$date2=$info['date_added']; //date in field for record
echo (round($diff=strtotime($date1)-strtotime($date2)))/86400;
When i echo this it needs to give the days between each date in database and current date.
Re: getting days between dates
Posted: Fri Aug 06, 2010 12:06 pm
by pickle
If you're using your $date1 variable you need to define it before you use it. You also need to reference it in your query:
Code: Select all
$date1=date('Y-m-d'); //todays date
$q = mysql_query("SELECT DATEDIFF ('$date1', date_added) FROM listings") or die(mysql_error());