I have an array of integers, how do I use each one in a mysql query (in php)?

I have an array full of random content item ids. I need to run a mysql query (id in the array goes in the WHERE clause), using each ID that's in the array, in the order that they appear in the said array. How would I do this?

This will be an UPDATE query, for each individual ID in the array.

Answers


As with nearly all "How do I do SQL from within PHP" questions - You really should use prepared statements. It's not that hard:

$ids  = array(2, 4, 6, 8);
$sql  = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id = ?";
$stmt = $mysqli->prepare($sql);
for ($i = 0; $i < count($ids); $i++)
{
    $stmt->bind_param("i", $ids[$i]);
    $stmt->execute();
    echo "Updated record ID: $id\n";
}
$stmt->close();

Alternatively, you can do it along the lines of this:

$ids    = array(2, 4, 6, 8);
$params = implode(",", array_fill(0, count($ids), "?"));
$sql    = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id IN ($params)";
$stmt   = $mysqli->prepare($sql);

call_user_func_array(array($stmt, 'bindparams'), $ids);
$stmt->execute();
$stmt->close();
echo "Updated record IDs: " . implode("," $ids) ."\n";

But obviously the second option would limit you to update all affected records with the same data only, while using the first option you can bind different data to the statement in every loop iteration.

Calling a prepared statement is a lot safer (no SQL injection possible), and doing so in a loop is more efficient as well, because only parameter values are sent to the server on execute() instead of whole statements. In longer-running loops this difference would surely become noticeable.


Need Your Help

gender identification in natural language processing

nlp stanford-nlp

I have written below code using stanford nlp packages.

What is the best way to build up an XML document in .NET?

.net xml

There seem to be many options to create an XML document in .NET. What's the best approach?