CODEWITHSUNDEEP
phpmysqlsyntax
Mario Panebianco
Mario Panebianco

Reputation: 1

Basic Create Table MYSQL Syntax error

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.

Upvotes: 0

Views: 39

Answers (2)

Andrius
Andrius

Reputation: 5939

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
    )

Upvotes: 2

Thamilhan
Thamilhan

Reputation: 13303

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
    )

Upvotes: 0

Related Questions