How do I change this script so when it finds a user exists in a MySQL database it updates and doesn't return the error message?

Question

This part of the script checks for multiple usernames, I want it to update the user found instead of ending with the user exists error message:

// Verify a users existance by username.  Function will fail if multiple usernames are encountered.
function userExists( $username ) 
{
    // set up prepared statement
    $stmt = $this->conn->prepare("SELECT * FROM $this->table WHERE $this->username_column = ?");

    // bind the parameters
    $stmt->bind_param("s", $username);

        // execute prepared statement 
    if(!$stmt->execute())
        {
            $this->lastError = "Error in ".__FUNCTION__.": Username provided has more then one (".$stmt->num_rows().") records associated with it where there should only be one record.";
            return false;
        }


    $stmt->store_result();

        // check to make sure a username doesn't have duplicate usernames
        if($stmt->num_rows() == 0)
        {
            $this->lastError = "Error in ".__FUNCTION__.": Username provided has more then one (".$stmt->num_rows().") records associated with it where there should only be one record.";
            return false;           
        }

        // return indicating success 
        return true; 
}

I'm not sure if this is needed to aid in the answer but this part of the script is used to add new users to the database:

function addUser( $username, $password ) 
{

    // encrypt password if set (default == enabled w/ sha1)
    if($this->use_encryption)
        $password = ($_SESSION['password']);
        $salt = "CHANGE-SALT"; 
        // Add some salt to the users password. 
        $salt .= $password; // The password is salted
        $password = $salt; // Change the password var to contain our new salted pass. 
        $password = md5($password);

    if($this->userExists( $username ))  
        die("ERROR USER EXISTS"); 


    // create sql   
    $sql = "INSERT INTO  $this->table ( $this->username_column , $this->password_column) VALUES (?, ?)";

    // set up prepared statement
    $stmt = $this->conn->prepare($sql);

    // bind the parameters
    $stmt->bind_param("ss", $username, $password);


     // execute prepared statement 
    if(!$stmt->execute())
        {
        $this->lastError = "Error in ".__FUNCTION__.": Supplied user/pass fields cannot be added.";
        return false;       
        }



    return true;
}

Any help at all with this would be greatly appreciated, thanks in advance.

Trevor

try REPLACE instead of UPDATE http://dev.mysql.com/doc/refman/5.5/en/replace.html — avisheks, May 09 '14 at 12:27
You are a new user, so all is forgiven, but please try to avoid the "here is a bunch of code, how do I make it do something else" kind of questions. That feels like you just want us to do your coding for you. A better format would be: "How do add a row or update if duplicate"-style question. For which btw answers are abundant.. — Damien Overeem, May 09 '14 at 12:31
I do apologise for the way I've just dumped the code in here. I've been messing with the script for over a week with no success. — Tjenks, May 09 '14 at 12:33

score 3 · Accepted Answer · answered May 09 '14 at 12:32

3

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Add Unique index to the username column;

REPLACE INTO user SET username=".$username." AND password=".$password;

http://dev.mysql.com/doc/refman/5.5/en/replace.html

answered May 09 '14 at 12:32

avisheks

1,178
10
27

Oh man, it really was that simple. I feel so dumb not thinking of REPLACE instead of INSERT! Script is working perfectly. Thank you so very much! Trevor – Tjenks May 09 '14 at 12:40
:) @Tjenks, nothing to feel bad, these things happen we simply miss-out simple things when we think so deeply – avisheks May 09 '14 at 12:47

How do I change this script so when it finds a user exists in a MySQL database it updates and doesn't return the error message?

1 Answers1