Relying on MySQL error codes in code

While the error codes themselves might not change, there is still a good reason not to rely on them outside of your database class; a good reason is that relying on the numeric error codes, outside of the database class, will just introduce a not-so-obvious dependency to our code.

This is going to be a problem if suddenly we decide to switch databases, since all these error codes might no longer match, and then you may have an even larger refactoring project on your hands.

So, instead of returning the error code from our database client, we might want to handle the errors from the client class itself; and then only handle the specific error codes that our application needs to handle. The places in my code that I actually need to know the cause as to why a given query is failing are very few, and everything else can be handled simply by returning false and then checking for this in my code. I.e:

if (false === $stmt) {
  throw new Exception("The query failed for unknown reasons.");
}

Relying on constants instead of numeric error codes

In PHP we are encouraged to rely on predefined constants rather than error codes, at least when it comes to PHP's own errors, such as E_ERROR and E_WARNING, since the integer values might change. But this does not necessarily translate to MySQL or MariaDB, and PHP also does not have predefined constants for MySQL errors; so what can we do instead?

As I already discussed in the previous section, we do not really need our application to know about every single MySQL error — there are currently more than a thousand of them! No. Instead we will just define constants for the ones we need.

Some of these error codes do also not appear to be something that can be triggered from PHP applications anyway.

Defining global constants is probably a bad idea, since it will create unknown dependencies for your classes. Instead you might consider defining local constants, inside of the PHP database client class.

Defining constants inside of classes

Defining relevant MySQL error codes as constants has the advantage that you can just update them in one place, if they ever do change. But, instead of defining global constants, which is bad, we can just define local constants inside a PHP object; to do this, we use the const keyword:

class mysqli_client
{
    const ER_DUP_ENTRY = 1062;

    function example_method() {
        echo  $this::ER_DUP_ENTRY . "\n";
    }
}

We can use perror 1062 in a terminal to find a good name for our constant, as this hints at what MySQL is calling it themselves. For example, the 1062 code is returned when a unique or primary key is duplicated in a table; if we check it in a terminal we get:

$ perror 1062
MySQL error code MY-001062 (ER_DUP_ENTRY): Duplicate entry '%-.192s' for key %d

To access a constant from the outside of the class, we would write like this:

echo $class::ER_DUP_ENTRY."\n"; // As of PHP 5.3.0

So, in order to check if a query returned the ER_DUP_ENTRY code, we can do like this:

$stmt = $db->prepared_query("insert into some_database (id, some_column) values(?, ?)",
 [$id, $some_value]
);

if ($db::ER_DUP_ENTRY === $stmt) {
  echo 'The ID already exists in the database. Nothing was inserted.';
  exit();
} elseif (false === $stmt) {
  throw new Exception("The query failed for unknown reasons.");
}

Tell us what you think: