为什么我的MySQL查询在PHP条件检查之前执行？

Question

I'm learning to create conditional event where sql checkout data where is exists before inserting data so they don't conflicted.

i've tried using mysql row check in php then check if query empty before i tried to validate the query executed properly.

also trying to close db connection when conditional satisfied but it worthless anyway.

$user = addslashes(strtolower($usr));
$mail = addslashes(strtolower($mail));
$pass = md5(addslashes($pwd));

$check = $db->query("SELECT EXISTS(SELECT * 
                                   FROM `users`
                                   WHERE LOWER(`username`) = LOWER('$user')
                                      OR LOWER(`email`) = LOWER('$mail'))");

if (!$check) {
    $db->close();
    return false;
} else {
    $sql = "INSERT IGNORE INTO `users` (`username`, `password`, `email`)
                   VALUES ('$user', '$pass', '$mail')";
    $query = $db->query($sql);
    $db->close();
    return true;
}

I'm expecting it execute my queries while data was empty and return false while data has been existed.

Answer 1

Your main issue is that $check will always be a truthy value, so long as the query never fails. If the query returns 0 rows, it is still a true object.

You should instead check if there were any values returned. You can also simplify the query quite a bit, given that MySQL is case-insensitive, and you don't need to check if the result exists. Using a prepared statement, the code would look like this

$stmt = $db->prepare("SELECT username FROM users WHERE username = ? OR email = ?");
$stmt->bind_param("ss", $usr, $mail);
$stmt->execute();
$check = $stmt->fetch();
$stmt->close(); 

// True if the user exists 
if ($check) { 
    return false;
} else {
    $stmt = $db->prepare(" INSERT INTO users (username, password, email) VALUES (?, ?, LOWER(?))");
    $stmt->bind_param("sss", $usr, $pass, $mail);
    $stmt->execute();
    $stmt->close();
}

That said, you should not use md5() for passwords - use password_hash() with password_verify() instead.