Keep Server Online
If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.
or
A donation makes a contribution towards the costs, the time and effort that's going in this site and building.
Thank You! Steffen
Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
| |
|
 Topic: PHP :: MySQL update issue |
|
| Author |
|
Mox
Joined: 14 Sep 2021
Posts: 8
|
 Posted: Wed 22 Sep '21 14:42 Post subject: PHP :: MySQL update issue |
 |
|
I have an issue updating record with PHP.
I am using PHP 7.2 and MySQL 8.0.
I have two pages: register.php and activate.php
In register.php there is a code that inserts user in a mysql table Users like this:
| Code: | $db = new PDO("mysql:host=" . $mysqlHost . ";dbname=" . $mysqlDB, $mysqlUser, $mysqlPassword);
$sql = "INSERT INTO Users (name, email, active, activationCode) VALUES ('$name', '$email', $active, '$activationCode')";
$result = $db->exec($sql);
|
Field 'active' in Users table is a boolean field with 0 or 1 value.
This code executes properly and user is inserted properly in table User. Field 'active' has a value 0.
Next, user gets email with an activation link to click in order to activate user account, like this http://activate.php?activationCode=123456
activate.php validates activation code and updates field 'active' with value 1, like this:
| Code: |
$sql = "UPDATE Users SET active = 1 WHERE activationCode = '" . $activationCode . "'";
$result = $db->exec($sql);
|
Problem that I am facing is, if the user opens register.php and activate.php on the same device, for example computer, then UPDATE Users SET active = 1 is working ok, meaning field 'active' updates value with 1.
But, if the user opens register.php on computer, and activate.php on mobile phone, then field 'active' gets updated with 1 and then after a second, it reverts to 0.
What is the problem?
Is mysql connection related to PHP session? Maybe locking issue?
How to solve this? |
|
| Back to top |
|
mraddi
Joined: 27 Jun 2016
Posts: 152
Location: Schömberg, Baden-Württemberg, Germany
|
| Posted: Wed 22 Sep '21 15:37 Post subject: |
|
|
Hello Mox,
might be a transaction but no COMMIT for it. And as your PHP-script closes the connection the modification is reverted. But this is just a guess...
From my point of view it is not a locking issue because in this case your script would either wait until the lock is not locked anymore or will fail.
Regarding your SQL-statements: please sanitize your input to avoid SQL-injection-attacks - https://www.php.net/manual/de/pdo.prepare.php
See what might happen if you don't: https://xkcd.com/327/  |
|
| Back to top |
|
Mox
Joined: 14 Sep 2021
Posts: 8
|
| Posted: Wed 22 Sep '21 18:23 Post subject: |
|
|
I tried also manual COMMIT, but still the same issue.
It does update for a second, and then reverts, so COMMIT does occur, but why it reverts, is a puzzle to me.
| mraddi wrote: | Regarding your SQL-statements: please sanitize your input to avoid SQL-injection-attacks
|
I did, I ommited here for clarity sake. |
|
| Back to top |
|
|
|
|
|
|
