You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax …

by Yang Yang on January 13, 2009

Scratching your head for a mystic error in your SQL query? No matter how you look into it, it just seems right and should by the God’s name work as you have wished. But it doesn’t and keeps pumping out annoying SQL syntax errors.

Don’t panic, I have a few tips on this that might make your life easier:

  1. Search on Google by your primary query syntax such as ‘SELECT FROM’ or ‘INSERT INTO’ to see how correct queries are written by others.
  2. Make sure you don’t have any typo of those table names and field names.
  3. Try quoting (or bracketing) table names and field names with
    ` `

    And values (user provided, changeable data) with

    ' '
  4. Try tidying and indenting your query in manners such as:
    $results = mysql_query(
    	"INSERT INTO `offers` (
    		`title`,
    		`subtitle`,
    		`description`,
    		`oprice`,
    		`sprice`
    	) VALUES (
    		'$title',
    		'$subtitle',
    		'$description',
    		'$oprice',
    		'$sprice'
    	)"
    );

    So that you could more easily spot the bugs.

  5. Make sure you don’t have a comma at the end of the last item. This happened to me quite a few times.
  6. Escape or prepare the string before committing the query.

If instead the querying script (possibly in php) giving zero results without any mysql errors at all, you have to remove any error suppression tokens such as ‘@’ and explicitly print out the error message with:

<?php
$error = mysql_error(); // PHP4
$error = $mysqli -> error; // PHP5
?>

So that you can debug the query.

Comments on this entry are closed.

Previous post:

Next post: