Updating time stamp - no update

[El Chupacodra]

I have this code to change the validity date of an account.

I do a check against timestamp and current validity which works  fine.

Then I have radio buttons you can check to choose to extend with one, three, six or 12 months through a form and a post variable - they work too.

When I write the variables out they look fine but when I want it in my database - nothing happens!

I mean the original data is never changed.

Can you see what is wrong?

 

I tried changing the data type from date to datetime to timestamp with the same result.

I suppose I will learn something new from phpfreaks today again.

 

Code is here for the 12 month radio button:

		$newdate = strtotime ( '+12 months' , strtotime ($fromDate) ) ;
	$newdate = date ('Y-m-d H:i:s', $newdate );
	echo "$newdate";
	$query = "UPDATE TABLE user_data SET paid_days='$newdate' WHERE user='$user'";
	mysql_query($query);

When I write the variable $newdate it shows up as 2013-01-10 21:55 (right now) which seems like a valid format to me.

Do I need to convert it somehow?

[Pikachu2000]

I suspect you don't need to be calculating the date in php at all. Is there more code that goes with that? Where does $fromDate come from?

[El Chupacodra]

$fromDate is a variable thats just sets where to start counting - from today's date or from the registered ValidTo date, whichever is more recent.

The variable $validTo is taken straight from the DB (Timestamp format, normal sql query).

Code here:

$fromDate='0000-00-00 00:00:00';
if ($validTo>$now) {
$fromDate = $validTo;
}
if ($now>$validTo){
$fromDate = $now;
}
else $fromDate = $fromDate;

 

EDIT: Echoed variables: Newdate = 2013-01-10 22:20:00 Testdate = 2012-02-10 PaidTo = 2012-01-09 00:00:00 Now = 2012-01-10 22:20:00 FromDate = 2012-01-10 22:20:00

 

Your account is currently VIP until 2012-01-09 00:00:00

 

That part seems to work since I get the correct date and time when I echo $newdate.

It looks just right to me, it just won't appear in my DB.

[Pikachu2000]

Assuming that $validTo and $now are both valid YYYY-MM-DD hh:mm:ss formats, you can get rid of the $newDate calculation, and let MySQL handle adding the 12 months to the date.

$query = "UPDATE TABLE user_data SET paid_days = DATE_ADD( ";
$query .= $validTo > $now ? "'$validTo'" : 'NOW()';
$query .= ", INTERVAL 12 MONTH ) WHERE user='$user'";

[El Chupacodra]

That is an elegant solution!

I just woke up and typed it in but it still leaves the paidTo date at 0000-00-00 (with a zero timestamp after if I use datatypes Timestamp or Datetime).

Maybe I'll send you the entire code and you will spot what I missed.

Thanks for the suggestion anyway, second time you help me with dates in PHP.

Here is the entire page (except the include to the connection document):

 

$query = "SELECT paid_days FROM user_data WHERE user='$user'";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$validTo = $row[0];
$now = time();
$now=(date("Y-m-d H:i:s"));
$fromDate='0000-00-00 00:00:00';
if ($validTo>$now) {
$fromDate = $paidTo;
}
if ($now>$validTo){
$fromDate = $now;
}
else $fromDate = $fromDate;
if (isset($_POST['buy'])) {
 	 	$buy = sanitizeString($_POST['buy']);
      if ($buy==1)
 { 
	$newdate = strtotime ( '+ 1 month' , strtotime ($fromDate) ) ;
	$newdate = date ( 'Y-m-d' , $newdate );
	$query = "UPDATE TABLE user_data SET validTo=$newdate WHERE user = $user";

 }
      if ($buy==3)
 { 
	$newdate = strtotime ( '+3 months' , strtotime ($fromDate) ) ;
	$newdate = date ( 'Y-m-d H:i:s' , $newdate );
	$query = "UPDATE TABLE user_data SET validTo=$newdate WHERE user = $user";
 }
      if ($buy==6)
 { 
	$newdate = strtotime ( '+6 months' , strtotime ($fromDate) ) ;
	$newdate = date ( 'Y-m-d H:i:s' , $newdate );
	$query = "UPDATE TABLE user_data SET validTo= DATE_ADD($fromDate INTERVAL 12 MONTH) WHERE user = $user";
 }
      if ($buy==12)
 { 
$query = "UPDATE TABLE user_data SET valid_days = DATE_ADD( ";
$query .= $validTo > $now ? "'$validTo'" : 'NOW()';
$query .= ", INTERVAL 12 MONTH ) WHERE user='$user'";
mysql_query($query);
 }
}
$testdate = strtotime ( '+1 month' , strtotime ( $fromDate ) ) ;
$testdate = date ( 'Y-m-j' , $testdate );
echo " Testdate = $testdate";
echo " validTo = $validTo"; //three variable just to see if they work
echo " Now = $now";
echo " FromDate = $fromDate"; //it DOES work to figure out whether to count from today or the current validTo date



echo <<<_END
<br />
<p>Your account is currently VIP until $validTo</p>
<form method='post' action='account.php'>
<table><tr><td>
1 Month<input type='radio' name='buy' value='1' /></td><td>
3 Months<input type='radio' name='buy' value='3' /></td><td>
6 Months<input type='radio' name='buy' value='6' /></td><td>
12 Months<input type='radio' name='buy' value='12' /></td><td></tr><tr><td>
100<br />100/month</td><td>270<br /> 90/month</td><td>450<br />75/month</td><td>600<br />50/month</td></tr></table>
<input name='submitted' type='hidden' value='true' />
<input type='submit' value='Buy VIP Status'></form>

_END;
?>

 

[El Chupacodra]

I tried a number of different things but it won't update the table.

I set an echo to show if the code even reaches the 12 month update and it does.

I changed to update an empty table called test to see if it was a data type error but it wasn't - it wouldn't insert there either.

Then I let the query run in a brand new variable but it still refuses to run.

I add and take away quotation marks....still nada.

Why won't my (well Pikachu's) script run?

Actually I think it's not his fault since my simpler queries won't run either.

Going crazy here.

[El Chupacodra]

I got it working - it updates when I don't use DATE_ADD on the variables but on the database table itself.

So it should work as I intend it to if I first write the fromDate to the table and then update it there.

 

[El Chupacodra]

Finally got it to work by doing THIS:

 

	 	$query0 = "UPDATE user_data SET paid_days = '$fromDate' WHERE user = '$user'";
	mysql_query($query0); 
	$query = "UPDATE user_data SET paid_days= DATE_ADD(paid_days, INTERVAL 12 MONTH) WHERE user = '$user'";
	mysql_query($query);

 

...for every one of my options.

I appreciate the help from Pikachu and I will use that string somewhere.

This one is now DONE!