pdo bind param for multiple when and then statements-CodePudding

I need to update multiple rows in a single table at once. So I used following query:

UPDATE color
    SET color_id= CASE color_id
        WHEN 45 THEN 56
        WHEN 64 THEN 78
    END
WHERE color_id IN (45,64)

I have to use prepared statement in pdo structure, so altered it as:

$query = "UPDATE color SET color_id= CASE color_id WHEN :old THEN :new END WHERE color_id = :old";
$stmt = $conn->prepare($query);
$params[0] = array(array("var"=>":old","val"=>"45"),array("var"=>":new","val"=>"56"));
$params[1] = array(array("var"=>":old","val"=>"64"),array("var"=>":new","val"=>"78"));
 foreach ($params as $value) {
    foreach ($value as $row) {
        $stmt->bindParam($value['var'], $value['val'], PDO::PARAM_STR);
    }
    $stmt->execute();
 }
 
 $result = $stmt->fetchAll();

I know this method will be bit slower than previous method. My main goal is to prevent sql injection. Is there any other way of implement 1st query with prepared statement to make it as fast as 1st query?

CodePudding user response：