BizWedsCode BizWedsCode - 1 month ago 8
MySQL Question

MySQL error when query is wrong VS when input is wrong

Is there a way to distinguish between error messages returned by MySQL when the query is formed incorrectly VS when the user input is wrongly entered?

For example:

//WRONG QUERY
try {
INSERT into table_name ('clm1','clm2') values('val1','val2'); //BAD query
}
catch (PDOException $e) {
echo $e->getMessage(); //display the error message returned
}


VS.

//wrong user input

try {
INSERT INTO table_name ('unique_clm1') value('duplicate_value_from_user');
}

catch (PDOException $e) {
echo $e->getMessage(); //display the error message returned
}


REASON:

If there is an error in query written by the developer; the error needs to be displayed as a raw MySQL error for debugging......while a bad user entry needs to be shown as an error message to the user without disrupting the normal flow of code.....

Answer

You have a few options:

1. Use INSERT IGNORE

Using IGNORE means that the query will not fail on duplicate key. So failure means that there is a problem in the query. To know if there was a duplicate all you need is to get the number of affected rows. 0 means nothing was inserted (so there was a duplicate value). 1 means a row was inserted.

2. Use tests to catch bad queries

Bad queries in the code stay bad until fixed. They will not be OK for some users and then go bad for others. So if you have some tests in place, the tests will catch BUGS while the error handling in your code will catch "Something went wrong" that might be a duplicate entry, DB connectivity etc.

3. Read the error message

In your code, you can read the content of $e, and handle accordingly. If $e contains duplicate key or the relevant error code, notify the user. Otherwise log the error as something else (not necessarily bad query - DB connectivity is another good example for an error that can come up in runtime).