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

Question

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.

Answer 1

Using the "IN" Clause

Might be what you're after

$ids = array(2,4,6,8);
$ids = implode($ids);
$sql="SELECT * FROM my_table WHERE id IN($ids);";
mysql_query($sql);

otherwise, what's wrong with

$ids = array(2,4,6,8);
foreach($ids as $id) {
    $sql="SELECT * FROM my_table WHERE ID = $id;";
    mysql_query($sql);
}

Answer 2

You could do something like the following, however you need to be VERY careful that the array only contains integers otherwise you could end up with SQL injection.

You really don't want to be doing multiple queries to get the content out if you can help it. Something like this might be what you are after.

foreach ($array as $key = $var) {
   if ((int) $var <= 0) {
       unset($array[$key]);
   }
}


$query = "SELECT * 
from content 
WHERE contentid IN ('".implode("','", $array)."')";

$result = mysql_query($query);

Answer 3

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.

Answer 4

Amen to Tomalak's comment on statements.

However, if you do not wish to use mysqli, you can always use intval() to prevent injection:

$ids  = array(2, 4, 6, 8);
for ($i = 0; $i < count($ids); $i++)
{
    mysql_query("UPDATE MyTable SET LastUpdated = GETDATE() WHERE id = " . intval($ids[$i]));
}

Answer 5

$values_filtered = array_filter('is_int', $values);
if (count($values_filtered) == count($values)) {
    $sql = 'update table set attrib = 'something' where someid in (' . implode(',', $values_filtered) . ');';
    //execute
} else {
    //do something
}