PHP update multiple rows with multiple WHERES doenst work

Question

I want to update multiple rows with multiple wheres. When i update only 1 input field it works perfecly, but when i update diffrent weeks and field the query fails. I dont know how to put this code togetter. I already search on google and stackoverflow but could not find something that fits my question.

here is my code

if(isset($_POST["opslaan"]))
        {
            unset($_POST["opslaan"]);

            foreach($_POST as $input => $inner_arr)
            {
                foreach($inner_arr as $week => $value)
                {
                    $update_query[] = $input." = '".$value."' WHERE week = ".$week;
                }
            }

            $test   = implode(", ", $update_query);

            echo $test;

            if(mysqli_query($conn, "UPDATE buitenklimaat SET ".$test))
            {
                header("location: dashboard.php?user=1&page=gewasregistratie");
                echo "done";
            }
            else
            {
                echo "Failed !";
            }
        }

echo output:

gem_buitentemperatuur_etmaal = '1' WHERE week = 1, 
gem_buitentemperatuur_etmaal = '2' WHERE week = 2, 
gem_buitentemperatuur_etmaal = '3' WHERE week = 3

You do realize that `WHERE week = ".$week` is being added multiple times, right? Can you paste what `$test` returns? — AyB, Jan 20 '14 at 10:07
There should be only a single [`WHERE`](http://dev.mysql.com/doc/refman/5.0/en/update.html). — BlitZ, Jan 20 '14 at 10:07
what is the output of echo $test; and check the final query output using `echo "UPDATE buitenklimaat SET ".$test` — Balaji Kandasamy, Jan 20 '14 at 10:09
DUmp your final sql statement and execute it in an interactive sql client. That is the easiest way to understand why your sql query is invalid. — arkascha, Jan 20 '14 at 10:11

AyB · Accepted Answer · 2014-01-20T12:30:48.143

2

$query = "UPDATE buitenklimaat SET ";

foreach($_POST as $input => $inner_arr)
{
    $query .= $input.' = CASE ';

    foreach($inner_arr as $week => $value)
    {
        $query .= ' WHEN week = '.$week. ' THEN '.$value;
    }

    $query .= ' ELSE '.$input.' END , ';
}

$query = rtrim($query, ', ');

Not tested, can you see what $query outputs?

Update: Added $input_array
Update 2: Trimming the last comma
Update 3: Restructured loop

edited Jan 20 '14 at 12:30

answered Jan 20 '14 at 10:28

AyB

11,609
4
32
47

**+1** for the `CASE` Illustration. – Shankar Narayana Damodaran Jan 20 '14 at 10:39
Nice example indeed and it works, There is only 1 but... gem_buitentemperatuur_etmaal is not the only field i need to update, i have 45+ field with diffrent weeks to update IF the user wants to. – Julez Jan 20 '14 at 10:46
Gonna try this give me 15 min – Julez Jan 20 '14 at 10:59
Query doesnt fail, but only updates the last week. UPDATE buitenklimaat SET gem_buitentemperatuur_etmaal = CASE WHEN week = 1 THEN 12 END, gem_buitentemperatuur_etmaal = CASE WHEN week = 2 THEN 4 END, gem_buitentemperatuur_etmaal = CASE WHEN week = 3 THEN 67 END, gem_buitentemperatuur_etmaal = CASE WHEN week = 4 THEN 23 END – Julez Jan 20 '14 at 11:17
I think I get now what you mean, please check again. – AyB Jan 20 '14 at 11:29
Hi, i almost works like it should, When i update 10 weeks for example it works perfecly. But when i update week 1 and 2 the other 8 weeks become empty somehow... – Julez Jan 20 '14 at 11:42
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/45637/discussion-between-user007-and-julez) – AyB Jan 20 '14 at 11:46
After the discussion works like a charm !! Thank you mate for youre time – Julez Jan 20 '14 at 12:32
ya this approach is definately better than mine +1 :) – R R Jan 20 '14 at 12:44

score 1 · Answer 2 · edited May 23 '17 at 11:56

1

You can not update multiple fields like that.

Look at answers in this question SQL Statement with multiple SET's and WHERE's

Also do not forget to sanitize your input, taking values directly from $_POST and putting them into a query is asking for a SQL Injection.

edited May 23 '17 at 11:56

Community

1
1

answered Jan 20 '14 at 10:10

Jozef Legény

1,157
1
11
26

Yes i know, thanks for that. I wanted first to get the code works then adding security. – Julez Jan 20 '14 at 10:24

R R · Answer 3 · 2014-01-20T10:18:38.883

1

you are trying to do

update
set 
where
where
where

which is wrong.

An update query can have only one where clause.

Update
set //can have multiple set clause
where//only one where clause

now what you can do is:

foreach($_POST as $input => $inner_arr)
            {
                foreach($inner_arr as $week => $value)
                {
                  $update_query[] = $input." = '".$value."' WHERE week = ".$week;
                  $test   = implode(", ", $update_query);
                   if(mysqli_query($conn, "UPDATE buitenklimaat SET ".$test))
                    {
                    header("location: dashboard.php?user=1&page=gewasregistratie");
                    echo "done";
                     }
                    else
                     {
                     echo "Failed !";
                     }
                }
            }

edited Jan 20 '14 at 10:18

answered Jan 20 '14 at 10:11

R R

2,999
2
24
42

But what i need to do when i got diffrent weeks to update ? for example WHERE week = 1, WHERE week = 2, WHERE week = 4 – Julez Jan 20 '14 at 10:13
Okey, this works and i understand whats happening. But is this slow when u update 120 querys ? or more. – Julez Jan 20 '14 at 10:22
@Julez if this approach slows your query then you should use CASE. – R R Jan 20 '14 at 10:23
Yes i was planning to, but i could not do it yet – Julez Jan 20 '14 at 10:43

Hassan · Answer 4 · 2014-01-20T11:11:34.940

You are creating query wrong. Traditional way to perform multiple updates is to create multiple update query in a single string and execute them in one go like as follow

UPDATE buitenklimaat  SET Category_ID = 10 WHERE week  = 2;
UPDATE buitenklimaat  SET Category_ID = 9  WHERE week  = 3;
UPDATE buitenklimaat  SET Category_ID = 12 WHERE week  = 4;
UPDATE buitenklimaat  SET Category_ID = 11 WHERE week  = 5;

Or there is another way describe by Karl Rixon

UPDATE buitenklimaat  
    SET Category_ID = CASE week 
        WHEN 2 THEN 10 
        WHEN 3 THEN 9
        WHEN 4 THEN 12
        WHEN 5 THEN 11
        ELSE Category_ID
    END

You can read further about it through following link

Source: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

Thanks i wil read the article, its kinda hard to read the CASE. But i will take a good look at it — Julez, Jan 20 '14 at 10:23

score 0 · Answer 5 · edited Jan 20 '14 at 10:46

0

Instead of WHERE, you could write AND and put the WHERE in the UPDATE string before the test variable, where you'll have all the requisites

edited Jan 20 '14 at 10:46

DB5

13,553
7
66
71

answered Jan 20 '14 at 10:24

ioses

997
1
8
11

PHP update multiple rows with multiple WHERES doenst work

5 Answers5