How could I check which value users want to update after getting this request in MySQL?-CodePudding

I want to update the specific value that one user requested. for example, if users want only title update then it goes to query in the MySQL only title. If users want all of them (title, description, selling_price, etc).

How could I achieve this goal using node js and MySQL?

I have tried but when users update only one field in the MySQL column it will affect other columns.

CodePudding user response：

You need to dynamically generate the update statement that you're affecting the DB with available variables.

const { id } = req.params;
const keys = Object.keys(req.body);

const params = [];

for (let i = 0; i < keys.length; i  ) {
  const key = keys[i];
  const value = req.body[key];

  if (!value) continue;

  if (typeof value === "string") {
    params.push(`${key}='${value}'`);
  } else {
    params.push(`${key}=${value}`);
  }
}

const query = `UPDATE product SET ${params.join(", ")} WHERE id=${id};`;
// query execution

You need to make sure that you validate the body that comes from the request to make sure it contains only the valid keys and values.

CodePudding user response：

After posting I did it. How is this? If anyone finds a better solution please share the code.

//update or modify API

app.patch("/product/:id", (req: Request, res: Response) => {
    const { id } = req.params;
    const obj = req.body;
    let success = 0;
    Object.entries(obj).forEach(([key, value]) => {
      if (value) {
        const sql = `UPDATE products SET ${key} = '${value}' WHERE product_id = ${id}`;
        supermarketDB.query(sql, (err: string, result: JSON) => {
          if (err) {
            console.log("error occurred from UPDATE method", err);
          }
          if (success === 0) {
            console.log("render", success);
            success  = 1;
            res.send(result);
          }
        });
      }
    });
  });