In PHP PDO, how can I bind named parameters of a prepared statement without knowing their names?

Question

I am trying to write a helper query function to return a resultset to the caller, but when the caller provides a prepared statement using named params, I am not sure how to bind them in a function.

function queryDB(string $query, array $param=null) {
  
  global $dbh; //reference the db handle declared in init.php 

  if (isset($param)) { //query params provided, so a prepared statement
    
    $stmt = $dbh->prepare($query);
    for($i = 1; $i <= count($param);$i++) { //bind the parameters 1-by-1
      $stmt->bindParam($i, $param[$i]); //
    }
    $stmt->execute();

  } else { //a straight sql query, not a prepared statement

    $stmt = $dbh->query($query);   

  }
  $result = $stmt->fetchAll();
  return $result;
}

If I call queryDB($query, [$name, $age]) with an unnamed-param prepared statement such as $query = INSERT INTO users (name, age) VALUES(?, ?) and $name = "trump"; $age = 18, that code should work.

But there can be times when I (or someone else) will call with a named-param prepared statement such as $query = INSERT INTO users (name, age) VALUES(:name, :age) and $name = "trump"; $age = 18. The existing bindParam(i, $value) shouldn't work but then the function wouldn't know those :name, :age, :whatever named parameters. How should I write the bindParam(param, value) to accommodate both named and unnamed prepared statements? assuming params will be provided in the matched order even when named.

Answer 1

There's absolutely no reason to use bindParam.

If your SQL has named placeholders then your array must be associative. You need to call it like this:

queryDB($query, ['name' => $name, 'age' => $age]);

You could then loop with foreach($params as $key => $value) and use bindValue instead of bindParam, but, as I said, there's absolutely no reason to use it.

Instead, pass the array to execute.

function queryDB(PDO $dbh, string $query, ?array $param = null)
{
    $stmt = $dbh->prepare($query);
    $stmt->execute($param);
    return $stmt;
}

P.S. You can even remove the if statement and the call to query. This method does the same thing as prepare and execute. There's no reason to have a special case like this in your code.

Also, this function will become much more flexible if if would return just $stmt. This way you can use it for DML queries as well and also use many other fetch modes supported by PDO other than simple fetchAll(), i.e. queryDB($dbh, $query, $param)->fetchAll(PDO::FETCH_KEY_PAIR);