Unique mysql record

PHP
1

I have a mysql table (members) and want to get it so that when a new member is added, and the email address is already in the table Id get a message that the user already exists, and a new member would be added if the email address is unnique.

2

If you are doing this via standard query, it’s simple: Just run a SELECT COUNT(*) query WHERE email = ‘entered email’ and if the value is greater than 0, you know it is already in the database, then you can send the email - otherwise it isn’t, you can INSERT.

If you’re using Stored Procedures, you can put it all into one package and return data depending upon what course of action was followed.

3

If the email must be unique create a unique index on the email column. That way there’s no need to check the existence of the email. Just do the insert and check the result.

4

If using a database that will error after trying to insert a duplicate value for an indexed field, you’d have to make sure that the user sees only generic error messages (if any), then send the admin an email on a failed insert.

Checking first is more code, but doesn’t require error trapping.

5

The idea is to check for the duplicate key error and act accordingly. No need for a mail to the admin in this specific case. And no need to do in your code what the database will do for you.

6

Maybe change the query to INSERT IGNORE ??

7

is it ok if I do this when setting up the table?

CREATE TABLE members (
id SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(50),
email VARCHAR(50),
password VARCHAR(50),
type_id TINYINT,
created DATE,
display CHAR(1),
FOREIGN KEY (type_id) REFERENCES types(id),
UNIQUE KEY (email)
);

And since im using the PDO thing, heres the INSERT query,
[php]
…
try {

$sql = “INSERT INTO members (
name,
email,
password,
type_id,
created,
display
) VALUES (
:name,
:email,
:password,
:type_id,
:created,
1
)”;

$stmt = $dbh->prepare($sql);

$stmt->execute(array(
‘:name’ => $name,
‘:email’ => $email,
‘:password’ => $password,
‘:type_id’ => $type,
‘:created’ => $date_created
));
header(“Location: members.php”);

exit;
} catch(PDOException $e) {
echo 'Error: ’ . $e->getMessage();
}
[/php]

I think if the query fails(cause theres already the email in the table) the catch block would execute. Do I put an if statement in there or something or can I simply put
[php]
echo “the email address is already in use”;
[/php]

8

@Wolfshade,
On the contrary. Unless you are doing some sort of expensive table locking then you better be prepared to handle a sql duplicate record error. Someone from another request could add a record in between the check and the insert.

9

The catch block will catch all errors, so you should definitely look for the duplicate error specifically before printing the message, and log other errors as actual errors.

If the email is the only constraint in the table, then it would suffice to look for the constraint violation SQL state (23000). On a PDOException option, that value can be found in the getCode() method.

if ($e->getCode() == '23000') {
    echo "Email already taken!";
}
else {
    echo "An error has occurred. Please try again.";
    // Log the error.
}

If there are more constraints in the table, you would have to look for the duplicate key error, specific to the email key, in the error message, either in the $e->getMessage() value or the $e->errorInfo[2] value.

You would also have to give your unique key a name you can look for specifically in the error message. That’s a simple matter of adding the name into the UNIQUE KEY (email) line, changing it into something like UNIQUE KEY email_unique (email).

The error message for duplicate keys looks like: Duplicate entry '$1' for key '$2', where $1 is the value and $2 is the key name.

So if you have more than one unique key that need to be tested, you can either create a comparison string like that one, using the appropriate key name and values (which is fairly simple), or you can just test for them all in one go with a regular expression (which is a bit less simple.)

My approach would be something like:

$duplicatePattern = "/Duplicate entry '[^']*' for key '([^']+)'/";
if (preg_match($duplicatePattern, $e->getMessage(), $match)) {
    // $match[1] here is the first group in the pattern.
    // Groups are sub-patterns enclosed in parenthesis.
    // In this case that is the key name in the message.
    if ($match[1] == 'email_unique') {
        echo "Email already taken.";
    }
    else {
        // Any duplicate key errors you don't specifically search for
        // with an IF/ELSE IF clause will end here.

        echo "An error has occurred. Please try again.";
        // Log the duplicate key problem.
    }
}
else {
    echo "An error has occurred. Please try again.";
    // Log the error.
}
2 Likes
10

thanks, that worked great. You bring up a point. I want to see all errors , and how do you recommend I log em so I can review them once I get them?

11

The simplest method would be to just write the errors to a text file (located out of the public directory).

12

That would depend on how your application is set up. If it’s based on a decent framework, or is set up to utilize composer libraries, then I’d definitely recommend setting up something like Monolog, which offers various ways to store logs, including combinations of file storage methods, databases, and email.

If you’re looking for something simpler, you might want to check out the error_log() function.