1

I'm using OO MySQLi to work with a register form. I made the username column unique in my table and noticed that $stmt will throw an error when the code tries to use the execute() method, if a duplicate username is given.

I'd like to know how I can differentiate between other errors and a duplicate username error in the error detection? At the moment I'm essentially doing this:

$stmt->execute();
if($stmt->error)
    die();

The problem is that if execute() fails for another reason (instead of duplicate username), I'll most likely want to log it, but I don't want to log everytime a user tries to register with a taken username. The simplest (and probably not a very good) solution I came up with right away was to analyze the $stmt->error message. If the problem is about duplicate usernames, the string will be "Duplicate entry 'value' for key 'column'", so I could just check if the strings first words are "Duplicate entry ". But I guess that'd be prone to errors.

Any better suggestions?

nvbmnbvm
  • 23
  • 6

2 Answers2

0

You could run a statement before the insert to check if the username already exists in the table and if it doesn't then run the insert statement, else log it/throw errors as needed.

Pez Cuckow
  • 14,048
  • 16
  • 80
  • 130
  • 1
    I'd rather not run a statement for such a small thing. I'll do that if nothing else works, because it seems like a waste of resources. – nvbmnbvm Aug 06 '14 at 12:24
  • I would be very surprised if you noticed any such impact, especially if the username column is indexed. – Pez Cuckow Aug 06 '14 at 12:25
  • 2
    Why is it a waste of resources to make check if a username exists? Relying on the exception is probably a bigger waste of resources. – tbddeveloper Aug 06 '14 at 12:26
  • 1
    @Hammerstein any kind of source to back that claim up? – nvbmnbvm Aug 06 '14 at 12:37
0

A mysql_statement has a $sqlstate property, taking values documented in the MySQL manual.

If you work with exceptions, mysqli_sql_exception also has two extra relevant properties:

protected string $sqlstate ;
protected int $code ;

You want to check that error = 1062 or sqlstate = '23000'.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • 1
    As suggested by [Pez Cuckow](http://stackoverflow.com/a/25160307/1446005) and [Hammerstein](http://stackoverflow.com/questions/25160267/properly-checking-mysqli-oop-errors#comment39169947_25160307]), I wouldn't rely on exceptions for this use case. Exceptions should be reserved to exceptional cases that you knowingly decide not to handle yourself. (see also: [When to throw an exception](http://stackoverflow.com/q/77127/1446005)) – RandomSeed Aug 06 '14 at 13:37