PHP/MySQL : UPDATE multiples values with multiples WHERE

Question

The problem I'm having is with a strategy browser game which has 7 types of values. The problems are as follows:

I got 7 different values in $_POST, from index unit_1 to index unit_7 included. These 7 values are INTEGERS between 0 and 20 and aren't static. They can at each call of the page. They represent the number of units sent.
I got 7 lines in a table that does not follow the same schema : "nameUnit" = "Soldier" && nbUnit = "0", "nameUnit" = "Mage" && nbUnit = "5", etc..
I need to do an update on the number of units in 1 request, but I don't know how to do this.

Example of what I want to do :

UPDATE x SET nbUnit = $_POST['u1'] WHERE nameUnit = "Soldier", nbUnit = $_POST['u2'] WHERE nameUnit = "Mage" [...]

Is it possible to do this that way and can it be done in a single query?

If the values updated (SET clause) are the same, you can use the clause OR. If the values are different, you have the create one update for each statement. — Gabriel Heming
– Gabriel Heming, Commented May 11, 2018 at 13:22

Null Pointer · Accepted Answer · 2018-05-11 13:23:29Z

1

this work in ur case

UPDATE x 
SET nbUnit = $_POST['u1'] 
WHERE (nameUnit = "Soldier" AND nbUnit = $_POST['u2']) OR 
(nameUnit = "Mage" AND nbUnit= $_POST['u5'])

answered May 11, 2018 at 13:23

Null Pointer

4641 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cdaiga · Accepted Answer · 2018-05-11 13:26:41Z

0

IN PL SQL you can do the following :

UPDATE x SET nbUnit = case when nameUnit = "Soldier" then $_POST['u1'] 
when nameUnit = "Mage" then $_POST['u2'] else nbUnit end;

edited May 11, 2018 at 13:26

cdaiga

4,9374 gold badges25 silver badges44 bronze badges

answered May 11, 2018 at 13:22

Hany Habib

1,4152 gold badges12 silver badges19 bronze badges

3 Comments

P. Jerome Over a year ago

Your solution seems to work perfectly. Is this possible to do "THEN nbUnit + $_POST['u1']" to increment ?

Hany Habib Over a year ago

after then expression comes which can represent a value as mentioned in Oracle : In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. You cannot specify the literal NULL for every return_expr and the else_expr.

P. Jerome Over a year ago

I understand. I got this working perfectly using CASE. For information, I used to do UPDATE x SET y = CASE [...] ELSE y END WHERE z = xxxx, and it seems to work. It must be way better than using AND on a WHEN clause. Thanx for everything !

Collectives™ on Stack Overflow

PHP/MySQL : UPDATE multiples values with multiples WHERE

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related