Basic Create Table MYSQL Syntax error

Question

Ok, so I think this should be some kind of simple basic fact that I seemingly can't for the life of me seem to find.

This is the code:

$sql="
CREATE TABLE Customer
    (
        CustomerID  INT (2)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
        FirstName   CHAR(15)    NOT NULL,
        SecondName  CHAR(15)    NOT NULL,
        StreetAdd   CHAR (255)  NOT NULL,
        City        CHAR (30)   NOT NULL,
        States      CHAR (3)    NOT NULL,
        ZIP         INT (4)     NOT NULL,
        Credit      INT(4),
        Balance     DOUBLE(20)
    )
    ";

And the error is:

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 ') )' at line 11

It's simple right? I mean it's supposed to be simple? I have another table in my full code that has a similar error, and then I have a third one that works fine. I can't notice any difference between them.

I've been messing with the syntax for about 2 hours already and I can't figure it out.

To debug the problem yourself you might: a) use an SQL tool instead of say PHP. b) you can split up your statement into a smaller piece and see if that piece works. — Micha Wiedenmann
– Micha Wiedenmann, Commented Nov 13, 2015 at 8:14

Andrius · Accepted Answer · 2015-11-13 13:22:34Z

In MySQL, when creating floats and doubles, you need to specify how much numbers after the decimal point are being stored if you define the length stored. Source

Either you define both numbers or you do not define the length at all.

So, just add the second number like in my example:

CREATE TABLE Customer
    (
        CustomerID  INT (2)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
        FirstName   CHAR(15)    NOT NULL,
        SecondName  CHAR(15)    NOT NULL,
        StreetAdd   CHAR (255)  NOT NULL,
        City        CHAR (30)   NOT NULL,
        States      CHAR (3)    NOT NULL,
        ZIP         INT (4)     NOT NULL,
        Credit      INT(4),
        Balance     DOUBLE(20,2)
    )

or remove the length parameters altogether

CREATE TABLE Customer
    (
        CustomerID  INT (2)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
        FirstName   CHAR(15)    NOT NULL,
        SecondName  CHAR(15)    NOT NULL,
        StreetAdd   CHAR (255)  NOT NULL,
        City        CHAR (30)   NOT NULL,
        States      CHAR (3)    NOT NULL,
        ZIP         INT (4)     NOT NULL,
        Credit      INT(4),
        Balance     DOUBLE
    )

Thamilhan · Accepted Answer · 2015-11-13 08:14:11Z

0

It should be: Double shouldn't include 20

CREATE TABLE Customer
    (
        CustomerID  INT (2)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
        FirstName   CHAR(15)    NOT NULL,
        SecondName  CHAR(15)    NOT NULL,
        StreetAdd   CHAR (255)  NOT NULL,
        City        CHAR (30)   NOT NULL,
        States      CHAR (3)    NOT NULL,
        ZIP         INT (4)     NOT NULL,
        Credit      INT(4),
        Balance     DOUBLE
    )

answered Nov 13, 2015 at 8:14

Thamilhan

13.3k5 gold badges40 silver badges60 bronze badges

Collectives™ on Stack Overflow

Basic Create Table MYSQL Syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related