1

I'm using PHPMyAdmin, hosted with hostgator, to add a table to a database, but I keep getting the following error:

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 , note TEXT NOT NULL , cardNumber BIGINT(20) NOT NULL , `nameC' at line 1

Here's what I have: enter image description here and here's the preview of the SQL

CREATE TABLE `nightwin_mark-inn`.`guests` (
    `id` INT(3) NOT NULL AUTO_INCREMENT , 
    `dateIn` DATE NOT NULL , 
    `dateOut` DATE NOT NULL , 
    `email` TEXT NOT NULL , 
    `phone` INT(10) NOT NULL , 
    `room` TINYINT(2) NOT NULL , 
    `price` DOUBLE(6) NOT NULL , 
    `note` TEXT NOT NULL , 
    `cardNumber` BIGINT(20) NOT NULL , 
    `nameCard` TEXT NOT NULL , 
    `expDate` TEXT NOT NULL , 
    `cvc` TINYINT(3) NOT NULL , 
    PRIMARY KEY (`id`)
)

What's causing this issue? Do I have the length of one of the fields wrong?

Improve this question

4 Answers 4

Reset to default
2

Try to use this

price` DOUBLE(6,2) NOT NULL   //9999.99 max value stored

instead of

price` DOUBLE(6) NOT NULL

Note: for price field use datatype DECIMAL more preferable. In FLOAT or DOUBLE datatype you will get rounding number issue

Reference

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is the only Answer (so far) that points out that DOUBLE is not good for money.
1

You can try below - DOUBLE(6) should be only DOUBLE

CREATE TABLE `nightwin_mark-inn`.`guests` ( `id` INT(3) NOT NULL AUTO_INCREMENT , 
`dateIn` DATE NOT NULL , `dateOut` DATE NOT NULL , `email` TEXT NOT NULL , 
`phone` INT(10) NOT NULL , `room` TINYINT(2) NOT NULL , `price` DOUBLE NOT NULL , 
`note` TEXT NOT NULL , `cardNumber` BIGINT(20) NOT NULL , `nameCard` TEXT NOT NULL , 
 `expDate` TEXT NOT NULL , `cvc` TINYINT(3) NOT NULL , PRIMARY KEY (`id`))
Improve this answer

1 Comment

this works, thanks! I had a feeling it was improper setting of length. I'll accept the answer when it allows me too.
0 
please try using this MySQL statement

CREATE TABLE `guests` ( `id` INT(3) NOT NULL AUTO_INCREMENT ,  `dateIn` DATE NOT NULL , `dateOut` DATE NOT NULL , `email` TEXT NOT NULL ,  `phone` INT(10) NOT NULL , `room` TINYINT(2) NOT NULL , `price` DOUBLE(6,2) NOT NULL ,  `note` TEXT NOT NULL , `cardNumber` BIGINT(20) NOT NULL , `nameCard` TEXT NOT NULL ,      `expDate` TEXT NOT NULL , `cvc` TINYINT(3) NOT NULL , PRIMARY KEY (`id`));
Improve this answer

Comments

0

please try using this MySQL statement

CREATE TABLE guests ( id INT(3) NOT NULL AUTO_INCREMENT , dateIn DATE NOT NULL , dateOut DATE NOT NULL , email TEXT NOT NULL , phone INT(10) NOT NULL , room TINYINT(2) NOT NULL , price DOUBLE(6,2) NOT NULL , note TEXT NOT NULL , cardNumber BIGINT(20) NOT NULL , nameCard TEXT NOT NULL , expDate TEXT NOT NULL , cvc TINYINT(3) NOT NULL , PRIMARY KEY (id));

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.