多次更新同一行 [英] Update the same row multiple times

问题描述

当在表单中选择项目时，我试图增加一行.我知道执行更新语句可以满足我的要求，但我的问题是:如果表单中有 5 或 10 个字段可供用户同时选择同一项目，我如何同时更新同一行保存X次选择相同的项目?

I'm trying to increment a row when in the form's the item is selected. I know that doing an update statement does what I ask but my question is: if in the form there is 5 or 10 fields that the user can choose at the same time the same item, How can I update the same row in the same time to save the X number of times that the same item was chosen?

我的桌子是这样的:

---materials--- (table name)
id (int)
mat_name (varchar)
quantity (int)
used  (int)
existence (int)

选择项目时我需要加1"的那一行是used"

The row I need to raise with "1" when the item was choose is "used"

在材料"表中，大约有 100 个项目，例如:

In the table of "materials" there are like 100 of items like:

洋葱，梨，西兰花，等等.

onions, pears, broccoli, etc..

所以这个想法是，当为表中的任何项目选择了某个项目时，该项目将在已使用"行中以 1 递增，并通过一个简单的减法显示每个项目的剩余数量

So the idea is when some item was selected for whatever in the table that item will be increment in 1 in the row "used" and with a simple substraction show how much quantity of every item remains in existence

我的查询可能如下所示:

my query could look something like this:

$statement = "UPDATE materials
    SET used = :used,
    WHERE mat_name = :mat_name";
$stmt = $conn->prepare($statement);
$stmt->bindParam(':used', $_POST['used'], PDO::PARAM_INT);
$stmt->bindParam(':mat_name', $_POST['mat_name'], PDO::PARAM_STR);
$stmt->execute();

感谢您的帮助！

推荐答案

您可以像这样更新表中的一行，给出要更改的列以及每个列的新值.请注意，您可以引用列的先前值，就像 used = used+1 一样.

You can update a row in a table like this, giving the columns you want changed and the new value for each. Notice that you can refer to the column's previous value, as used = used+1 does.

UPDATE materials
   SET used=used+1, 
       recent_user='personsName'
 WHERE mat_name = 'apple'

您可以像这样在单个 UPDATE 查询中增加多行中的数字.

You can increment a number in multiple rows in a single UPDATE query like this.

UPDATE materials
  SET used = used + 1
WHERE mat_name IN ('apple', 'banana', 'kumquat')

您也可以在多行中更新多于一列.只需给出要更新的列及其新值的列表.例如，

You can update more than one column in multiple rows, too. Simply give a list of columns to be updated along with their new values. For example,

UPDATE materials
   SET used=used+1, 
       recent_user='personsName'
 WHERE mat_name IN ('apple', 'banana', 'kumquat')

这篇关于多次更新同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！