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());