CODEWITHSUNDEEP
phpsqliteselectinsert
mixkat
mixkat

Reputation: 3785

Simple PHP SQLite question

I am trying to implement a function that will insert a new entry in a database if a field with same name (as the one given) doesn't already exist. In particular I want to restrict duplicate usernames in a table.

The only way I could think was to run a select query and then if that doesn't return anything run the insert query. For some reason though I cant get it to work...

My db select Function

function getAllUsers($user)
{
    $stmt = $this->db->stmt_init();
    $stmt->prepare('SELECT username from users where username=? ');
    $stmt->bind_param("s", $user);
    $stmt->bind_result($username);
    $stmt->execute();
    $results = array();
    while($stmt->fetch())
    {
        $results[] = array('username' => $username);
    }        
    $stmt->close();        
    return $results;
}

My php code (this is in a different page)

foreach ($GLOBALS['db']->getAllUsers($_POST['username']) as $i)
{
    $results =  "".$i['username']."";       
    break;
}    
if(strcmp($results, "")==0)
{
    if($GLOBALS['db']->addUser($_POST['username'],$_POST['password']))
    {
            session_destroy();                
            echo "registerSucces";
    }
    else
    {
            session_destroy();
            echo "registerError";
    }
}
else
{
        echo "userNameExists";
}

Can you see whats wrong with this???

Thanks

Mike

Still cant find how to make the above code work but just in case someone needs this: A temporary simple solution is not to compare strings at all and instead have a counter in the foreach loop and then check that upon a desired number(0 in my case)...

Upvotes: 2

Views: 317

Answers (2)

Raceimaztion
Raceimaztion

Reputation: 9634

If you're using the username as the primary key in your tables, you can use the INSERT OR IGNORE command instead of checking to see if the username already exists.

If SQLite finds that an INSERT OR IGNORE command will conflict with an existing row in your table, it will simply ignore the command.

You can find out more stuff in the SQLite documentation for the INSERT command

Upvotes: 1

outis
outis

Reputation: 77400

SQLite supports the UNIQUE constraint. Simply declare a UNIQUE index on the column and check whether an INSERT fails.

Upvotes: 1

Related Questions