Cannot Execute SQL Query in a .map()-CodePudding

I have an endpoint that receives an array in the req.body. I need to fetch that array and for each element of that array, i need to execute the SQL Update Query. This is the code:

const approveShifts = (req, res) => {
  try {
    const { guard_id } = req.params;
    const { shiftIDs, isBooked } = req.body;
    shiftIDs.map((shift_id) => {
      connection.query(
        `UPDATE shift SET isBooked=${isBooked}, fk_guard=${guard_id} WHERE shiftID=${shift_id}`,
        (err, rows) => {
          if (!err) {
            res.status(200).json({
              success: true,
              message: `Successfully Approved Shift #${shift_id} for Guard #${guard_id}`,
            });
          } else {
            res.status(404).json({
              success: false,
              message: "Shift Not Found!",
            });
          }
        }
      );
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

This is my req.body:

{
    "shiftIDs": [64],
    "isBooked": 1
}

The issue is, no matter what kind of testing i do, the only output i get is "Shift Not Found!" from the else statement of the query. Nothing else happens. I can't get it to work. Can someone guide me ?

CodePudding user response：

A couple of things here- firstly I recommend you use prepared statements instead of string templates for your query:

// (assuming this is mysql client?)

connection.query('UPDATE shift SET isBooked = ?, fk_guard = ? WHERE shiftID = ?', [isBooked, guard_id, shift_id], (err, rows, fields) => {...})

// if it's the mysql2 client, use connection.execute() instead of connection.query()

This works by replacing each ? with the value in the array, in order. This will help avoid SQL injection problems.

Secondly, you can do this in 1 query instead of mapping by using the IN SQL operator because you are setting the same value for isBooked and fk_guard for every shiftID:

// assuming shiftIDs is an array

connection.query('UPDATE shift SET isBooked = ?, fk_guard = ? WHERE shiftID IN (?)', [isBooked, guard_id, shiftIDs], (err, rows, fields) => {...});

And as someone else said, you should console.log(err) right before res.status(404) to see what the error is. And by the way, if the shift doesn't exist, no rows will be updated but no error will be thrown either, so your response wouldn't be 404.