Merlin Merlin - 2 months ago 14
MySQL Question

Fastest way to JSON object into mysql using node

Using a prior example? How could I insert/update a mysql table using

a JSON object without manually naming the table column headers? And insure it async.

var mysql = require('node-mysql');
var conn = mysql.createConnection({
...
});


var values = [
{name:'demian', email: 'demian@gmail.com', ID: 1},
{name:'john' , email: 'john@gmail.com' , ID: 2},
{name:'mark' , email: 'mark@gmail.com' , ID: 3},
{name:'pete ' , email: 'pete@gmail.com' , ID: 4}
];

// var sql = "INSERT INTO Test (name, email, n) VALUES ?";


conn.query(sql, [values], function(err) {
if (err) throw err;
conn.end();
})

Answer

You could do something like this:

for(var i = 0; i < values.length; i++){
    var post  = values[i]
    var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
         // Finish
    });
}

EDIT

This is how you inserts multiple 'posts' at once.

INSERT INTO posts (type, details)
  VALUES
  ('Helen', 24),
  ('Katrina', 21),

You would have to loop through the first value to get the names like this.

var names = [];
for(name in values[0]){
names.push(name);
// That would give you name, email, id
}

Then you would have to create your own string to insert.

var newvalues = [];
for(var i = 0; i < values.length; i++){
    newvalues.push('(' + values[i].join(',') + ')');
}

Then to execute the query:

connection.query('INSERT INTO posts (' + names.join(',') + ') VALUES ' + newvalues.join(',') , function(err, rows, fields) {
  // Result
});

You would have to test the code yourself, this is just how you would do it.