Jump to content

Archived

This topic is now archived and is closed to further replies.

mhoctober

Adding a selected number of Days to an existing Date

Recommended Posts

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


Share this post


Link to post
Share on other sites
Don't use single quotes(') around the field name...use back tics(`)...which is usually the button above the tab.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.