date queries

[wmguk]

Hi Guys,

 

I've got a couple of queries really

 

1)

I'm looking to work out the amount of days between 2 different dates.

 

I have $date1 and $date2 but i just cannot seem to work out what i need to do to get the result of 345 days....

 

the date is pulled out as 2008-03-14 (Y-m-d)

 

I have checked the php manual for date, but can only work out how to format it..

 

can anyone help please?

 

2)

I need to pull out the info in month order (So i will have a drop down to select the month to search) and it will show everything in the database for march... I know i can run a php query but because my date is stored 2008-03-14 (as per Mysql) how can i just say, show all result where xxxx-03-xx ?

 

Thank you for any help

[PFMaBiSmAd]

The easiest answer to both of your questions are found in the mysql manual.

 

For #1, use the datediff() function -  http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff

 

For #2, use the month() function to get only the month from your date field to compare it in a WHERE clause - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_month

[wmguk]

The easiest answer to both of your questions are found in the mysql manual.

 

For #1, use the datediff() function -  http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff

 

For #2, use the month() function to get only the month from your date field to compare it in a WHERE clause - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_month

 

Thank you for that, I did see the mysql for query (1) but i need that to be done in php, and displayed on a php page, rather than using MySQL scripts.

 

What I have so far:

 

$showdate = date('Ymd', strtotime($row['creationdate']));

echo $showdate ;
echo "<br />" ;
$nowdate = date("Ymd") ;
echo $nowdate ;
echo "<br />" ;

$date = ('$nowdate' - '$showdate') ;
echo $date ;

[PFMaBiSmAd]

Adding the following in your SELECT query will return the number of days in a field called diff -

 

SELECT *, DATEDIFF(CURDATE(),creationdate) AS diff the_rest_of_your_query_here...

 

After you fetch the row -

 

echo $row['diff'];

 

Pretty easy? Don't use a lot of slow and complicated php code for something that one statement in a query can do.

[wmguk]

Adding the following in your SELECT query will return the number of days in a field called diff -

 

SELECT *, DATEDIFF(CURDATE(),creationdate) AS diff the_rest_of_your_query_here...

 

After you fetch the row -

 

echo $row['diff'];

 

Pretty easy? Don't use a lot of slow and complicated php code for something that one statement in a query can do.

 

okies, ill have a look in to that, never done anything like that before, normally let php do it all..

 

$showdate = date('Ymd', strtotime($row['creationdate']));
$nowdate = date("Ymd") ;
$date = ("$nowdate" - "$showdate") ;

 

however, i did get it working, but if something is 4 years i get 12489 days produced lol, is there anyway to format the output to years, days?

[PFMaBiSmAd]

The subtraction in the following is not giving you what you expect -

 

$date = ("$nowdate" - "$showdate") ;

 

It is literally subtracting the characters in each successive position in the strings. This does not result in the number of days between dates (do a reality check using some random dates a few months and a few years apart.)

[wmguk]

ahhh, yeah very true :(

 

hmmm back to the drawing board then

[wmguk]

just tried your way

 

$result2 = mysql_query("SELECT *, DATEDIFF(CURDATE(),creationdate) AS diff FROM album WHERE login = '$login'");

 

but it doesnt pull anything out? just get an empty answer

[PFMaBiSmAd]

It works for me. What is your code to check for query errors, fetch the results, and display it?

[wmguk]

oh ok, sorry scrub that, its working now, how would i be able to say years and days, cause it now says 970 days

[samshel]

Hi,

 

check if this works.

 

<?php 
$indays = 970;

echo $intyears = (int) (970/365);
echo " Years and ".$intRemainingDays = fmod(970,365);
?>

[wmguk]

Hi,

 

check if this works.

 

<?php 
$indays = 970;

echo $intyears = (int) (970/365);
echo " Years and ".$intRemainingDays = fmod(970,365);
?>

 

Hey,

 

the problem is that this would be 970 very often, so it needs to work where the resulting days are $diff (however also it might only be 35 days...

[wmguk]

<?
echo $intyears = (int) ($diff/365);
echo " Years and ".$intRemainingDays = fmod($diff,365);
?>

 

Just editted it and its working, plus it says 0 years and 23 days if it needs too..!! excellent thank you

[samshel]

//$diff is the difference

$intyears = (int) ($diff/365);
$intRemainingDays = fmod($diff,365);

$str = "";
if($intyears > 0) $str .= $intyears." Years and ";

$str .= $intRemainingDays." Days";

?>