php - Bind multiple parameters into mysqli query

Question

Right now I need to use the following structure to cope with binding multiple parameters into a mysqli query:

if ($words_total == 1)
{
    $statement -> bind_param("s", $words[0]);
}
else if ($words_total == 2)
{
    $statement -> bind_param("ss", $words[0], $words[1]);
}
else if ($words_total == 3)
{
    $statement -> bind_param("sss", $words[0], $words[1], $words[2]);
}

//and so on....

I work out the number of question marks using the code below and insert it into my query:

$marks = "";
for($i = 1; $i<=$words_total; $i++) {
    if ($i == $words_total)
    {
        $marks .= "?";
    }
    else
    {
        $marks .= "?,";
    }
}

My question is surely there must be a way of handling as many inputs into the query as I need dynamically. Hardcoding the bind_param() seems like a really bad way of handling this.

I am using php version 5.4.10

Answer 1

Unfortunately, by default, bind_param() doesn't accept an array instead of separate variables. However, since PHP 5.6 there is a magnificent improvement that will do the trick.

To bind an arbitrary number of variables into mysqli query you will need an argument unpacking operator. It will make the operation as simple and smooth as possible.

For example, to use a PHP array with a mysql's IN() operator, you will need the following code

// our array
$array = ['a','b','c']; 

// create an SQL query with placeholders and prepare it
$in    = str_repeat('?,', count($array) - 1) . '?'; //  returns ?,?,?...
$sql   = "SELECT name FROM table WHERE city IN ($in)"; 
$stmt  = $mysqli->prepare($sql);

// create the types string dynamically and bind an array
$types = str_repeat('s', count($array)); // returns sss...
$stmt->bind_param($types, ...$array); 

// execute and fetch the rows
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data