我有一个整数数组，我如何使用（在PHP）在MySQL查询每一个？ [英] I have an array of integers, how do I use each one in a mysql query (in php)?

问题描述

我有一个数组全随机的内容项ID的。我需要（在阵列而来的WHERE子句中的id）运行MySQL查询，使用每个它们出现该数组中的数组中，在订单ID。我会怎么做呢？

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?

此将是一个UPDATE查询，对于阵列中的每个单独的ID。

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

推荐答案

作为与几乎所有的我该怎么做SQL从PHP内的问题 - 你的真正的应该用prepared语句。这并不难：

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.

调用ppared语句$ P $是安全多了（没有SQL注入可能），在一个循环中这样做更有效的为好，因为只有参数值是在发送到服务器执行（），而不是整个报表。在长时间运行的循环这种差异将必将成为明显的。

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.

这篇关于我有一个整数数组，我如何使用（在PHP）在MySQL查询每一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！