Node.js新手求助：PUT接口无法更新数据库数据

Hey there! Let's break down why your PUT endpoint isn't updating your orders table correctly. I noticed a couple of key issues in your code that are stopping the update from working as expected.

Key Issues in Your Current Code #

• Hardcoded Status Value: You're pulling status from the request body but then hardcoding "Complete" in your SQL query. That means no matter what status the frontend sends, the database will always set it to "Complete"—not exactly dynamic!
• Incorrect Parameter Binding: Your SQL query only has one placeholder (?) for orderID, but you're passing two parameters ([status, orderID]) in the wrong order. This not only wastes the status value but can also cause unexpected query behavior.

Corrected Code #

app.put('/orders/:id', (req, res) => {
  pool.getConnection((err, connection) => {
    if (err) {
      console.error('Error connecting to database:', err);
      return res.status(500).send('Failed to connect to database');
    }
    console.log(`Connected as id ${connection.threadId}`);
    
    // Destructure and validate request body parameters
    const { orderID, status } = req.body;
    if (!orderID || !status) {
      connection.release();
      return res.status(400).send('Missing required fields: orderID and status are required');
    }

    // Use placeholders for both status and orderID, with correct parameter order
    connection.query(
      'UPDATE orders SET status = ? WHERE orderID = ?',
      [status, orderID],
      (err, rows) => {
        connection.release();
        if (!err) {
          if (rows.affectedRows === 0) {
            return res.status(404).send(`Order ${orderID} not found`);
          }
          res.send(`Order ${orderID} has been updated to status: ${status}`);
        } else {
          console.error('Error updating order:', err);
          res.status(500).send('Failed to update order');
        }
      }
    );
  });
});

What Changed & Why #

1. Dynamic Status Value: Replaced the hardcoded "Complete" with a placeholder (?) so the status from the request body is actually used in the update.
2. Correct Parameter Order: The parameters array [status, orderID] now matches the order of placeholders in the SQL query (SET status = ? first, then WHERE orderID = ?).
3. Added Validation: Check if orderID and status exist in the request body to avoid invalid queries.
4. Better Error Handling: Return appropriate HTTP status codes (400 for bad requests, 404 for missing orders, 500 for server errors) and user-friendly messages instead of just throwing errors or logging them.
5. Check Affected Rows: Verify if any rows were updated to handle cases where the orderID doesn't exist in the database.

内容的提问来源于stack exchange，提问作者Siyabonga Mntungwa