How to Efficiently Update Multiple MySQL Records Using PHP with Array of IDs?

Iterating Through an Array of IDs for MySQL Queries

When working with MySQL queries in PHP, you may need to incorporate multiple values from an array into the query. This guide demonstrates how to achieve this efficiently using both prepared statements and dynamic SQL queries.

Prepared Statements (Recommended)

Prepared statements offer enhanced security and efficiency. Here's how to use them:

$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: $ids[$i]";
}

$stmt->close();

Dynamic SQL Queries

Alternatively, you can construct a dynamic SQL query dynamically:

$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));
$args = array_merge(array($types), $ids);
call_user_func_array(array($stmt, 'bind_param'), ref($args));

$stmt->execute();

echo "Updated record IDs: " . implode(",", $ids);

Comparison and Usage Guidelines

• Prepared Statements: Iterative execution of multiple database operations. More suitable for INSERT and UPDATE queries.
• Dynamic SQL: Executes a single, more efficient query for all records. Ideal for SELECT, DELETE, and UPDATE operations involving the same data.

Benefits of Prepared Statements

• Security: Prevents SQL injection attacks.
• Efficiency: Re-uses compiled statements for multiple executions.
• Compactness: Transmits only parameter values during execution.

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