0

I'm currently coding the forgot password option in PHP, and I'm having trouble with an SQL query.

My forgot password form has a field to enter the email address. When someone enters an email and hits the "Request New Password" button, it's supposed to create a token and a token expiration time and pass it to a database table. The email and the token columns are defined in two different tables (connected with a foreign key).

At first, I had all the columns defined in the [users] table, so storing the token details under the given email was easy to figure out. But I got stuck when I split the table into two. I can't figure out how to write the SQL query for that.

Here's what the two tables look like:

[users] => id, username, email, password   
[persistences] => id, user_id (foreign key), token, expires_at

This is what the forgot_password() function looked like when all the columns were defined in the [users] table:

public function forgot_password ($token, $email) 
{
    $sql = "UPDATE users SET token = :token, token_expire = DATE_ADD(NOW(), INTERVAL 10 MINUTE) WHERE email = :email";    
    $stmt = $this->conn->prepare($sql);    
    $stmt->execute(['token' => $token, 'email' => $email]);    
    return true;    
}

How should the SQL query be changed to achieve the same goal, but when the columns are in separate tables?

K Deven
  • 27
  • 1
  • 4
  • i recommend to check https://stackoverflow.com/a/9417254/9240674. As stated, ensure to include `users.id = persistence.user_id` in WHERE clause. – mottek Apr 18 '22 at 20:41
  • Instead of `WHERE clause joins` use a typical join with the update, which is possible with MySQL and specify the join there instead. – Paul T. Apr 19 '22 at 00:53

0 Answers0