INSERT INTO error for MYSQL

Question

Hi this is my first time posting. I have searched all over for an answer but can't seem to find one that fixes mine. I am writing mysql code for a project and can't seem to insert data into a database I am creating. The link to the code is here:

http://sqlfiddle.com/#!9/be69b

Below is what I have come up with so far

CREATE TABLE IF NOT EXISTS `customers` (`CustomerID` INT AUTO_INCREMENT,
`FirstName` VARCHAR(255) NOT NULL,
`LastName` VARCHAR(255) NOT NULL,
`Address` VARCHAR(255) NOT NULL,
`Apt#` VARCHAR(255) NOT NULL, 
`City` VARCHAR(50) NOT NULL, 
`State` VARCHAR(3) NOT NULL,
`Zip` VARCHAR(9) NOT NULL, 
`HomePhone` VARCHAR(11),
`MobilePhone` VARCHAR(11),
`OtherPhone` VARCHAR(11), 
PRIMARY KEY(`CustomerID`)
)
ENGINE=INNODB;


INSERT INTO `customers` VALUES
(`1`, `John`, `Doe`, `123 Green St.`, `Apt A.`, `Richmond`, `VA`, `78646`, `18049481616`, ``, ``);

It throws out an error saying that Unknown column 1 in field list. I have tried doing this without AUTO_INCREMENT and it doesn't change anything. Can anyone help with this? Thanks for your help!

Isn't surrounding values in backticks making them string literals? — orhtej2, Dec 04 '17 at 18:58

score 2 · Answer 1 · answered Dec 04 '17 at 19:12

2

Two issues:

1) You don't need to insert the ID since it's the field has an AUTO_INCREMENT clause.

2) You're using backticks which indicate column names, not strings.

INSERT INTO
    `customers`
    (`FirstName`, `LastName`, `Address`, `Apt#`, `City`, `State`, `Zip`, `HomePhone`, `MobilePhone`, `OtherPhone`)
VALUES
    ('John', 'Doe', '123 Green St.', 'Apt A.', 'Richmond', 'VA', '78646', '18049481616', '', '');

SQLFiddle: http://sqlfiddle.com/#!9/392ddf

answered Dec 04 '17 at 19:12

kchason

2,836
19
25

Thank you so much kchason! I completely missed the backticks. I crown myself idiot for the day lol. Changing this allowed the rows to be added successfully. I've programmed before but I'm completely new to databases. I should have seen this though. Thanks again. – user9052540 Dec 04 '17 at 20:04

score 1 · Answer 2 · edited Dec 04 '17 at 19:13

1

You define your table with "CustomerId" (auto_increment), so you shouldn't give a value for it in your insert-statement.

(1, John, Doe, 123 Green St., Apt A., Richmond, VA, 78646, 18049481616, ,)

should be

(John, Doe, 123 Green St., Apt A., Richmond, VA, 78646, 18049481616, ,)

That's what i think!

edited Dec 04 '17 at 19:13

kchason

2,836
19
25

answered Dec 04 '17 at 19:07

Ace

31
9

score 0 · Answer 3 · answered Dec 04 '17 at 19:11

One of the things that I try and do when debugging sql code is to make sure that special characters and blanks aren't messing with the query.

Using your insert code above, I recreated your table, and this query worked:

INSERT INTO `customers` VALUES (null,'John', 'Doe', '123 Green St.', 'Apt A.', 'Richmond, VA', '78646', '18049481616', null,null, null)

INSERT INTO error for MYSQL

3 Answers3