How to bind params to multiple values without parameter repetition

Question

My prepared statement looks something like this:

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), ?, ?, ?),
        (LAST_INSERT_ID(), ?, ?, ?)
        ");
$stmt20->bind_param('sdssds', $m1, $zero, $parcela_situacao_prog, $m2, $zero, $parcela_situacao_prog);
$stmt20->execute();

As you can see, the params are repeating, except for $m1 and $m2. Can I bind these repeated params to multiple values and keep these m1 and m2? Even if I had to insert many values, the binding order wouldn't change.

You only have to do the `bind_param` once. After that, you can just change the variable values and call `$stmt20->execute()` again. — Nick, Dec 07 '18 at 11:17
What about the variables, I have to repeat them for each row? — Luann Sousa, Dec 07 '18 at 11:22
No. Just use the first two lines of your code **once**, then for as many inserts as you need set the values of `$m1, $zero, $parcela_situacao_prog, $m2` to the desired values and call `$stmt20->execute()` — Nick, Dec 07 '18 at 11:24
You want to use named parameters: http://php.net/manual/en/pdo.prepare.php. — Gordon Linoff, Dec 07 '18 at 11:38
@Nick Sorry, I don't get it, I use bind just once, and execute again? — Luann Sousa, Dec 07 '18 at 12:42
So I'm probably doing somehting wrong with the syntax, how can I bind just once if I am inserting two records? "$m1, $zero, $parcela_situacao_prog" and "$m2, $zero, $parcela_situacao_prog"? — Luann Sousa, Dec 07 '18 at 13:01

score 0 · Answer 1 · answered Dec 07 '18 at 13:07

0

Instead of using question mark parameters, you need to use named parameters. See the php docs.

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), :m1, :zero, :parcela_situacao_prog),
        (LAST_INSERT_ID(), :m2, :zero, :parcela_situacao_prog)
        ");
$stmt20->execute(array(
    ':m1' => $m1, 
    ':m2' => $m2, 
    :zero => $zero, 
    :parcela_situacao_prog => $parcela_situacao_prog 
));

answered Dec 07 '18 at 13:07

GMB

216,147
25
84
135

I am using mysqli instead PDO, is there a problem? – Luann Sousa Dec 07 '18 at 13:34
that should do it as well – GMB Dec 07 '18 at 13:58

Caius Jard · Accepted Answer · 2018-12-07T13:49:20.037

Typically you wouldn't try and double up like this, you'd prepare your statement once and run it twice with different values. I'm not a PHP coder so I borrowed from GMB's post:

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), :m, :zero, :parcela_situacao_prog)
        ");

$stmt20->execute(array(
    :m => $m1, 
    :zero => $zero, 
    :parcela_situacao_prog => $parcela_situacao_prog 
));

$stmt20->execute(array(
    :m => $m2, 
    :zero => $zero, 
    :parcela_situacao_prog => $parcela_situacao_prog 
));

If you had 1000 values to insert, you'd prepare the statement once and execute it 1000 times (in a loop)..

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), :m, :zero, :parcela_situacao_prog)
        ");

for ($x = 0; $x <= 1000; $x++) {
  $stmt20->execute(array(
    :m => $array_of_m[$x], 
    :zero => $zero, 
    :parcela_situacao_prog => $parcela_situacao_prog 
  ));
}

Digging around in the php help docs for mysqli, I guess the code for your situation might be more like:

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), ?, ?, ?)
        ");

$stmt20->bind_param('sdssds', $m, $zero, $parcela_situacao_prog);

for ($x = 0; $x <= 1000; $x++) {
    $m = $array_of_m[$x];
    $stmt20->execute();
}

Chiefly I'm trying to get across this notion that you set things up once, then jsut change the values and repeatedly call execute..

As notd, i'm not a php coder.. I referenced this: PHP/mysql: mysqli prepared statement insert same value multiple times within for loop

Nearly every language I've worked with has this "prepare once, repreatedly bind/execute" pattern. Looking at the help docs for mysqli it seems no different — Caius Jard, Dec 07 '18 at 13:40
I made an edit, but I don't profess to be a php expert, so the minor stuff like syntax errors might be something you need to fix up; i've no environment to even test this. I'm purely trying to get across a concept of setting up the query once, and then many times doing a "set values/execute" repetition — Caius Jard, Dec 07 '18 at 13:51
There it goes! Thank you man, I have studied your answers and with some changes, it did the trick. — Luann Sousa, Dec 07 '18 at 14:41

How to bind params to multiple values without parameter repetition

2 Answers2