Adding a selected number of Days to an existing Date
Posted 18 July 2006 - 09:37 PM
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
$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
Posted 18 July 2006 - 09:40 PM
Posted 18 July 2006 - 09:59 PM
"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?
Posted 18 July 2006 - 10:02 PM
[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';
Sorry, missed that on the first read through.
Posted 18 July 2006 - 10:33 PM
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....
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
Posted 19 July 2006 - 01:25 AM
UPDATE tblPeople SET fldExpiryDate = DATE_ADD(fldExpiryDate, INTERVAL $adDurationInDays DAY) WHERE stuff
if you want to do it in weeks, it's just as easy. change the interval type:
UPDATE blah SET blah = DATE_ADD(blah, INTERVAL x WEEK) WHERE blah
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).
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users