CODEWITHSUNDEEP
phpmysqlsqlpdo
argoneus
argoneus

Reputation: 1147

How to check for duplicate username and/or email?

I've been wondering how to check in a MySQL table if username and/or email are duplicates during registration. Here's what I've tried so far but to no avail:

$username = $_POST['username'];
$sq = $db->exec("SELECT * FROM `users` WHERE `username` = '$username'");
if ($sq->rowCount() > 0)
{
    $msg = "That username is already taken.";
    $error = true;
}

$email = $_POST['email'];
$sq = $db->exec("SELECT * FROM `users` WHERE `email` = '$email'");
if ($sq->rowCount > 0)
{
    $msg = "That email is already taken.";
    $error = true;
}

if (!error)
{
    //add to db
}

The error this gives is Call to a member function rowCount() on a non-object

Could you please help?

Upvotes: 2

Views: 3644

Answers (3)

discomatt
discomatt

Reputation: 195

To avoid a potential race condition, you should simply set your username and email fields as unique indexes.

You database engine will give you a specific error code when you try to insert a duplicate row, for MySQL, it's 1062.

If the query fails, check if the error number is that of a duplicate row, and display the message based on that.

At the very least, your columns should be unique indexes to prevent duplicates at the database level.

Upvotes: 2

DaveRandom
DaveRandom

Reputation: 88697

PDO::exec() returns an integer, so your above code will die with a fatal error along the lines of call to member function rowCount() on a non-object. What you should do for this is SELECT COUNT(*) to get the number of rows.

You can also use exceptions to aid the error handling process.

Like this:

// Ensure PDO will throw exceptions on error
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Turn emulated prepares off
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

try {

  // Check if username is taken
  $stmt = $db->prepare("SELECT COUNT(*) FROM `users` WHERE `username` = :username");
  $stmt->execute(array('username' => $_POST['username']));
  if ($stmt->fetchColumn() > 0) {
    throw new Exception("That username is already taken.");
  }

  // Check if email is taken
  $stmt = $db->prepare("SELECT COUNT(*) FROM `users` WHERE `email` = :email");
  $stmt->execute(array('email' => $_POST['email']));
  if ($stmt->fetchColumn() > 0) {
    throw new Exception("That email is already taken.");
  }

  // Username and email are free

} catch (PDOException $e) {

  // A database error occured

} catch (Exception $e) {

  // Either the username of email is taken

}

If the data in the column should be unique, it should have a unique index on it to prevent duplicate inserts, and speed up queries that search based on the values of that column.

Upvotes: 4

Mahmoud Aladdin
Mahmoud Aladdin

Reputation: 546

Please clarify the type of $db, assuming it to be sqllite3.

Check the documentation for SQLLITE3::exec() at http://php.net/manual/en/sqlite3.exec.php .. as far as it says, it only valid for (UPDATE, INSERT, DELETE) and returns boolean. So, your code will not probably run.

If according to your tag, you're using MySQL. I believe you need to look at mysql/mysqli extension, mysql_query() & mysqli_query(), or $db -> query() if $db is of type mysqli.

Upvotes: 0

Related Questions