Symfony2 + DBAL. How to use bindValue for multiple insert?

Question

I'm using DBAL and I want to execute multiple insert query. But I have the problem: bindValue() method not working in loop. This is my code:

    $insertQuery = "INSERT INTO `phonebook`(`number`, `company`, `user`) VALUES %s 
           ON DUPLICATE KEY UPDATE company=VALUES(company), user=VALUES(user)";

    for ($i = 0; $i < count($data); $i++) {
        $inserted[] = "(':number', ':company', ':user')";
    }

    $insertQuery = sprintf($insertQuery, implode(",", $inserted));
    $result = $db->getConnection()->prepare($insertQuery);

    for ($i = 0; $i < count($data); $i++) {
        $result->bindValue($data[$i]["number"]);
        $result->bindValue($data[$i]["company"]);
        $result->bindValue($data[$i]["user"]);
    }

    $result->execute();

As result I received one-line table with fields: :number, :company, :user.

What am I doing wrong?

Thanks a lot for any help!

Answer 1

The problem you're having is that your binding has no way to determine to which placeholder it should be doing the binding with. To visualize it better, think on the final DBAL query you're generating:

INSERT INTO `phonebook`(`number`, `company`, `user`) VALUES
(':number', ':company', ':user'),
(':number', ':company', ':user'),
(':number', ':company', ':user');

When you do the binding, you're replacing all the parameters at the same time, ending up with a single row inserted.

One possible solution would be to give different parameter names to each row and then replace each one accordingly. It would look like something similar to this:

public function randomParameterName()
{
    return uniqid('param_');
}

...

$parameters = [];
for ($i = 0; $i < count($data); $i++) {
    $parameterNames = [
        'number' => $this->randomParameterName(),
        'company' => $this->randomParameterName(), 
        'user' => $this->randomParameterName(),
    ];
    $parameters[$i] = $parameterNames;
    $inserted[] = sprintf("(':%s', ':%s', ':%s')",
        $parameterNames['number'], 
        $parameterNames['company'], 
        $parameterNames['user']
    );
}

$insertQuery = sprintf($insertQuery, implode(",", $inserted));
$result = $db->getConnection()->prepare($insertQuery);

foreach ($parameters as $i => $parameter) {
    $result->bindValue($parameter['number'], $data[$i]["number"]);
    $result->bindValue($parameter['company'], $data[$i]["company"]);
    $result->bindValue($parameter['user'], $data[$i]["user"]);
}

You could probably extend your $data variable and incorporate the new parameter names into it. This would remove the need of yet another array $parameters to hold reference to the newly created parameter names.

Hope this helps