Queries That Return Strange Results

A problem many MySQL users come across, particularly when accessing the database using PHP, Perl, or Java, is that a script or application does not give the intended results. There are many places for a problem to occur:

• In connecting to and selecting a database

• In executing the query

• In the SQL of the query itself

• In the MySQL results of the query

• In how the query results are handled by the program

(If you're not using a programming language, you'll be limited to just the third and fourth issues.) To debug such situations, you need to identify the actual cause of the problem.

To debug query problems:

1.	If using a programming language to create an SQL query, print out the query so that you know exactly what is trying to be executed. Frequently, the program is not assembling a query as you would expect.
2.	Run the query on the database via another method, such as using the mysql client. Doing so will validate both the SQL and the results.
3.	In a programming language, use the methods available for reporting any MySQL errors.
4.	If the query results are bad, rewrite the query in its most basic form. Then keep adding dimensions back in until you discover which clause is causing the problem.
5.	Pay attention to what versions you are using (of MySQL and the languages) and reread any applicable manual pages to see if this is a version-specific issue.