PHP, MySQL Update multiple columns

Question

$mengetotzut = 7','8','9','22','23','24','25 ;
$zutatiddd = 42','75','8','90','23','82','33 ;
$sql5= "UPDATE zutaten SET Menge=('$mengetotzut') WHERE Zutat_ID IN('$zutatiddd')";
 $run_query = mysqli_query($con,$sql5);

As SQL statement is looking like :

UPDATE zutaten SET Menge=('42','75','8','90','23','82','33') WHERE Zutat_ID IN('7','8','9','22','23','24','25')

What i am trying to do is to do is:

Update Menge='42' Where Zutat_ID is '7' AND Update Menge ='75' Where Zutat_ID = '8' ...

But in one SQL statement. Can someone tell me if this is possible and how can i do it?

You can't do a multiple update in one query. You can do a multiple insert with the clause "ON DUPLICATE KEY UPDATE" TO DO YOUR PURPOSE. You can find this explained here: http://stackoverflow.com/questions/3432/multiple-updates-in-mysql — SBO, Jan 18 '17 at 10:09
With a simple for or foreach loop you can iterate over your $mengetotzut and $zutatiddd arrays. You cant execute a single sql statement for your purpose. you have to execute as many update statements, as items in your arrays. Insert on duplicate key presumes a strictly normalized data structure. But have a try on SBO s comment. — Marcel, Jan 18 '17 at 10:11
@SBO yes you can, the second answer in the question linked shows you can using CASE. — Styphon, Jan 18 '17 at 10:15

score 0 · Answer 1 · answered Jan 18 '17 at 10:09

0

Use foreach loop

foreach( $mengetotzut as $index => $code ) {
$sql5= "UPDATE zutaten SET Menge=('$code') WHERE Zutat_ID = $zutatiddd[$index];
 $run_query = mysqli_query($con,$sql5);   
}

answered Jan 18 '17 at 10:09

coder

906
1
12
19

i use this in $code ) { $sql5= "UPDATE zutaten SET Menge=('$code') WHERE Zutat_ID = $zutatiddd[$index]; $run_query = mysqli_query($con,$sql5); } ?> and he shows me as error. – Jan 18 '17 at 10:43

score 0 · Accepted Answer · edited Jan 18 '17 at 11:00

0

Considering that both arrays have the same count, try the below

$count = 7;

for ($i=0; $i<$count; $i++) {
    $sql5= "UPDATE zutaten SET Menge=$mengetotzut[$i] WHERE Zutat_ID = $zutatiddd[$i]";
    $run_query = mysqli_query($con,$sql5);
}

edited Jan 18 '17 at 11:00

lorem monkey

3,942
3
35
49

answered Jan 18 '17 at 10:12

andyd

71
8

score 0 · Answer 3 · answered Jan 18 '17 at 10:13

0

You can use an SQL query like the following to UPDATE with one go:

UPDATE zutaten AS t1
JOIN (
    SELECT 7 AS Zutat_ID, 42 AS Menge
    UNION ALL
    SELECT 8, 75
    UNION ALL
    SELECT 9, 8
) t2 ON t1.Zutat_ID = t2.Zutat_ID
SET t1.Menge = t2.Menge

answered Jan 18 '17 at 10:13

Giorgos Betsos

71,379
9
63
98

the problem is that the number can change this because in my code i have more sql statements and the numbers can change that why i want to do it from variable – Jan 18 '17 at 10:15

score 0 · Answer 4 · answered Jan 18 '17 at 10:14

0

What you are after is CASE, your query will look like this:

UPDATE zutaten SET Menge = CASE Zutat_ID
    WHEN 42 THEN 7
    WHEN 75 THEN 8
    WHEN 8 THEN 9
    WHEN ...
END WHERE Zutat_ID IN (42,75,8,...)

answered Jan 18 '17 at 10:14

Styphon

10,304
9
52
86

the problem is that the number can change this because in my code i have more sql statements and the numbers can change that why i want to do it from variable – Jan 18 '17 at 10:42
@CristianCănănău great, so write it dynamically using variables. It's not hard to add a for loop that outputs the `WHEN THEN` part. – Styphon Jan 18 '17 at 10:47
can you show me as code? my variable $mengetotzut = 7','8','9','22','23','24','25 ; ---- is looking like this and $zutatiddd = 42','75','8','90','23','82','33 ; ---- is looking like this – Jan 18 '17 at 10:49
@CristianCănănău I'm not going to write your code for you, this isn't a code writing service. If you can't even write a basic for loop then go do some tutorials on **basic** PHP. – Styphon Jan 18 '17 at 10:50
than how can i do this ? because i try more option and thy doesdn' work? – Jan 18 '17 at 10:51

PHP, MySQL Update multiple columns

4 Answers4