MySQL Error 1064: You have an error in your SQL syntax
When working with programs that have a database, you face the possibility of having database errors as well as regular php errors. This guide explains what the 1064 error is, some likely causes, and general troubleshooting
Causes for the 1064 error
This error message may seem cryptic at first. That is because it is a general MySQL error pointing to a syntax error in the SQL Query statement. This error can have multiple causes which are explained below.
Using reserved words
Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.
How to fix it:
Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks (“`”), this is usually the button just to the left of the "1" button on the keyboard. The code block below shows how the code will need to look in order to run properly.
Inserting incompatible data types into a field
One of the most common causes for the 1064 error is when a SQL statement is trying to insert a value for a data column that is of a different type. Simply put, if you are trying to place a number into a text field, a date in a number field, or text into a numeric field you will get the error.
Below is a simple example of a query that is trying to insert text into a numeric field, meaning the table structure has the id field set to accept numbers.
How to fix it:
Since the id field is set as an INT (integer: numeric, whole number) field, the word 'none' is not correct. Changing the value to a number will fix the issue. If you are unsure what data type the columns are, read our article on How to determine the data type of a data column in phpMyAdmin.
Below is the syntax for the correct query statement.
As you can see there is more than one cause for this type error within MySQL code. This list will be updated as more specific instances are reported.