get mysql error text from sqlstate error code

Question

I am working on error handling of mysql queries that are written in a different page (let's say page A) than the page responsible of printing them (page B), for example:

SQLSTATE[42S02]: Base table or view not found

I want to get the error message text:

Base table or view not found

from the code:

42S02

idealy I am looking for a function like this:

get_sqlstate_error_text(42S02)

for info, I can't access the last query and I only have the error code

Answer 1

This is how stuff happens:

1. PHP submits a query to MySQL

2. Something breaks, MySQL gets an internal error code, e.g. 1146

3. MySQL populates the message template: Table '%s.%s' doesn't exist → Table 'shop.customers' doesn't exist

4. MySQL gets the mapped standard SQLSTATE that corresponds to the internal code: 42S02

5. Data is sent back to PHP and exposed through the API

We want to go the other way round:

1. PHP has as SQLSTATE (42S02) and wants an error message (Base table or view not found)

2. SQLSTATE maps to several internal errors:

   • Error: 1051 SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR) - Message: Unknown table '%s'
   • Error: 1109 SQLSTATE: 42S02 (ER_UNKNOWN_TABLE) - Message: Unknown table '%s' in %s
   • Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE) - Message: Table '%s.%s' doesn't exist

3. PHP is confused: three messages to choose from, with %s symbols, none of them the wanted message.

Not to mention that that userland SQL code is allowed to generate its own SQLSTATE:

mysql> SIGNAL SQLSTATE 'OMG00';
ERROR 1644 (OMG00): Unhandled user-defined exception condition

In short, there's no way to figure out message from code for two reasons:

1. There's no one-to-one correspondence between them
2. Message appears to be a custom application text, not a built-in message you can lookup

Unfortunately, all obvious solutions are explicitly banned:

• Store the error message
• Hard-code a key/value list will all possible values

... so the only thing left is to transmit the error:

http://example.com/show-error?txt=Base%20table%20or%20view%20not%20found

... but of course you can't do that because, sooner or later, someone will send out edited links as prank:

http://example.com/show-error?txt=Jimmy%20is%20an%20idiot

So now you need to encrypt (or at least digitally sign) the message. But you wonder if you're allowed to store the encryption key in a location where both scripts can reach it...

---

Alright, I admit I was being too melodramatic. A simple encryption/signature may possibly do the trick—I don't think that copying the secret key to both scripts goes against the rules.

Answer 2

With mySqli you can do this to handle error :

if (!mysqli_query($con,"INSERT INTO Persons (FirstName) VALUES ('Glenn')"))
  {
  echo("Error description: " . mysqli_error($con));
  }

With PDO try this :

echo "\nPDOStatement::errorInfo():\n";
$arr = $sth->errorInfo();
print_r($arr);
?>