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
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);
Using prepared statements you can do:
<?php
$expiringDomains = array('dom1.com', 'dom2.com');
// Build a string of comma separated question marks (as many as there are domains)
$qm = substr(str_repeat(',?', count($expiringDomains)), 1);
$sth = $db->prepare("
UPDATE customer_domains SET reminder1 = '', reminder2 = '', reminder3 = ''
WHERE domain NOT IN($qm)
");
$sth->execute($expiringDomains);
?>
Edit: If you are using MySQLi, you can do it in the following way, since MySQLi doesn't support arrays as execute() parameter (thanks to Jeff in the comments).
call_user_func_array(array($sth, "bind_param"), $expiringDomains);
$sth->execute();
mysqli_query etc... But anyhow OP can loop over the array to bind each of them using bind_param().Don't use mysqli for that if you can avoid it, it'll just give you headaches. Use PDO instead. Something like:
$sth = $pdo->prepare(
"UPDATE mytable SET foo=1 WHERE domain NOT IN(" .
implode(", ", array_fill(0, count($expiringDomains), "?")) .
")"
);
$sth->execute($expiringDomains);
$result = $sth->fetchAll();
If you really want to use mysqli, the approach you'd use is similar but instead of providing the data in execute() you'd have to either make a very complicated single bind_param call (involving call_user_func_array() unless the number of elements in your array is fixed) or loop over making several bind_param calls.
where domain NOT IN ('dom1', 'dom2')