CODEWITHSUNDEEP
phpmysqlsql
Alesfatalis
Alesfatalis

Reputation: 777

php use two arrays in an mysql update query

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?

Upvotes: 1

Views: 2319

Answers (5)

Alan D'Avila
Alan D'Avila

Reputation: 91

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.

Upvotes: 3

George
George

Reputation: 36784

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

Upvotes: 4

phant0m
phant0m

Reputation: 16905

  1. You are susceptible to SQL injections
  2. 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

Upvotes: 2

Sven van Zoelen
Sven van Zoelen

Reputation: 7229

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

Upvotes: 1

Jason McCreary
Jason McCreary

Reputation: 72971

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.

Upvotes: 0

Related Questions