Adding a selected number of Days to an existing Date

[mhoctober]

Experts...

I have a start date in a database (fldDateCreated) and wish to add a number of days to it to calculate and store an end date in the database. The number of days to add to the start date is determined by the value that a user has selected from a form drop down field (frmAdDisplayFor)

//Code Snippet Start

$adDurationInDays=$_POST['frmAdDisplayFor'];
$expires=mysql_result($result,$i,"fldDateCreated")+$adDurationInDays;
$query = "INSERT INTO tblPeople(fldExpiryDate) VALUES ('$expires') where 'fldTransActionNumber' = $transNum";
mysql_query($query) or die('There is a problem: ' .mysql_error());

//Code Snippet End

This is the error message that I am getting when I run the code above...

"There is a problem: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where 'fldTransActionNumber' = 10016' at line 1"


I'm guessing that I have to do some further work with the variable $expires to convert it to a date before I can insert the value it holds into the table.  Any ideas?

Many thanks in advance....Mike

[hitman6003]

Don't use single quotes(') around the field name...use back tics(`)...which is usually the button above the tab.

[mhoctober]

Karma - thanks for the suggestion...which I have tried and still get the same error...

"There is a problem: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where `fldTransActionNumber` = 10018' at line 1"

Any further ideas?

[hitman6003]

[code][code]It just occurred to me that you should be using an update query, not an insert query:

[/code]UPDATE tblPeople SET fldExpiryDate = '$expires' WHERE fldTransActionNumber = '$transNum';[/code]

Sorry, missed that on the first read through.

[mhoctober]

Thanks again....now I think I'm getting close to the solution!!

I am no longer getting an error message but the fldExpiryDate in the table is reading 0000-00-00

here is the revised code snippet....

$num=mysql_numrows($result);
$adDurationInDays=$_POST['frmAdDisplayFor']*7;
$expires=mysql_result($result,$i,"fldDateCreated")+$adDurationInDays;

echo "Weeks ".$_POST['frmAdDisplayFor']."<br>";
echo "Days ".$adDurationInDays."<br>";

$query = "Update tblPeople SET fldExpiryDate='$expires' where `fldTransActionNumber` = $transNum";

I'm still suspecting that I need to do some further work with the variable $expiry before I can store its values in the fldExpiryDate database field.

Thanks for the help

[akitchin]

you seem to be going about this in a very lengthy way.  allow MySQL to save you some effort:

[code]UPDATE tblPeople SET fldExpiryDate = DATE_ADD(fldExpiryDate, INTERVAL $adDurationInDays DAY) WHERE stuff[/code]

if you want to do it in weeks, it's just as easy.  change the interval type:

[code]UPDATE blah SET blah = DATE_ADD(blah, INTERVAL x WEEK) WHERE blah[/code]

anytime you're doing some database updates, before using PHP to get the value you want, see if MySQL has a built-in function for it.  their functions often save time and effort since it knows what format the value needs to be in, etc.

have a peek in the MySQL manual at "Functions and Operators" (chapter 7 i think).

[mhoctober]

Awesome asistance guys - thanks.