CODEWITHSUNDEEP
phppdoprepared-statement
user14436083
user14436083

Reputation:

How to bind multiple values using PDO prepared statements?

Here is my code

$bindParams = [
'name1'  => 'Law',
'name2'  => 'King'
              ];

$sql = "INSERT INTO users (name) VALUES (:name1),(:name2);";

$db->prepared_query($sql,$bindParams);



class Database
{
public function __construct
(
    public \PDO $PDO
)
{}

public function prepared_query(string $sql='',$bindParams=[]): bool
{
    $stmt = $this->PDO->prepare($sql);

    if(count(value:$bindParams) !=0)
    {
        foreach ($bindParams as $k => $v)
        {
            $stmt->bindParam(param:":$k",var:$v);
        }
    }
    $x = $stmt->execute();

    if($x)
    {
        return true;
    }else{
             return false;
         }
    }
}

The problem I am facing is that in the foreach loop the second value from array $bindParams having key name2 and value king is overriding $stmt->bindParam and only name2 king is inserted in the database. And for every insert I am getting king in database. Here is screen shot of database. How can I insert both records from array sucessfully without repetition.

enter image description here

Upvotes: 1

Views: 1064

Answers (1)

Dharman
Dharman

Reputation: 33238

You can simply pass the parameter in execute(). There is no need to use bindParam() which binds by reference and will overwrite your values inside a loop.

Replace your code with this:

public function prepared_query(string $sql, array $bindParams=[]): void
{
    $stmt = $this->PDO->prepare($sql);
    $stmt->execute($bindParams);
}

If you really want to have a loop, which you really do not need, then you have to bind by value and not by reference. e.g.

public function prepared_query(string $sql, array $bindParams = []): void
{
    $stmt = $this->PDO->prepare($sql);
    foreach ($bindParams as $param => $var) {
        $stmt->bindValue($param, $var);
    }
    $stmt->execute();
}

Upvotes: 1

Related Questions