CODEWITHSUNDEEP
phpmysql
ZStoneDPM
ZStoneDPM

Reputation: 341

Is there a way to add a PHP IF statement in an SQL Update Statement

This is from a profile update page; so if a person changes their email2 from [email protected] to [email protected] 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?

Upvotes: 1

Views: 463

Answers (3)

Kouber Saparev
Kouber Saparev

Reputation: 8115

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.

Upvotes: 2

Patrick Maciel
Patrick Maciel

Reputation: 4944

Just do that (I'm using your code):

<?php   

  if ($_POST['email2']) { 
    // has changed

    $sql = "UPDATE user SET uname=%s,email=%s, email2=%s " .  
           ",email2_verified=%s " . 
           "WHERE `uid`=%s && `pass_code`=%s";

    $updateSQL = sprintf(
      $sql,
      GetSQLValueString($_POST['uname'], "text"),
      GetSQLValueString($_POST['email'], "text"),
      GetSQLValueString("unverified","text")
      GetSQLValueString($_POST['email2'], "text"),
      GetSQLValueString($_POST['uid'], "int"),
      GetSQLValueString($password, "text")
    );



  } else {

    $sql = "UPDATE user SET uname=%s,email=%s, email2=%s " .  
           "WHERE `uid`=%s && `pass_code`=%s";

    $updateSQL = sprintf(
      $sql,
      GetSQLValueString($_POST['uname'], "text"),
      GetSQLValueString($_POST['email'], "text"),
      GetSQLValueString($_POST['email2'], "text"),
      GetSQLValueString($_POST['uid'], "int"),
      GetSQLValueString($password, "text")
    );

  }

If you want a SQL solution, use the Kouber Saparev answer.

Upvotes: 0

xate
xate

Reputation: 6379

You could reach this by working with prepared statements.

Means: you would create a query with placeholders. Then you can write something like this:

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");

if(condition) {
    $stmt->bind_param('sssd', $code, $language, $official, $percent);
} else {
    $stmt->bind_param('abc', dbc, $language2, $officia2l, $percent2);
}
$stmt->execute;

check this link for more informations about statements:

https://php.net/manual/de/mysqli-stmt.bind-param.php

Upvotes: 2

Related Questions