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

DB2 and Report Studio: convert string date to numeric

db2 report ibm cognos

I have a date in this format: Jan 1, 2013 (taken from the date prompt in report studio)

Unit testing MFC UI applications?

c++ unit-testing user-interface mfc tdd

How do you unit test a large MFC UI application?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.