CODEWITHSUNDEEP
mysql
user3236149
user3236149

Reputation: 167

Update the same row multiple times

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?

My table is this:

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

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

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

onions, pears, broccoli, etc..

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();

Thanks for any help!

Upvotes: 1

Views: 2830

Answers (1)

O. Jones
O. Jones

Reputation: 108839

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'

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')

Upvotes: 2

Related Questions