How to get json object with multiple rows

Question

My goal is to write the Fields from the JSON object below into a MySQL Database.

var dataRow = {
    Table: "promosbudget",
    Fields:[{
        ManagerID: "Jose",
        UserID: "ife",
        Budget: "50000",
        Year: "2015"
    },
    {
        ManagerID: "Jose",
        UserID: "fgs",
        Budget: "50000",
        Year: "2015"
    },
    {
        ManagerID : "Jose",
        UserID : "brz",
        Budget : "50000",
        Year : "2015"
    }]
};

I'm using this command to receive and write the data:

app.post('/paramsjson', jsonParser, function(req, res) { 
    conMySQL.query('INSERT INTO ' + req.body.Table + ' SET ?',req.body.Fields,               
                 function(err,result) {
            console.log(result);
        }
    );
});

The issue is that I can only write the first JSON row, the other 2 rows are omitted.

I'd like to ask if there is a recommended method to do that right when I need to export a large JSON object (100.000 row), is it necessary to make a loop and read each row sequentially?

Thanks in advance for your help!

You are vulnerable to sql injection attacks, and need to loop on the json data structure and insert each of those "fields" sub-objects individually. — Marc B
– Marc B, Commented Oct 20, 2015 at 17:20

yeya · Accepted Answer · 2015-10-20 17:16:28Z

1

Try to go step by step. Don't INSERT right into the database, first console.log the outputs, look into the results, try to INSERT directly from the code. Then try to combine it all.

Anyway - I highly recommend using knex for any DB operation.

Take this sample codes for testing:

app.post('/paramsjson', jsonParser, function(req, res) { 

  console.log(req.body);

  var table = req.body.Table;
  var fields = req.body.Fields;

  //If you want to use knex:

  knex( table ).insert( fields )

    .then(function (result) {
      console.log(result)
    })
    .catch(function (err) {
      console.log(err)
    })

  });

answered Oct 20, 2015 at 17:16

yeya

2,2791 gold badge26 silver badges40 bronze badges

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

1 Comment

IvanF Over a year ago

Thanks a lot for your comments. That's very interesting that knex, I'll take a sight on this.

Collectives™ on Stack Overflow

How to get json object with multiple rows

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related