How to feed array to prepared statement multiple placeholders?

Question

(I've unsuccessfully tried for days every solution I can find at this and other sites - please help)

I collect an array from the user via html form { $locationreq = $_REQUEST; }, for which the var_dump($locationreq) is:

array (size=1)
  'locationreq' => 
    array (size=3)
      0 => string 'New York' (length=8)
      1 => string 'NY' (length=2)
      2 => string 'USA' (length=3)

I need to feed this array to execute this prepared statement mysql query:

protected function getLocationList($Location) {
    $sql = "SELECT *
            FROM events
            WHERE City = ?, State = ?, Country = ?";

    $stmt = $this->connect()->prepare($sql);
    $stmt->execute([$Location]);

    $result = $stmt->fetchALL(PDO::FETCH_ASSOC);
    return $result;

I have variously tried (and failed):

Binding (but does not seem relevant)
Imploding (again, does not seem relevant)
And this:

foreach ($result as $row) {
        $array = array(0 => $row[0], 1 => $row[1], 2 => $row[2]);
    return $result;

The most common error message is "Warning: Array to string conversion". I know what I have collected from the user is an array but I can't figure out how to get it to populate each placeholder in my prepared statement query.

The most promising and relevant question/solution seems to be this (source: Replace prepared statement placeholders with array elements in PHP ). But I couldn't get it to work. I don't know how to translate this into my code, if it's even the right answer.

<?php
$sql = "SELECT * FROM table WHERE col1 = ? AND col2 = ?";
$array = array(0 => "TEST", 1 => "TEST2");
foreach ($array as $value) 
    $sql = preg_replace("#\?#", "'" . $value . "'", $sql, 1);
echo $sql;

Can you please help?

Quote manual, for the $params parameter of execute: _"An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string."_ - you have three placeholders, but your array does not have three items, it has _one_ - under the key `locationreq`. — CBroe, Sep 05 '22 at 13:59
_"The most promising and relevant question/solution seems to be this..."_ - Please **_never_** use that code. It completely removes _all_ benefits of using prepared statements and will open you up to SQL injection attacks! It's just a round about way of putting the data directly into the query directly (the end result would be the same) — M. Eriksson, Sep 05 '22 at 14:06
CBroe, I see what you mean re array has 1 even though next level down has 3 I need. Am I collecting input incorrectly or how else do I get at the 3 items listed in the var_dump? Using the proposed solution from @Code Spirit, I get an error consistent with what you're saying: "Uncaught ArgumentCountError: Too few arguments to function...1 passed in...exactly 3 expected in..." Input via html form format: — randosev, Sep 05 '22 at 14:08

score -2 · Answer 1 · answered Sep 05 '22 at 13:48

bindParam binds by reference. bindValue by value so you should use it instead when iterating:

    $sql = "SELECT *
            FROM events
            WHERE City = ?, State = ?, Country = ?";

    $stmt = $this->connect()->prepare($sql);
    foreach ($params as $i => $param) {
      $stmt->bindValue($i + 1, $param);
    }

    $stmt->execute();

How to feed array to prepared statement multiple placeholders?

1 Answers1