Using one parameter multiple times in prepared mysqli-statement

Question

Is it possible to use one parameter in a prepared mysqli-statement multiple times with only binding it one time?
something like this

$stmt = $mysqli->prepare(SELECT * FROM user WHERE age BETWEEN ?1 - 2 AND ?1 + 2);
$stmt->bind_param('i', $myAge);

I think this is possible with PDO, but I don't konw how to do this with mysqli.

Criticize

That's not possible with plain PDO either. – Achilles

Unfortunately no. PDO has named parameters; while MySQLi does not. As such you'll need to bind it twice. – Washedup

Ok. So I realy have to bind the parameters multiple times. Its no problem but I thought there ist a nicer way to do this. – Criticize

You can do this with named parameters in PDO, but only when PDO is configured to emulate prepares. – Starlin

Answer 1

Just to close the question:

The answer is no.

If you want to bind a parameter only one time and using it multiple times in a query you have to use PDO and this maybe also needs a special configuration.

But there seems to be more reasons to use PDO instead of mysqli, according to this great answer or this.

But sure there are workarounds. See the other answers to this question.

Criticize

The mysqli over PDO sentiment is not relevant anymore though. – Ephemerality

Answer 2

You can use this instead

$stmt = $mysqli->prepare(SELECT * FROM user WHERE age BETWEEN ? - 2 AND ? + 2);
$stmt->bind_param('ii', $my_age, $my_age);

Lagena

Answer 3

Since PHP 8.1 you can use execute without bind_param. I am using array_fill to do the trick.

$stmt->execute(array_fill(0, 1, $myage));

You can edit the array_fill values depending on the statement, Suppose you have 3 values to fill, you can use:

$stmt->execute(array_fill(0, 2, $myage));

Scintillant

Answer 4

I use this solution:

$stmt = $mysqli->prepare("SET @age := ?;");
$stmt->bind_param('i', $myAge);
$stmt->execute();

$stmt = $mysqli->query("SELECT * FROM user WHERE age BETWEEN @age - 2 AND @age + 2;");

Objurgate

But why? it takes much more code than normal approach. Why bother? – Ephemerality

Better readability? Obviously is not the better solution, so I use it in just few cases, but sometimes could help, for example with stored procedures. – Objurgate