charlie charlie - 5 months ago 11
PHP Question

update mysql table where rows are not in php array

i have an array of items:

$expiringDomains = array('dom1.com', 'dom2.com');


how can i run an update on my table to update all rows where the
domain
in the table is NOT in the array

$sql = $mysqli->query("UPDATE FROM customer_domains set reminder1 = '', reminder2 = '', reminder3 = '' WHERE domain = '';");


i know i could do a PHP loop through the array but i want it to run as quick as possible - the array could get up to 20,000 items or more

Answer

quickest dirtiest way to execute unescaped, unprepared trusted data like this requires knowledge that your domains will be valid domains without ' characters or other inject-able nonsense.

you also have an error in your SQL statement, there shouldn't be a FROM in an UPDATE statement.

<?php

$expiringDomains = array('dom1.com', 'dom2.com');

$query = "
  UPDATE customer_domains
  SET reminder1 = '', reminder2 = '', reminder3 = ''
  WHERE domain NOT IN ('".implode("','",$expiringDomains)."');
";

$sql = $mysqli->query($query);