Can you interpolate a variable into a string by reference?

Question

My overall goal is to bind a variable to a prepared statement, but I need to do it by reference. I know that mysqli_stmt::bind_param already binds variables by reference, but I need it to work with a variable number of variables. E.g:

$stmt = $conn->prepare("SELECT * FROM accounts WHERE id IN (?, ?, ?, ? /* unknown number of question marks */);");

To get around this, I came across this solution (the second code block in the accepted answer): I have an array of integers, how do I use each one in a mysql query (in php)?

In this solution, the bound values $params are interpolated into the query. However, I won't know what number of parameters I'll need, until later on:

// I don't know yet how many values will be needed for the IN expression.

$stmt = $conn->prepare("SELECT * FROM ingredients WHERE id IN ($params);");

while ($doingSomething) {
    $ids = getTheIds();

    // Okay, now I know how many.

    $params = implode(",", array_fill(0, count($ids), "?")); // $params can finally be set

    $types = str_repeat("i", count($ids));                        // "iiii"
    $args = array_merge(array($types), $ids);                     // ["iiii", 2, 4, 6, 8]
    call_user_func_array(array($stmt, 'bind_param'), ref($args)); // $stmt->bind_param("iiii", 2, 4, 6, 8)

    $stmt->execute();
    // The statement fails because $params wasn't set when the query was prepared
}

I could put the statement preparation inside the loop, after I know the size of $ids, but this would kind of defeat the purpose of preparing it in the first place.

In my mind, if the $params in the prepared query was passed by reference, this would solve my problem. Is this possible, or perhaps am I looking at this whole problem wrong?

IMHO https://stackoverflow.com/a/48388482/1213708 is the way to do it. You will have to delay the prepare until you know what `$params` is though. — Nigel Ren, Mar 29 '20 at 17:37
A `??` placeholder is a common idiom for a query builder function. (Instead of littering the count/array_fill/implode thing everywhere.) — mario, Mar 29 '20 at 17:51

score 1 · Answer 1 · answered Mar 29 '20 at 21:50

You can't prepare a query until you know exactly what this query will look like. In your case you are building the SQL dynamically based on the number of placeholders you need. Until you know the number of placeholders you can't prepare the query.

You need to do it inside the loop.

while ($doingSomething) {
    $ids = getTheIds();

    // Okay, now I know how many.

    $params = implode(",", array_fill(0, count($ids), "?")); // $params can finally be set
    $stmt = $conn->prepare("SELECT * FROM ingredients WHERE id IN ($params);");

    $types = str_repeat("i", count($ids));  // "iiii"
    $stmt->bind_param($types, ...$ids);

    $stmt->execute();
}

On a side note:

I recommend to switch to PDO, where such things are much simpler. If you are starting a new project do not use mysqli class.
Please make sure you have error reporting enabled for mysqli. See How to get the error message in MySQLi?
Do not use call_user_func_array function when calling bind_param. You are only making it more complex than you should.

Can you interpolate a variable into a string by reference?

1 Answers1