2

I am trying to update the email in multiple tables for when a user wants to change his current email. The problem is how to update the email for tables that the user has no connection with yet. If a new user tries to change his email adress he will only have a connection with 2 of 4 tables so his email doesn't exist in the other 2.

So my question is if there is someway here for me to update the email in all tables that has the email inside it and skip the one without any trace of it inside.

My current attempt is to do this, but the issue here is that this user doesn't have a connection with the tables posts and votes yet. 

$update_email = $pdo->prepare("UPDATE users, votes, user_biography, posts SET
email = :email_new WHERE email=:email");
$update_email->bindParam(':email_new', $new_email);
$update_email->bindParam(':email', $email);
$update_result_email = $update_email->execute();
Emoless96
  • 103
  • 10
  • possible duplicate of https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query – gogaz Jan 01 '18 at 17:51
  • 3
    Why not try using a trigger on your primary (users) table to update the others if the email address changes? It always helps to have a single source. – kurt Jan 01 '18 at 17:51
  • 2
    This sounds like poor normalisation, consider only keeping a reference to the email in the "users" table, and have your other tables refer to this. – mquinn Jan 01 '18 at 18:18
  • @kurt This sound like it could work, where would i place this "trigger" and how should i write it... have never used any triggers i think. – Emoless96 Jan 01 '18 at 19:58
  • @mquinn This sound like the proper way to do it, but im unsure of how to do this...I still have a lot of things that checks against the other tables for rows containing the current logged in user with his email so i will still need the email in all tables? Or is there a way for the email in all other tables to have a reference to the main email in the user table so all other changes if the main changes? – Emoless96 Jan 01 '18 at 22:52
  • @Emoless96 Anything you have that is checking the user's email address should look for this in the 'users' table in the 'email' column. Whatever is checking this should look up the row in 'users' table using the PRIMARY KEY in a "Foreign Key relation". A user's email address should only appear in your database once in this sort of schema, as this is what allows for greater data integrity. See more on this here: https://en.wikipedia.org/wiki/Data_integrity#Types_of_integrity_constraints – mquinn Jan 02 '18 at 13:51

0 Answers0