modify date in an array

subtract year of date stored in an array

[SusanA]

Hello, I'm a newby and have muddled through alot but I am stuck I need to subtract a year from a date field that is shown in a drop down box. The data in my database is 2015-02-07 but I need to display 2014-02-07. I know in my select statements I can use - INTERVAL 1 YEAR but I can't figure how to display the date as a year earlier. Can anyone help me.

$sql1 = "SELECT DISTINCT `datePackageded` FROM `packing1` ORDER BY `datePackageded` DESC ";
$result1 = mysql_query($sql1) or die("Error601: " . mysql_error());
$rowcount = mysql_num_rows($result1);
echo("<form name=\"recallmgmt\" method=\"post\" action=\"recallMgmt.php\">");
echo('<table border="1"><th bgcolor="#D3DCE3" colspan=2><font face="Verdana" size="3" color="#2349F6"><center><b>Total Package Dates: '.$rowcount.'</b></center></font></th>');

echo('<tr><td bgcolor="#E5E5E5"><font face="Verdana">Select your date: </font></td><td><select name="datePackageded" class="input" id="datePackageded" tabindex="1">');
echo("<option value=\"-1\" selected> </option>");

while($rows1 = mysql_fetch_assoc($result1)) {
//MODIFY ARAY HERE?
 echo('<option value="'.$rows1['datePackageded'].'">'.$rows1['datePackageded'].'</option>');
}
echo("</select></td></tr></table><HR>");

Is there a way to add a calculation to subtract a year to what is displayed? I appreciate any help and suggestions!

[omoutop]

Assuming that your dates are stored in date field in your table, you can use something like:
SELECT DATE_SUB(`datePackageded`-INTERVAL 1 YEAR)

Now if your stored values are in any other format you can use php to convert the value of the field to unixtimestamp and substract 1 year.
For example:
$curDate = time();
$prevYear = $curDate - strtotime("-1 Year");
echo date("Y-m-d", $prevYear);

[SusanA]

Thank you for the reply - yes the field is a date field. I've been able to use the example you suppplied in my select statement but I can't figure out the syntax when trying to display it on a form to show the date a year earlier -

I've tried
$rows1['datePackageded'] = ($rows1['datePackageded']- INTRVAL 1 YEAR );

but it errors out.

I also tried to modify this line of code to subtract the year but unable also

echo('<option value="'.$rows1['datePackageded'].'">'.$rows1['datePackageded'].'</option>');

I tried to modify the above line to
echo('<option value="'.$rows1['datePackageded'].'">'(.$rows1['datePackageded']- INTERVAL 1 YEAR).'</option>');

and that too is incorrect.

[omoutop]

SELECT DATE_SUB(`datePackageded`-INTERVAL 1 YEAR)

This you use inside your query, not in your php
not in here

$rows1['datePackageded'] = ($rows1['datePackageded']- INTRVAL 1 YEAR );

For pure php solution you can try something like:

$calculated Date = strtotime('".$rows1['datePackageded']." -1 year');
echo date("Y-m-d", $calculated);

EDIT
If your php supports the DateTime class (php >5.2.0)
then you can use this:
$time = new DateTime($rows1['datePackageded']);
$newtime = $time->modify('-1 year')->format('Y-m-d');

[SusanA]

Omoutop Thank you for clearing that up! I am using php greater than 5.2.0
I used your suggestion for the DateTime class - could you tell me why when I use the code below shows the same date - choosing a different date line does give me the correct value I am looking for but the same date shows repeatedly in the drop down. I thought that the while statement would loop through and calculate the date for each distinct datepackegeded.

while($rows1 = mysql_fetch_assoc($result1)) {
$time = new DateTime($rows1['datePackageded']);
$newtime = $time->modify('-1 year')->format('Y-m-d');
echo('<option value="'.$rows1['dateBestUsed'].'">'.$newtime.'</option>');
}

As you can see I'm not a php programmer but I'm trying to learn. I REALLY appreciate your help.

[omoutop]

You said that the

$rows1['datePackageded']

comes from a date field in mysql database. I assume the format is Y-m-d

while($rows1 = mysql_fetch_assoc($result1))
{
$time = new DateTime($rows1['datePackageded']);
$newtime = $time->modify('-1 year')->format('Y-m-d');
echo('<option value="'.$rows1['dateBestUsed'].'">'.$newtime.'</option>');
}

Line by line we have:

$time = new DateTime($rows1['datePackageded']);

this will look like: $time = new DateTime(2014-09-12);

$newtime = $time->modify('-1 year')->format('Y-m-d');

This must look like $newtime = '2013-09-12';

echo('<option value="'.$rows1['dateBestUsed'].'">'.$newtime.'</option>');

Here i have a problem. What is $rows1['dateBestUsed']? What format it has?

To debug the problem, change your loop into something liek this:

while($rows1 = mysql_fetch_assoc($result1))
{
$time = new DateTime($rows1['datePackageded']);
$newtime = $time->modify('-1 year')->format('Y-m-d');
echo 'original= '.$rows1['datePackageded'].', modified= '.$newtime.'<br>';
}

Check your calculations to see if it comes as you want it.
Post back here and i will try to assist. (Post final loop with query again)

[SusanA]

Thank you! Thank you! Doh it was a syntax error. I've got it working. I REALLY appreciate you helping and explaining it to me.