How to update table with a date

PHP
1

Hloo,

I ant to update a table of users where a user subscribes for a 30 days free trial.

When a user subscribes for it - I want to insert the trial end date - 30 days from subscription date

Here is the code with which I tried to do it:

function update_free_trial( $username)
{
	global $db;
	
	try
	{
		$sql = "UPDATE users 
		        SET trial = :SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 30 DAY) AS trial 
				WHERE username = :username";	

		$stmt = $db->prepare($sql);
		$stmt->bindParam(':trial', $trial, PDO::PARAM_STR);
		$stmt->bindParam(':username', $username, PDO::PARAM_STR);		

		$stmt->execute();
		
		return true;	
	}
	catch(Exception $e) 
	{
	   return false;        
	}
} // End function
$result = update_free_trial($username);
if($result ==true){
echo "yes";
} else {
echo "no";	
}

I got a “no” as result

What is the correct way to "tell the DB to updatea date 30 days ahead from current date?

2

A cleaner logic would be to just calculate x days from start date. What if you decided to change the trial to 45 days or have multiple trial periods? You would end up writing spaghetti code and doing code gymnastics to get the info you want.

3

That’s actually what you should be doing. You should insert a new row of data into a ‘subscription’ table to record all the who, what, when, where, and why information about each subscription. The who information would be the user’s id (auto-increment integer primary index in the users table), to relate the subscription record(s) to the user they belong to.

Your database statement error handling is not displaying/logging the reason why a query has failed and since it doesn’t stop code execution upon an error, you are getting meaningless follow-on information from your code.

In most cases, a database statement error is a fatal problem, either due to a programming mistake or a database server that’s not running, and the current web page won’t work. In these cases, there’s no good reason for your code to continue to run and try to use the result from a query that never executed. If you remove the try/catch logic for these cases and let php catch the database statement exception, php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors) and your code execution will stop and not try to use the result from a failed query. The exception to this rule is when inserting/updating duplicate or out of range user submitted data. In these cases, your code should catch the database statement exception, test if the sql error number is for something that your code is designed to handle, then setup and display a message telling the user what was wrong with the data that they submitted. For all other sql error numbers, just re-thow the exception and let php handle it.

1 Like
4

What’s the purpose of this line?

$stmt->bindParam(':trial', $trial, PDO::PARAM_STR);

You’re trying to bind a variable that you don’t define anywhere ($trial) to a parameter that isn’t in your query (:trial). And why is there a colon before your inner SELECT in the query? I haven’t seen that used before.

5

You almost had it.

UPDATE users
SET trial = DATE_ADD(CURRENT_DATE(), INTERVAL 30 DAY) 
WHERE username = :username;

This is the sort of thing for which you would fire up your sql console command and just type in the query until you the get the syntax right.

Of course, as some of the other posters have mentioned, it is a bit confusing as to why you would need to do this.

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.