Multiple insert from hbs to MySQL express js

Question

I have this hbs code:

                    {{#each data2}}

                    <tr>                                                     
                      <td width="30%"><div class="checkbox">
                        <label><input type="checkbox" name="data_{{no}}" id="id_produk" value=" {{no}}">{{item}}</label>
                      </div></td>
                      <td></td>
                      <td width="30%"><div>
                        <label><input type="text" id="id_produk" name="data_{{no}}" class="form-control text-right" placeholder="Qty"></label>
                      </div></td>                         
                    </tr>

                    {{/each}}

and this router code:

router.post('/save', (req, res, next) => {
    var sql = "INSERT INTO transaction (transaction_id, product_id, qty) VALUES ?";
});

I want to submit multiple data to mysql database. How can i achive that ?

You can use the batch insert like : INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); or refer [this](https://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js) — Punit Gajjar, Jan 23 '20 at 12:30

Senthil · Accepted Answer · 2020-01-24T09:23:00.247

0

Might be a duplicate question ref : Insert in bulk using Sequelize with Node and Express

Pass the data in an array , read the array and do bulk create operation

insert into (cols1, col2) values (list1val, list1val2), (list2val, list2val2)

Form data : data_4: [ '4', '9' ], data_5: [ '5', '8' ], data_6: [ '6', '7' ], data_7: [ '7', '3' ], data_8: [ '8', '2' ], data_9: [ '9', '1' ]

    var responseObj = {data_4: [ '4', '9' ], data_5: [ '5', '8' ], data_6: [ '6', '7' ], data_7: [ '7', '3' ], data_8: [ '8', '2' ], data_9: [ '9', '1' ] };

var tblSQL = "insert into tbl (col1, col2) values ";
var sql;
for (let key in responseObj) {
   if(responseObj.hasOwnProperty(key)) {
     console.log(`${key} : ${responseObj[key]}`)
     let id = responseObj[key][0];
     let age = responseObj[key][1];
     sql = (sql!=undefined ? sql+ "," : "") + "(" + id + "," + age+ ")";
   }
}

console.log (tblSQL  + sql);

edited Jan 24 '20 at 09:23

answered Jan 23 '20 at 12:33

Senthil

2,156
1
14
19

yes, i have to pass the data in array. but how can i read the data in the route, and attach in the query SQL. I was try to get the console.log(req.body) and here ins the result: data_4: [ '4', '9' ], data_5: [ '5', '8' ], data_6: [ '6', '7' ], data_7: [ '7', '3' ], data_8: [ '8', '2' ], data_9: [ '9', '1' ] – satt Jan 23 '20 at 12:48
Ref : how to iterate the objects https://dev.to/saigowthamr/how-to-loop-through-object-in-javascript-es6-3d26 and then form a string contains key, values . example see the edited answer – Senthil Jan 24 '20 at 09:17
i have tried your code. but there is an error that showing in my browser page. The error is: "responseObj.hasOwnProperty is not a function". what error is that? and how do i fix it? – satt Jan 24 '20 at 23:10
check how you are passing the data in jquery / javascript to the node js layer. You should properly convert into JSON object and send it to the node layer during form submission or read the form submitted data and generate a Javascritp object in this format var responseObj = {data_4: [ '4', '9' ], data_5: [ '5', '8' ], data_6: [ '6', '7' ], data_7: [ '7', '3' ], data_8: [ '8', '2' ], data_9: [ '9', '1' ] }; – Senthil Jan 25 '20 at 02:29
1

thank you for your help. I got the the clue from your link you shared to me. And this is my code: Object.keys(obj).forEach(key => { var a = (sql + "(" + `${obj[key]}`); console.log(a); connection.query(a, function(err, rows1) {}); }); and it works,,, – satt Jan 28 '20 at 13:14
great, if you find it useful.. kindlly mark it as correct answer – Senthil Jan 28 '20 at 18:47

Multiple insert from hbs to MySQL express js

1 Answers1