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.

BTW, you forgot to use parameters in your DELETE statement. Same on the last UPDATE. — Bill Karwin, Jan 09 '18 at 19:02
Also you should use `ON DUPLICATE KEY UPDATE customerID = VALUES(customerID)` if you want to set the new value instead of the existing value. — Bill Karwin, Jan 09 '18 at 19:03
I didn't want to set new instead of existing. it is a trick i've seen from a guy on stackoverflow. it say insert, but ,if already existing do nothing in a fast way. — Ariel Estrin, Jan 09 '18 at 19:19
Why not just use `INSERT IGNORE` for that? Using `INSERT...ON DUPLICATE KEY UPDATE` performs an insert then potentially an update. — Bill Karwin, Jan 09 '18 at 19:31
@BillKarwin read the reason for that in the comments of the first answer suggested here: https://stackoverflow.com/questions/812437/mysql-ignore-insert-error-duplicate-entry — Ariel Estrin, Jan 09 '18 at 20:32

score 1 · Accepted Answer · answered Jan 09 '18 at 19:01

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.

answered Jan 09 '18 at 19:01

Akar

5,075
2
25
39

1

It's more like using `bindValue()` because you can pass an expression instead of needing to pass an lvalue. Anyway yes, I always use an array argument to `execute()` too. – Bill Karwin Jan 09 '18 at 19:04
Yeah something like that. – Akar Jan 09 '18 at 19:07
but besides that i still need to use productID and customerID over and over in every query. can i store somewhere the binding and call it every time? – Ariel Estrin Jan 09 '18 at 19:21
@ArielEstrin I don't think it's that necessary. – Akar Jan 09 '18 at 19:48

score 0 · Answer 2 · answered Jul 09 '19 at 09:21

Yes, you can get around the repeated variables by defining mySql user variables like this:

$psVars = $dbh->prepare("SET @pid = :productID;");

$psVars->bindParam(':productID', $productID);

$psVars->execute();

Then, in subsequent statements, just use @pid instead of a bound parameter

PDO PHP bindParam() repeated use of same parameters

2 Answers2