dtjbcda841554
2014-01-23 21:00
采纳率: 0%
浏览 117
已采纳

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

This is from a profile update page; so if a person changes their email2 from tom@aol.com to tommy@aol.com 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?

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

3条回答 默认 最新

  • dongyikong6207 2014-01-23 21:16
    最佳回答

    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.

    评论
    解决 无用
    打赏 举报
查看更多回答(2条)

相关推荐 更多相似问题