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
– Bill Karwin, Commented Jan 9, 2018 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
– Bill Karwin, Commented Jan 9, 2018 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
– Ariel Estrin, Commented Jan 9, 2018 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
– Bill Karwin, Commented Jan 9, 2018 at 19:31

Akar · Accepted Answer · 2018-01-09 19:01:18Z

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 9, 2018 at 19:01

Akar

5,4353 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bill Karwin Over a year ago

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.

Akar Over a year ago

Yeah something like that.

Ariel Estrin Over a year ago

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?

Akar Over a year ago

@ArielEstrin I don't think it's that necessary.

Simon · Accepted Answer · 2019-07-09 09:21:31Z

0

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

answered Jul 9, 2019 at 9:21

Simon

2271 gold badge2 silver badges6 bronze badges

Collectives™ on Stack Overflow

PDO PHP bindParam() repeated use of same parameters

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related