Insert Data to Mysql Through Nodejs

Question

I am trying to insert data into the MySQL user table through node js, I am using and following https://github.com/felixge/node-mysql#escaping-query-values

Below is my code, I don't really know what went wrong, I couldn't insert data through postData query, but it works if I try to execute the test query.

Why isn't the postData query working?

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host        : 'localhost',
    port        : 3306,
    database    : 'csc309',
    user        : 'root',
    password    : 'root'
});

connection.connect();

var postData = {username: 'user4', 
    firstname: 'first', 
    lastname: 'last', 
    password: 'password', 
    email: '[email protected]'};

/*  
var test = connection.query('INSERT INTO user (username, firstname, lastname, password, email) VALUES 
    ("user4", "first", "last", "password", "[email protected]")');
*/

var query = connection.query('INSERT INTO user VALUES ?', postData, function(err, result) {
    console.log('The solution is: ', result);
});

Here is my user sql user table.

create table user (
    id int AUTO_INCREMENT PRIMARY KEY,
    username varchar(40) UNIQUE NOT NULL,
    firstname varchar(40) NOT NULL,
    lastname varchar(40) NOT NULL,
    password varchar(100) NOT NULL,
    created_at datetime NOT NULL,
    email varchar(40) UNIQUE NOT NULL,
    reputation int DEFAULT 0,
    mailing_address varchar(100),
    phone varchar(20),
    gender varchar(10),
    admin tinyint default 0 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Rodrigo Medeiros · Accepted Answer · 2015-02-20 11:09:50Z

2

If you want to insert a row with explicitly specified values passing an object to a escaped query, you should use the INSERT ... SET form:

var query = connection.query('INSERT INTO user SET ?', postData, function(err, result) {
  console.log('The solution is: ', result);
});

The resulting query will look like:

INSERT INTO user SET `username` = 'user4', `firstname` = 'first', `lastname` = 'last', `password` = 'password', `email` = '[email protected]'

edited Feb 20, 2015 at 11:09

answered Feb 20, 2015 at 10:23

Rodrigo Medeiros

7,8724 gold badges45 silver badges55 bronze badges

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

5 Comments

Ohhh Over a year ago

the test query works fine with or with out the callback, my question is why the postData query not working.

Rodrigo Medeiros Over a year ago

You question was very clear, but I totally misunderstood it. I've updated my answer.

Ohhh Over a year ago

Yes, I tried both VALUES ? AND SET?, and it still wouldn't work. That is the weird part, am i missing something package?

Ohhh Over a year ago

Oh, nvm, I figured out myself, in the sql user table I set email as unique so it wouldn't allow me to add user with the same email address....

Rodrigo Medeiros Over a year ago

Well, assuming that the test query worked, you've inserted a row in your table. That said, you should check the err variable, because you defined the username attribute as UNIQUE, so you can't insert another row with an already existent username='user4'.

Collectives™ on Stack Overflow

Insert Data to Mysql Through Nodejs

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related