1064 -You have an error in your SQL syntax check the manual that corresponds to your MySQL server version

Question

I m new to PHP and MySQL and ran into a little trouble with a learning project I m working on   Whenever I try to create a table  CREATE TABLE transactions  id int NOT NULL AUTO INCREMENT  location varchar 50  NOT NULL  description varchar 50  NOT NULL  category varchar 50  NOT NULL  amount double 10  NOT NULL  type varchar 6  NOT NULL   notes varchar 512   receipt int 10       I get the following error message       1064 - You have an error in your SQL syntax  check the manual that corresponds to your MySQL server version for the right syntax to use   near    NOT NULL  type varchar 6  NOT NULL  notes varchar 512     receipt int 10     at line 6     Here is some info on what I m working with   Server type  MySQL  Server version  5 5 32 - MySQL Community Server GPL  phpMyAdmin  4 0 4 1  latest stable version  4 1 7   I ve spent a day knocking my head against the wall over this and now I think its time to ask for help I was wondering if anyone can tell me what I m doing wrong

User · Answer

Remove the comma  receipt int 10    And also AUTO INCREMENT should be a KEY  double datatype also requires the precision of decimal places so right syntax is double 10 2

User · Answer

Rule 1  You can not add a new table without specifying the primary key constraint not a good practice if you create it somehow   So the code   CREATE TABLE transactions   id int NOT NULL AUTO INCREMENT   location varchar 50  NOT NULL   description varchar 50  NOT NULL   category varchar 50  NOT NULL   amount double 10 9  NOT NULL   type varchar 6  NOT NULL    notes varchar 512    receipt int 10    PRIMARY KEY id      Rule 2  You are not allowed to use the keywords words with predefined meaning  as a field name  Here type is something like that is used commonly used with Join Types   So the code   CREATE TABLE transactions   id int NOT NULL AUTO INCREMENT   location varchar 50  NOT NULL   description varchar 50  NOT NULL   category varchar 50  NOT NULL   amount double 10 9  NOT NULL   transaction type varchar 6  NOT NULL    notes varchar 512    receipt int 10    PRIMARY KEY id      Now you please try with this code  First check it in your database user interface I am running HeidiSQL  or you can try it in your xampp wamp server also and make sure this code works  Now delete the table from your db and execute the code in your program  Thank You

User · Answer

I see two problems   DOUBLE 10  precision definitions need a total number of digits  as well as a total number of digits after the decimal   DOUBLE 10 8  would make be ten total digits  with 8 allowed after the decimal   Also  you ll need to specify your id column as a key    CREATE TABLE transactions   id int NOT NULL AUTO INCREMENT   location varchar 50  NOT NULL   description varchar 50  NOT NULL   category varchar 50  NOT NULL   amount double 10 9  NOT NULL   type varchar 6  NOT NULL    notes varchar 512    receipt int 10    PRIMARY KEY id

User · Answer

One obvious thing is that you will have to remove the comma here  receipt int 10     but the actual problem is because of the line  amount double 10  NOT NULL    change it to  amount double NOT NULL

User · Answer

In MySQL  the word  type  is a Reserved Word

[mysql] #1064 -You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

Examples related to mysql

Examples related to error-handling

Examples related to create-table