Discussion on: MySQL in PHP - how to deal with databases

[neoan]

So in your scenario you would want the user to have multiple verified emails but authentication only works with the latest?
As mentioned in the post, I would normally have a column 'insert_date' in every table set up to insert the current timestamp on creation as well as a 'delete_date' defaulting to NULL.

In such a case I would modify my authentication logic accordingly ( I currently handle it like this: neoan3 user-model ).

Assuming the user passes in an email and a password, I would

1. retrieve the row in user_email by the provided email

2. use the retrieved info (if existing) to query again by user_id to ensure the passed in email address was the latest one

3. in success case, proceed with regular password verification

The code could look like this:

$assumedUserInput = ['email' => '[e-mail]', 'password' => '123456'];

// 1. find user-id (using easy here instead of SQL)
$existingUser = Db::ask('user_email.*',['email' => $assumedUserInput['email'], 'delete_date' => '']);

if(empty($existingUser){
    // throw error, return false, however you handle it
}
// 2. get latest email of user
$latestEmail = Db::easy('user_email.email', ['user_id' => $existingUser[0]['user_id'], 'delete_date' => ''],['orderBy'=>['insert_date','DESC'], 'limit'=>[0,1]]);

/**
* SQL equivalent:
* SELECT email FROM user_email WHERE user_id = ? ORDER BY insert_date DESC LIMIT 1
*/

if($assumedUserInput['email'] != $latestEmail[0]['email']){
    // throw error, return false, however you handle it
}

// then, authenticate (in our example we did not talk about passwords. Please have a look at the provided link if you have questions here)

DISCLAIMER
I am on the go and have not tested the above code. Also, sorry for missing line breaks here and there.

[Ted Ngeene]

Aah it's okay. I'll try out the code and your package to see if it does work