php use two arrays in an mysql update query

Question

Hey guys i want to use two arrays in on mysql UPDATE query. So here is what i have:

For example:

$ergebnis:
Array ( [0] => 100 [1] => 200 [2] => 15 )


$Index:
Array ( [0] => 3 [1] => 8 [2] => 11 )

And this is what i tried:

UPDATE `lm_Artikel` 
SET Bestand='".$ergebnis."' 
WHERE `Index` = '".$Index."'

This query seems not to work. I don't know why i enabled php error reporting and there are no errors and when i run the query it doesn't change anything in my database. Can anyone see what i did wrong?

Try printing the value of your query string and seeing what comes out. No matter how many times I make this mistake, the result always seems to surprise me. — Neil
– Neil, Commented Nov 28, 2012 at 13:17

George · Accepted Answer · 2012-11-28 13:34:40Z

4

You need to do it for each element of your arrays, hence, you can use the foreach() function:

foreach($ergebnis as $key => $value){
    $sql = "UPDATE lm_Artikel SET Bestand='".$value."' WHERE `Index` = '".$Index[$key]."'";
    mysqli_query($sql);
}

P.S. There could well be a pure-sql alternative but I'm not too SQL-hot, so I'll leave it to someone who has more expertise.

Also, please note that it may be easier for you to set the index as the array keys:

$ergebnis = Array(3=>100, 8=>200, 11=>15);

And then the foreach() would look a little better:

foreach($ergebnis as $key => $value){
    $sql = "UPDATE lm_Artikel SET Bestand='".$value."' WHERE `Index` = '".$key."'";
    mysqli_query($sql);
}

edited Nov 28, 2012 at 13:34

answered Nov 28, 2012 at 13:16

George

36.9k9 gold badges70 silver badges109 bronze badges

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

Comments

Alan D'Avila · Accepted Answer · 2014-01-22 10:43:29Z

3

Fellow,

it looks like that your database field is an int value so you can try doing it value by value, like this:

foreach( $Index as $key => $i ) :

    $query = "UPDATE lm_Artikel SET Bestand=".$ergebnis[$key]." WHERE Index = " . $i;
    mysqli_query($query);

endforeach;

Try it.

edited Jan 22, 2014 at 10:43

answered Nov 28, 2012 at 13:22

Alan D'Avila

914 bronze badges

2 Comments

Jason McCreary Over a year ago

Please don't use the mysql_* functions as they are in the deprecation process. Use MySQLi or PDO instead and be a better PHP Developer.

Alan D'Avila Over a year ago

Sorry for my mistake @JasonMcCreary. I was just illustrating a way of doing the query. I don't know what method is he using, I am used to use some framework to do it, like doctrine. Anyway, thanks.!

Community · Accepted Answer · 2017-05-23 11:56:58Z

2

You are susceptible to SQL injections
You cannot use arrays in queries. A query is a string, arrays are not.

You either need to use a loop or use a CASE statement:

UPDATE `lm_Artikel`
SET `Bestandteil` = CASE `Index`
  WHEN <insert id> THEN <insert value>
  WHEN <insert other id> THEN <insert other value>
  <etc>
END

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Nov 28, 2012 at 13:19

phant0m

17k6 gold badges52 silver badges84 bronze badges

1 Comment

Jason McCreary Over a year ago

Interesting use of the CASE statement.

Sven van Zoelen · Accepted Answer · 2012-11-28 13:19:28Z

1

$data_db = array( '3' => 100,
          '8' => 200,
          '11' => 15);


foreach($data_db as $key=>$value) {
    $q = 'UPDATE lm_Artikel SET Bestand=' . $value . ' WHERE `Index` = ' . $key;
    mysqli_query($sql);
}

answered Nov 28, 2012 at 13:19

Sven van Zoelen

7,2595 gold badges40 silver badges49 bronze badges

3 Comments

Jason McCreary Over a year ago

What's the point of creating $data_db when the other two arrays exist?

Sven van Zoelen Over a year ago

It's a visual aid. And why use two if you can combine them and make it more readable and manageble?

Jason McCreary Over a year ago

It's an extra, unnecessary step. Added readability is debatably, especially with a proper variable name, e.g. $indexes. If you're going to suggest it, you should provide code to create it.

Jason McCreary · Accepted Answer · 2012-11-28 13:19:15Z

0

Assuming these are value pairs, i.e. $ergebnis[0] is for $Index[0] and so forth.

foreach ($ergebnis as $key => $value) {
    $sql = 'UPDATE lm_Artikel SET Bestand=' . (int)$value . ' WHERE `Index` = ' . (int)$Index[$key];
    // execute query...
}

A few notes:

You are open to SQL Injection. I used (int) as a quick patch.
I would encourage you to look into Prepared Statements.
You should avoid naming your columns SQL keywords, e.g. Index.

answered Nov 28, 2012 at 13:19

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

1 Comment

Patrick James McDougle Over a year ago

Technically their openness to SQL Injection is debatable. We don't know how those arrays are being generated. If it isn't user submitted, they could be safe. Regardless, prepared statements is probably a better way to go.

Collectives™ on Stack Overflow

php use two arrays in an mysql update query

5 Answers 5

Comments

2 Comments

1 Comment

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related