MySQL - Custom error in WHERE clause

Question

I'm developing a PHP script with MySQL database connection. The problem I'm having is my queries have to be optimized because I handle a lot of information and, in order to do so, I'm making large queries.

To write an example I won't write a large query but one for you to be able to see what I need:

SELECT db1.name, db1.pass, db1.user_id, db2.datetime, db2.user_id 
FROM database1 db1, database2 db2 
WHERE db1.name = 'name' 
AND db1.pass = 'pass' 
AND db1.user_id = db2.user_id

What I need is to know if the query fails because the name and pass are incorrect, or because the user_id is not equal in both tables.

Basically, I need to return an Error Number or something like that in the query to be able to identify the error in the query and to output a custom message: "Your user and password are incorrect." or "You don't have permissions in database2."

Thanks in advance!

I believe that SQL is not the place for this kind of business logic. However, I think it's possible through the use of stored procedure: it's probably not what you want though. — Xaqq, Jul 24 '13 at 16:01
I agree with @Xaqq you should'nt be doing this in SQL. Normaly you would do the validation on the client side of the aplication not the server (or in this case the DB). But in some case I understand there might be exceptions. A store procedure would do the trick. — Sebastien, Jul 24 '13 at 16:06
But even with a store procedure this would still not be optimal. The way I see it, you would have to call a Store Procedure that go trought the one record and validate both condition, and this for the entire record set of both DB. Not the fastest nor the best way to achieve your goal. — Sebastien, Jul 24 '13 at 16:10
I'd recommend doing separate queries to check for correct login, permissions, and whatever else *if* you need the error codes - that way you'll know exactly where it went wrong. You may be able to simply say "something went wrong" and do all the checks in one, but without upper-level programming you wont be able to do error checking there. [Here's some tutorials on MySQL Stored Procedures](http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx). Maybe [MySQL subqueries](http://dev.mysql.com/doc/refman/5.0/en/subqueries.html) could be of use? — DACrosby, Jul 24 '13 at 16:10
Thank you all for the answers! @DACrosby This way is the way I'm doing it right now. The problem is that sometimes I have to request a lot of information from a lot of tables, and MySQL is better at performance if I make only a query (or two) rather than a lot of them - just the way it's done right now. I'll take a look, though, to Stored Procedures. Thank you again! — Unapedra, Jul 25 '13 at 08:06
I've been testing around and definitely a stored procedure should do the trick with some IFs and CASEs. Thanks again for the answers! — Unapedra, Jul 25 '13 at 16:11

spencer7593 · Accepted Answer · 2013-07-24T17:56:19.947

The SQL SELECT statement is not designed to return an "error number" or "error message", when no rows are returned because a particular predicate was not satisfied.

(By "query fails" and "error in the query", I assume you are referring to the query not returning any rows because the predicates were not satisfied (i.e. whether any rows "matched" the WHERE clause), rather than an actual MySQL error.)

Given the example query in the question, that could be rewritten, to do this kind of check.

For example:

SELECT IF(db1.name IS NULL
         ,'Your user and password are incorrect.'
         ,IF(db2.user_id IS NULL
           ,'You don''t have permissions in database2.'
           ,''
       ) AS message
     , db1.name
     , db1.pass
     , db1.user_id
     , db2.datetime
     , db2.user_id 
  FROM (SELECT 1 AS i) i
  LEFT
  JOIN database1 db1
    ON db1.name = 'name' 
   AND db1.pass = 'pass' 
  LEFT
  JOIN database2 db2
    ON db1.user_id = db2.user_id

But the fact you can write such a statement does not mean this is a good approach. (There has to be some name for this anti-pattern.)

Thanks for this answer! Maybe I'll discard this way because of the handicaps I'll get compared to other ways I've tested, but your way would be the one I'd use, then checking if message is '' or is not '' to know if there has been some kind of error. Thanks again. — Unapedra, Jul 25 '13 at 08:28

MySQL - Custom error in WHERE clause

1 Answers1