有没有办法在SQL Update语句中添加PHP IF语句

Question

This is from a profile update page; so if a person changes their email2 from [e-mail] to [e-mail] I want the email2_verified to be reset to unverified

My code is..

    $updateSQL = sprintf("UPDATE user SET uname=%s,email=%s, email2=%s  WHERE `uid`=%s && `pass_code`=%s",
            GetSQLValueString($_POST['uname'], "text"), 
            GetSQLValueString($_POST['email'], "text"),
            GetSQLValueString($_POST['email2'], "text"),
            GetSQLValueString($_POST['uid'], "int"),
            GetSQLValueString($password, "text"));

I also have a field email2_verified; The goal is.. if email2 has changed I want it unverified; Is there a way to ad an if statement in an sql statement that would work like the following code..?

    $updateSQL = sprintf("UPDATE user SET uname=%s,email=%s, email2=%s  
    WHERE `uid`=%s && `pass_code`=%s,email2_verified=%s",


    if($_POST['email2']){ // **has not changed** 
    GetSQLValueString($_POST['uname'], "text"),
    GetSQLValueString($_POST['email'], "text"), 
    }        

    if($_POST['email2']){ //**has changed**                
     GetSQLValueString("unverified","text"),
    }

    GetSQLValueString($_POST['email2'], "text"),
    GetSQLValueString($_POST['uid'], "int"),
    GetSQLValueString($password, "text"));

I'm guessing (the most laymen way is)I may have to create a select statement that runs before the update statement and

    if htmlentities($post['email2']) <> $row['email2']
            use update statement 1 
    else
            use update statement 2

is that the best way or is there a php function I am unaware of that can do it on the fly?

Answer 1

You can create a trigger in MySQL.

DELIMITER //

CREATE TRIGGER detect_email_change BEFORE UPDATE ON user FOR EACH ROW
  BEGIN
    IF NEW.email2 != OLD.email2 THEN
      SET NEW.email2_verified = "unverified";
    END IF;
  END;//

DELIMITER ;

Then you just update the email2 field and the trigger will take care of the rest.