PHP to mySQL check if user exists

问题:

I have a script that updates/creates user from an iOS device. Now i want to have the script also check if the user already exists in the database. I am going to restrict this to username for now, so no more than ONE unique username may exist. I have an if-statement in my PHP but i cannot get it to work - help please :).

    <?php

    header('Content-type: application/json');
    if($_POST) {
        $username   = $_POST['username'];
        $password   = $_POST['password'];

        if($username && $password) {

                $db_name     = 'dbname';
                $db_user     = 'dbuser';
                $db_password = 'dbpass';
                $server_url  = 'localhost';

                $mysqli = new mysqli('localhost', $db_user, $db_password, $db_name);

            $userexists = mysql_query("SELECT * FROM users WHERE username='$username'"); 

            /* check connection */
            if (mysqli_connect_errno()) {
                error_log("Connect failed: " . mysqli_connect_error());
                echo '{"success":0,"error_message":"' . mysqli_connect_error() . '"}';
            }

            if(mysql_num_rows($userexists) != 0) { 
                echo '{"success":0,"error_message":"Username Exist."}';
            } 

            else {

                $stmt = $mysqli->prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
                $password = md5($password);

                $stmt->bind_param('sss', $username, $password, $email);

                    /* execute prepared statement */
                $stmt->execute();

                if ($stmt->error) {error_log("Error: " . $stmt->error); }

                $success = $stmt->affected_rows;

                    /* close statement and connection */
                $stmt->close();

                    /* close connection */
                $mysqli->close();
                error_log("Success: $success");

                        if ($success > 0) {
                        error_log("User '$username' created.");
                        echo '{"success":1}';
                        } 
                        else {
                        echo '{"success":0,"error_message":"Username Exist."}';
                        }
            }

        } 
        else {
            echo '{"success":0,"error_message":"Passwords does not match."}';
        }
    } 
    else {
        echo '{"success":0,"error_message":"Invalid Username."}';
    }
}   
else {
    echo '{"success":0,"error_message":"Invalid Data."}';
}
?>

回答1:

You could SELECTthe table before trying to insert username. If it already exists (= you have a result) you dont simply insert.

Better yet, use ON DUPLICATE IGNORE or something like that.