how to update mysql tabel with a multidimensional array

Question

I have a multidimensional array att I want to insert in MySQL through an UPDATE statement. But I only want it to be updated if the entry_id from the array fits with entry_id from DB. If it matches the values from the array, it has to be updated.

This is my multidimensional array named values

Array ( 

[0] => Array ( [entry_id] => 41149 [o_number] => 000001 [test1] => 000001 [test2] => 1234 [lev] => Ja [fak] => Mail [beta] => 30 [test] => 4 ) 

[1] => Array ( [entry_id] => 41142 [o_number] => 000202[test1] => 000202 [test2] => 1234 [lev] => Ja [fak] => Mail [beta] => 30 [test] => 4 ) 

[2] => Array ( [entry_id] => 41103 [o_number] => 000003 [test1] => 000003 [test2] => 1234 [lev] => Ja [fak] => Mail [beta] => 30 [test] => 4 ) 

[3] => Array ( [entry_id] => 41101 [o_number] => 000044 [test1] => 000044 [test2] => 1234 [lev] => Ja [fak] => Manuel/brev [beta] => 10 [test] => 2 ) 

[4] => Array ( [entry_id] => 41100 [o_number] => 000542 [test1] => 000542 [test2] => 1234 [lev] => Ja [fak] => Mail [beta] => 30 [test] => 4 ))

This is my DB fields, i want to update.

o_number     test1     test2     lev     fak     beta

And this is how my DB looks like now

title           entry_id     o_number     test1     test2     lev     fak     beta
Rest Soya       41149           
Cafe Bella      41142        
Danglette       41103

This is my code, for how to iterate throug an dimensional array and then update mySQL. But i only want to update, if the entry_id matches.

So the question is, how can I update the table when I have a multidimensional array? I tried it this way, but have not decided to test it because I do not want to insert anything wrong. Is there a better and more efficient way to do this?

foreach ($values as $key) 
{
        $sql_update = "UPDATE exp_channel_data set 
        o_number =$key['o_number'],
        lev ='$key['lev']',
        fak =$key['fak'],
        beta =$key['beta'],
        test1 =$key['test1'],
        test2 =$key['test2']

        where entry_id = '$entry_id"

        $this->EE->db->query($sql_update);
}

This is my desired output in my DB

title                   entry_id     o_number     test1     test2     lev     fak     beta
Rest Soya               41149        000001       000001    1234      Ja      Mail    30
Cafe Bella              41142        000202       000202    1234      Ja      Mail    30
Danglette               41103        000003       000003    1234      Ja      Mail    30

That's the appropriate way to do it, except you have lots of quoting problems. — Barmar
– Barmar, Commented Sep 2, 2013 at 22:47

Barmar · Accepted Answer · 2013-09-02 23:10:33Z

2

When you interpolate array references into a string, you must either surround the value with {} or leave out the quotes around the array indexes. You also forgot to use an array reference to get entry_id, and you had mismatched quotes around it.

foreach ($values as $key) 
{
        $sql_update = "UPDATE exp_channel_data set 
        o_number ={$key['o_number']},
        lev ='{$key['lev']}',
        fak ='{$key['fak']}',
        beta ={$key['beta']},
        test1 ={$key['test1']},
        test2 ={$key['test2']}

        where entry_id = '{$key['entry_id']}'";

        $this->EE->db->query($sql_update);
}

I can't tell what MySQL API you're using. Instead of interpolating strings, it would be better to used parametrized queries. If you're using mysqli or PDO, you should fix your db class to allow this.

edited Sep 2, 2013 at 23:10

answered Sep 2, 2013 at 22:52

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

4 Comments

Barmar Over a year ago

I added quotes around $key['fak'], since it's a string.

Zaz Over a year ago

Still the same @Barmar

Barmar Over a year ago

Any error? Make sure you have error reporting enabled. And if you're using mysql or mysqli, check the return value of the query call to see if it's successful, and call mysql_error() to print the error message if so.

Zaz Over a year ago

it did work, i just looked at the wrong place :) Thanks again :)

Collectives™ on Stack Overflow

how to update mysql tabel with a multidimensional array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related