具有“IF”条件的MySQL“UPDATE”语句

Question

I'm trying to create a MySQL UPDATE statement with an IF condition.

I would like to update the user's particulars if the email and username are not duplicate or found in database.

I'm stuck with this code:

<?php

include "connection.php";
$user   = $_REQUEST['user'];
$em     = $_REQUEST['email'];
$id     = $_REQUEST['id_user'];

//Check Email in Database
$query = mysqli_query($con,"SELECT username, email from `user` where id_user = '$id'");
$result = mysqli_fetch_object($query);

if (strtolower(trim($query->email)) == strtolower(trim($em))
    || strtolower(trim($user->username)) == strtolower(trim($user))
) {
    //next to condition  username
} else {
    $data_email = mysqli_query($con,"select email from user where em='".$em."'");
    $total_email = mysqli_num_rows($data_email);

    if($total_email > 0) {
        echo "Email Not Available";
    }  else {
        //next to condition username
    }
}

//Check Username in Database                        
$data_us_user = mysqli_query($con,"select username from user where id_user='".$id."'");
$us_user = mysqli_fetch_object($data_us_user);
if (strtolower(trim($us_user->username))==strtolower(trim($user))) {
    //next to query update
} else {
    $data_username = mysqli_query($con,"select username from user where username='".$em."'");
    $total_username = mysqli_num_rows($data_username);
    if($total_username > 0) {
        echo "Username Not Available";
    } else {
        //next to query update
    }
} else {
    //Finally Query Update
    mysqli_query($con,"update user set username='".$user."',em='".$em."' where id_user='".$id."' ");
    echo "OK";
}

Answer 1

The following PHP script will check for

• IF the user's updated email and updated username are the same
• IF the user's new email and username is already in use by another user
• IF both conditions are not met, the user's details will be updated

PHP code:

<?php

include "connection.php";
$user   = $_REQUEST['user'];
$em     = $_REQUEST['email'];
$id     = $_REQUEST['id_user'];

//Getting user' details in database
$query = mysqli_query($con, "SELECT username, email from `user` where id_user = '$id'");
$result = mysqli_query($query);

//Query to find if email exists
$query2 = mysqli_query($con,"SELECT `email` from `user` WHERE em = '$em'");
$result2 = mysqli_num_rows($query2);

//Query to find if username exists
$query3 = mysqli_query($con,"SELECT `email` from `user` WHERE id_user = '$user");
$result3 = mysqli_num_rows($query3);

while ($row = mysqli_fetch_row($result)){
    list($userfromdb, $emfromdb) = $row;
}

if (strtolower(trim($userfromdb)) == strtolower(trim($user))){

    //will return true if user's username is the same before updating
    echo 'Username cannot be the same!';

} else if(strtolower(trim($emfromdb)) == strtolower(trim($em))) {

    //will return true if user's email is the same before updating
    echo 'Email cannot be the same!';

} else if($result2 > 0) {

    echo "Email Not Available";

} else if($result3 > 0) {

    echo "Username Not Available";

} else {

    //Finally Query Update
    mysqli_query($con, "UPDATE `user` set username = '$user',em = '$em' WHERE id_user = '$id'");

    //check if row updated successfully
    $result4 = mysqli_affected_rows($con);

    if ($result4 > 0) {
        echo "Updated details successfully";
    } else {
        echo "An error occurred while updated details.";
    }

}

?>

This should work, thanks!