CODEWITHSUNDEEP
mysqlnode.js
Ohhh
Ohhh

Reputation: 435

Insert Data to Mysql Through Nodejs

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;

Upvotes: 1

Views: 2994

Answers (1)

Rodrigo Medeiros
Rodrigo Medeiros

Reputation: 7862

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]'

Upvotes: 2

Related Questions