Mysql, update where not in array php

Question

I am trying to update with 0 the rows that is not in the array I get from the xml.

$sus = array();
foreach( $xml->property as $node ) {
  $sus[] = $node->suid;
}
$A = "'".implode("','",$sus)."'";
 echo $A;
$sth = $dbh->prepare("UPDATE tabla SET alta = 0
WHERE suid NOT IN ($A)");
$sth->execute($sus);

When I echo $A it prints it out correctly like this: '60','62','65','73','74','79','83','90','112','124' However it does not do the update, whats wrong?

Clorichel · Accepted Answer · 2017-04-30 18:10:42Z

1

You should start by escaping your XML values to avoid SQL injection:

$escapedValues = str_repeat('?,', count($sus) - 1) . '?';
$sth = $db->prepare("UPDATE tabla SET alta = 0 WHERE suid NOT IN ($escapedValues)"
$sth->execute($sus);

edited Apr 30, 2017 at 18:10

answered Apr 30, 2017 at 18:00

Clorichel

2,1001 gold badge16 silver badges25 bronze badges

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

2 Comments

Helenp Over a year ago

That worked thanks, did´t know escapes where needed on pdo updates. There is a bug with the names in your query. Here is the full code in case its useful for anybody: $sus = array(); foreach( $xml->property as $node ) { $sus[] = $node->suid; } $escapedValues = str_repeat('?,', count($sus) - 1) . '?'; $sth = $dbh->prepare("UPDATE tabla SET alta = 0 WHERE suid NOT IN ($escapedValues)"); $sth->execute($sus);

Clorichel Over a year ago

Great! Glad for you. Just edited my answer with the appropriate variable name :)

Collectives™ on Stack Overflow

Mysql, update where not in array php

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related