Jump to content


Photo

Adding a selected number of Days to an existing Date


  • Please log in to reply
6 replies to this topic

#1 mhoctober

mhoctober
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 18 July 2006 - 09:37 PM

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




#2 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 18 July 2006 - 09:40 PM

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

#3 mhoctober

mhoctober
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 18 July 2006 - 09:59 PM

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?

#4 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

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.

#5 mhoctober

mhoctober
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 18 July 2006 - 10:33 PM

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

#6 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 19 July 2006 - 01:25 AM

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

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).

#7 mhoctober

mhoctober
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 19 July 2006 - 11:10 AM

Awesome asistance guys - thanks.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users