Im sort of confused with PDO

[lurtnowski]

I’m trying to create a nice registration thing on my site.
I have a simple users mysql table where i have the email record being a unique key

CREATE TABLE users (
…
UNIQUE KEY (email)
);

Since I only want to add users with a unique email, I thought this would work…

<?php

try {
 
$sql = "INSERT INTO users (
...
  ) VALUES (
...
  )";
  
$stmt = $dbh->prepare($sql);

//BIND PARAMETERS & DO QUERY
$stmt->execute(array(
':name' => $name,
':email' => $email,
':password' => $password,
':created' => $date_created,
':ip' => $user_ip
));

echo 'User added'';
		
} catch(PDOException $e) {

   if ($e->getCode() == '23000') {
  //is this the code I get if the email field is a DUPLICATE?	   
  echo 'That email is in use';   
		  
   } else {
   //What does this do?
   echo $sql . "<br>" . $e->getMessage();
   }
}
?>

[Pepster64]

I for one would never tell a person that an email is in use. I would just say that there’s something wrong and if the problem persist send an email at such and such address or something like that.

That only prevents if two people are just so happening to be entering the same email at the same time. If that is happening then there is something funny going on.

A better way to do that in my opinon would to do something like the following:

    $query = "SELECT 1 FROM users WHERE email = :email";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(':email', $email);
    $stmt->execute();
    $row = $stmt->fetch();
     return $row; // If it finds an email address it will be true:

[lurtnowski]

I thought the UNIQUE KEY (email) would prevent the same email from being used more than once. Wouldn’t that prevent 2 people from entering the same email no matter when?
Your code is a little confusing to me as I thought id be inserting a row and not selecting one (can you explain it a little more?
Thanks…,.

[chorn]

if you want them to be unique, prepare the DB this way. but i do not see what problem you have (other then the syntax error).

[droopsnoot]

Have you set PDO into Exception mode? I don’t think it will throw an exception unless you specifically set it to do so: http://php.net/manual/en/pdo.error-handling.php

@lurtnowski

Your code is a little confusing to me as I thought id be inserting a row and not selecting one (can you explain it a little more?

I think that code is intended to run first - first check if the email is already in use, if it us, then handle that situation. By the time your insert query runs, you already know the email doesn’t exist in the table.

[colshrapnel]

@lurtnowski

/What does this do?

You should re-throw this exception instead of echoing it out.

} catch(PDOException $e) {
   if ($e->getCode() == '23000') {
      //is this the code I get if the email field is a DUPLICATE?	   
      echo 'That email is in use';   
    } else {
        throw $e;
   }
}

As of the code, it varies, as far as I know. So there is another way - to use INSERT IGNORE and then check affected rows

$sql = "INSERT IGNORE ...";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    ':name' => $name,
    ':email' => $email,
    ':password' => $password,
    ':created' => $date_created,
    ':ip' => $user_ip
));
if ($stmt->rowCount()) {
    echo 'User added';
} else {
    echo 'That email is in use';
}