Is there a way to loop mysql (OR statement) to fetch multiple rows in express.js

Question

I have a json request in this form:

{
"claimNo":["5454545","4554454","45884"]

}

the claimNo could hold any number of items(not restricted). I want to get the values and write a query to fetch data from a mysql db where claimNo = the values in the request. sample response:

 "claims": [
    {
        "claimNo": "4554454",
        "ClaimCause": "gjgiukhknl",
        "ClaimAmount": 45550,
    },
    {
        "claimNo": "5454545",
        "ClaimCause": "fdfdfdfdf",
        "ClaimAmount": 0,
    }
]

I can successfully loop through the request and display on terminal or even insert into the db with multiple ORs but that only works for a restricted array length.

 req.body.claimNo.forEach(element => {
    // console.log(element)

    let sql = 'SELECT * FROM  claims WHERE claimNo = ?'

    connection.query(sql,element,(err, rows, fields) => {
            if(!err){
                // return res.json({
                //     success:true,
                //     errorCode:"",
                //     errorDescription:"",
                //     claims:rows
                // })
                console.log(rows)
            }else
            console.log(err)


    } )
})

Danny Sofftie · Accepted Answer · 2020-04-16 14:35:47Z

2

If I understand your question correctly, you're looking for a way to query MySQL database for multiple number of claimNo entries, and return the result as a single result.

Using MySQL IN() operator, you can write your query as select * from claims where claimNo in(?)

let sql = 'select * from claims where claimNo in(?)';

let claimNos = req.body.claimNo;

connection.query(sql, claimNos, (err, rows, fields) => {
  if(!err){
     // return res.json({
     //     success:true,
     //     errorCode:"",
     //     errorDescription:"",
     //     claims:rows
     // })
     console.log(rows)
 }else
   console.log(err)
})

edited Apr 16, 2020 at 14:35

answered Apr 18, 2019 at 9:37

Danny Sofftie

1,0611 gold badge9 silver badges17 bronze badges

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

Comments

MrfksIV · Accepted Answer · 2019-04-18 09:31:00Z

1

You don't need to send a separate request for each claimNo. You can you the IN operator instead. The following should work:

const claimsNo = claims.map(claim => claim.claimNo);

const sql = 'SELECT & FROM claims where claimNo IN (?)';
connection.query(sql, [ tableName, claimsNo ], (err, rows, fields) =>{
   ...
});

answered Apr 18, 2019 at 9:31

MrfksIV

9406 silver badges17 bronze badges

Collectives™ on Stack Overflow

Is there a way to loop mysql (OR statement) to fetch multiple rows in express.js

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related