prepared statement with varable inpunt

Question

I'm trying to make a prepared statement that has a variable amount of input variables. for example:

$sql = "INSERT INTO MyGuests (firstname, lastname, email, tel) VALUES (?, ?, ?, ?)";
$types = "ssss";
$result_array = array of four different strings
$update = $conn->prepare($sql);
$update->bind_param($types,$result_array);

I know that this won't work but it's an example

note:

result_array, types and the fields in $sql always have the same amount as eachother

So if result_array has 5 values then $types and the values in $sql would also have 5

but it could variable in my code so 5 values could also be 6 or 2 values

so my question is:

Is it possible to have a variable amount of input values in an prepared statement

With PDO it's easy. With mysqli less easy but there are ways, people have done it before - you should be able to search for previous examples. — ADyson, Jun 09 '21 at 15:53

AbraCadaver · Accepted Answer · 2021-06-09T16:08:06.063

This would be easier with PDO, but for this example, you have an array so you can count it and build what you need. If you already have $result_array:

// build placeholders ?, ? based on array length
$vals = implode(',', array_fill(0, count($result_array), '?'));

// use $vals placeholders
$sql = "INSERT INTO MyGuests (firstname, lastname, email, tel) VALUES ($vals)";

// build types based on array length *this is only string
$types = str_repeat('s', count($result_array));

$update = $conn->prepare($sql);

// unpack the array to individual arguments with splat ...    
$update->bind_param($types, ...$result_array);

Note: If $result_array is an associative array (string indexes) then you will have to use array_values before using it in the bind_param with ....

prepared statement with varable inpunt

1 Answers1