Re: Possible RFC: PDOStatement::addBatch
| From: | Vinicius Dias | Date: | Fri, 16 Jun 2023 17:14:43 +0000 |
| Subject: | Re: Possible RFC: PDOStatement::addBatch | ||
| References: | 1 2 | Groups: | php.internals |
| Request: | Send a blank email to internals+get-120603@lists.php.net to get a copy of this message | ||
> Hi Vinicius,
>
> What would be the reason to add this? A reason cannot just be that there is no such function.
> You can easily do it with a loop like you have just shown. There isn't really any other way
> > unless we introduce some driver specific SQL builder. So what would we gain from it? Is it
> speed, security, efficiency?
>
> String concatenation isn't an alternative though.
>
> Regards,
> Kamil
Great question, Kamil. This new method would not add new capabilities to PHP.
It would make things easier for a batch insert. Currently, we would
need something like the following. This is a real world production
code, not written by me. I just decreased the number of columns and
changed table/column names:
```
<?php
$values = implode(",", array_fill(0, count($data_list), "(?, ?)"));
$pdo = PDODataDB::getInstance();
$statement = $pdo->prepare(
"INSERT INTO
table_name(
column_1,
column_2
) VALUES
{$values}"
);
$counter = 0;
foreach ($data_list as $data) {
$statement->bindValue(++$counter, $data['specific_key'], PDO::PARAM_INT);
$statement->bindValue(++$counter, $data['another_key'], PDO::PARAM_INT);
}
$statement->execute();
$statement = null;
```
So when I said about string concatenation, that's what I meant. With
the new method, we would have something like:
```
<?php
$pdo = PDODataDB::getInstance();
$statement = $pdo->prepare("INSERT INTO table_name(column_1,
column_2) VALUES (?, ?)");
foreach ($data_list as $data) {
$statement->bindValue(1, $data['specific_key'], PDO::PARAM_INT);
$statement->bindValue(2, $data['another_key'], PDO::PARAM_INT);
$statemnt->addBatch();
}
$statement->execute();
$statement = null;
```
But the biggest pro isn't this case. It's the case where beginners
would have multiple individual INSERT queries being separately sent to
the database because they are not familiar with the approach of
concatenating the parameters to the query string. I hope I made it a
bit more clear now, so to sum it up: this new method would not bring
any new functionality to PHP, but it would:
1. Make the code a bit more clear;
2. Help newcomers to write bulk INSERTs.
> I have a library that abstracts this for you, if that helps:
> https://github.com/brick/db
> - Benjamin
That BulkInserter class would basically be the point of this new
addition to the core, Benjamin. But a lot of new developers are not
even familiar with composer, so adding an external library is out of
their reality. Having an addBatch method in PHP's core would
definitely help them write a better and more performatic code.
I once again appreciate the attention and the replies.
Vinicius Dias.