unknown column in filed list sql insertion error

Question

I have a java method in a data mapper, to create a user in a MySQL database.

Whenever I test creating a user I get this error

Unknown column 'phone' in 'field list'

here is my java method

public static void createUser( User user ) throws 
LoginSampleException {
    try {
        Connection con = Connector.connection();
        String SQL = "INSERT INTO Users (email, password, phonenumber, post, adress, role) VALUES (?, ?, ?, ?, ?, ?)";
        PreparedStatement ps = con.prepareStatement( SQL, Statement.RETURN_GENERATED_KEYS );
        ps.setString( 1, user.getEmail() );
        ps.setString( 2, user.getPassword() );
        ps.setString( 3, user.getPhonenumber() );
        ps.setInt( 4, user.getPostalCode() );
        ps.setString( 5, user.getAddress() );
        ps.setString( 6, user.getRole() );
        ps.executeUpdate();
        ResultSet ids = ps.getGeneratedKeys();
        ids.next();
        int id = ids.getInt( 1 );
        user.setId( id );
    } catch ( SQLException | ClassNotFoundException ex ) {
        throw new LoginSampleException( ex.getMessage() );
    }
}

It might be a syntax error, but I'm not sure why i get this error, I have double checked all fields to make sure that everything is typed correctly.

EDIT:

here is my SQL-script that creates the database:

CREATE TABLE `Users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`email` VARCHAR(90) NOT NULL,
`password` BLOB NOT NULL,
`phone` INT(16) NOT NULL,
`post` INT(8) NOT NULL,
`adress` VARCHAR(45) NOT NULL,
`role` VARCHAR(20) NOT NULL DEFAULT 'customer',
PRIMARY KEY (`id`),
UNIQUE KEY `email_UNIQUE` (`email`)
)  ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=LATIN1;

You mean `phonenumber` ? What is the `Users` table look like ? — AxelH, May 03 '18 at 10:15
Possible duplicate of [Unknown column in 'field list' error on MySQL Update query](https://stackoverflow.com/questions/1346209/unknown-column-in-field-list-error-on-mysql-update-query) — yash, May 03 '18 at 10:25
i haved added the sql script. @yash not a duplicate, since this is an insert method. — kristheman, May 03 '18 at 10:26
look at the answer of that question, if they don't work, thne post. — yash, May 03 '18 at 10:27

score 2 · Answer 1 · answered May 03 '18 at 10:23

2

Your table have a column phone but you are using phonenumber in your query.

"INSERT INTO Users (email, password, phonenumber, post, adress, role) VALUES (?, ?, ?, ?, ?, ?)"

Use instead :

"INSERT INTO Users (email, password, phone, post, adress, role) VALUES (?, ?, ?, ?, ?, ?)"

answered May 03 '18 at 10:23

AxelH

14,325
2
25
55

Why the down-vote? This seems like a perfectly reasonable possibility to me. – Syn May 03 '18 at 10:33
The first version of the answer, it was mostly "what could be" and not based on facts. No worries @Syn ;-) I don't hunt reputation. – AxelH May 03 '18 at 10:34

score 1 · Accepted Answer · answered May 03 '18 at 10:31

it's due to column name is not matching at both the places. you have used column name phone in database.
and you are using phonenumber in your java query, so it will not match with your database. So just change this

String SQL = "INSERT INTO Users (email, password, phonenumber, post, adress, role) VALUES (?, ?, ?, ?, ?, ?)";

To

 String SQL = "INSERT INTO Users (email, password, phone, post, adress, role) VALUES (?, ?, ?, ?, ?, ?)";

it will work for sure, if you are still getting error , then let me know.

unknown column in filed list sql insertion error

2 Answers2