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, Nov 28 '12 at 13:17

George · Accepted Answer · 2012-11-28T13:34:40.440

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);
}

Alan D'Avila · Answer 2 · 2014-01-22T10:43:29.110

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 '14 at 10:43

answered Nov 28 '12 at 13:22

Alan D'Avila

91
4

3

Please don't use the `mysql_*` functions as they are in the [deprecation process](http://news.php.net/php.internals/53799). Use [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) instead and [be a better PHP Developer](http://jason.pureconcepts.net/2012/08/better-php-developer/). – Jason McCreary Nov 28 '12 at 13:22
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.! – Alan D'Avila Nov 28 '12 at 17:22

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

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 '17 at 11:56

Community

1
1

answered Nov 28 '12 at 13:19

phant0m

16,595
5
50
82

1

Interesting use of the `CASE` statement. – Jason McCreary Nov 28 '12 at 13:23

score 1 · Answer 4 · answered Nov 28 '12 at 13:19

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 '12 at 13:19

Sven van Zoelen

6,989
5
37
48

What's the point of creating `$data_db` when the other two arrays exist? – Jason McCreary Nov 28 '12 at 13:25
It's a visual aid. And why use two if you can combine them and make it more readable and manageble? – Sven van Zoelen Nov 28 '12 at 13:27
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 Nov 28 '12 at 13:29

score 0 · Answer 5 · answered Nov 28 '12 at 13:19

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 '12 at 13:19

Jason McCreary

71,546
23
135
174

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. – Patrick James McDougle Nov 28 '12 at 13:23

php use two arrays in an mysql update query

5 Answers5