How to Efficiently Update Multiple MySQL Records Using an Integer Array in PHP?

How to Employ an Array of Integers in MySQL Queries (PHP)

Problem:

You require a way to utilize an array of random content item IDs in a MySQL query, with each ID being placed in the WHERE clause in the order of its appearance in the array. This query should be an UPDATE for each individual ID in the array.

Solution:

Using prepared statements for SQL operations within PHP is highly recommended. Prepared statements offer increased security by preventing SQL injection attacks and enhancing efficiency through database statement compilation. Two approaches you can consider are:

Iterative Approach:

$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();

Single-Execution Approach:

$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);

$types = str_repeat("i", count($ids)); // "iiii"
$args = array_merge(array($types), $ids); // ["iiii", 2, 4, 6, 8]
call_user_func_array(array($stmt, 'bind_param'), ref($args)); // $stmt->bind_param("iiii", 2, 4, 6, 8)

$stmt->execute();

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

For additional fields you need to add, simply append more parameter placeholders to the SQL statement.

Which Approach to Choose:

• Iterative Approach: Suitable for UPDATE and INSERT operations where database calls are made for each record.
• Single-Execution Approach: Efficient for SELECT and DELETE queries, or for UPDATE operations where all records are modified similarly.

Advantages of Prepared Statements:

• Enhanced security against SQL injection attacks.
• Improved efficiency due to statement compilation and reduced data transfer.
• Prevention of repetitive full SQL string transmissions to the database.

The above is the detailed content of How to Efficiently Update Multiple MySQL Records Using an Integer Array in PHP?. For more information, please follow other related articles on the PHP Chinese website!