SQL query deleting all rows instead of specific row-CodePudding

foreach ($toRemove as $remove){
            $sql = "DELETE FROM orders WHERE productId = :productId AND invoiceNo = :invoiceNo AND quantity = :quantity";
            $stmt = $pdo1->prepare($sql);
            $stmt->bindParam('productId', $remove[0], PDO::PARAM_INT);
            $stmt->bindParam('invoiceNo', $invoice->invoiceNo, PDO::PARAM_INT);
            $stmt->bindParam('quantity', $remove[1], PDO::PARAM_INT);
            $stmt->execute();
        }

$remove structure: ['productId', 'quantity']

'productId' is a string eg. 'C-1'

I expected this query to only remove 1 row, however, it is removing all rows that has 'productId' with 'C-x' format. This query works fine when 'productId' is purely an 'int', without the C in front, however, due to requirements, I cannot remove the C as it represents a custom product.

CodePudding user response：

You need to identify productId as a string for PDO param binder. Replace this:

$stmt->bindParam('productId', $remove[0], PDO::PARAM_INT);

With

$stmt->bindParam('productId', $remove[0], PDO::PARAM_STR);

For reading: https://www.php.net/manual/en/pdo.constants.php