PDO PHP bindParam() repeated use of same parameters

Question

Yesterday i decided to learn PDO and rewrite our server php to PDO.

The thing that jumped to my mind while rewriting the code is the need of repeated use of bindParam for the same parameters i already used.

Here is an example:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $dbh->beginTransaction();
    $stmt = $dbh->prepare("INSERT INTO Products(productID,numOfLikes) VALUES (:productID,0) ON DUPLICATE KEY UPDATE productID = productID;");
    $stmt->bindParam(":productID",$productID);
    $stmt->execute();

    if($customerID !== 0){  
        //*****Check, if customerID is in the Database, else add the customerID to the Database.
        $stmt = $dbh->prepare("INSERT INTO Customers(customerID) VALUES (:customerID) ON DUPLICATE KEY UPDATE customerID = customerID;");
        $stmt->bindParam(":customerID",$customerID);
        $stmt->execute();

        //*****if customerID and productID are NOT registered together ,then register and add +1 to productID numOfLikes
        $stmt = $dbh->prepare("SELECT customerID, productID FROM CustomerProducts WHERE productID = :productID AND customerID = :customerID");          
        $stmt->bindParam(":productID",$productID);
        $stmt->bindParam(":customerID",$customerID);
        $stmt->execute();

        if ($stmt->rowCount() == 0) {
            //echo "added";
            $stmt = $dbh->prepare("INSERT INTO CustomerProducts(customerID, productID) Values (:customerID,:productID)");
            $stmt->bindParam(":customerID",$customerID);
            $stmt->bindParam(":productID",$productID);
            $stmt->execute();

            $stmt = $dbh->prepare("UPDATE Products SET numOfLikes = numOfLikes + 1 WHERE productID = :productID");
            $stmt->bindParam(":productID",$productID);
            $stmt->execute();  
        }else {
            //echo "removed";
            $stmt = $dbh->prepare("DELETE FROM CustomerProducts WHERE productID = ".$productID." AND customerID = ".$customerID);
            $stmt->bindParam(":customerID",$customerID);
            $stmt->bindParam(":productID",$productID);
            $stmt->execute();

            $stmt = $dbh->prepare("UPDATE Products SET numOfLikes = numOfLikes - 1 WHERE productID = ".$productID);
            $stmt->bindParam(":productID",$productID);
            $stmt->execute();  
        }
    }
    $dbh->commit();

Is there a way to write it in "prettier way"? Can you see any flows in that could. I would appreciate every help.

Note: this code will be for production use in the near future.

Answer 1

Yes there is...

You can supply bindParam as an array to the execute function...

Something like this:

$statement->execute([
    ':username'=> $username,
    ':password'=> $password
]);

It's using bindParam and execute in just one statement, and it looks cleaner in my opinion.