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

user_13615381 0
Viewed 435

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.

function update following comments by @Dharman


/*
Helper function to query database and return the full resultset.
@param $query: the SQL query string, can be either a straight query (without any external inputs) or a prepared statement using either named parameters (:param) or positional (?)
@param $param: the values, in array, to bind to a prepared statement, [value1, value2, ...] or ["name1" => value1, "name2" => value2, ...] for positional or named params
@return full resultset of the query
*/
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); //set up the prepared statement

    $isAssocArray = count(array_filter(array_keys($param), "is_string")) == 0 ? false : true; //boolean flag for associative array (dict, with keys) versus sequential array (list, without keys)  
    
    if ($isAssocArray) { //the prepared statement uses named parameters (:name1, :name2, ...)
      
      foreach($param as $name => $value) { //bind the parameters 1-by-1
        if (substr($name, 0, 1) != ":") { //if the provided parameter isn't prefixed with ':' which is required in bindParam()
          $name = ":".$name; //prefix it with ':'
        }
        $stmt->bindParam($name, $value);
      }

    } else { //the prepared statement uses unnamed parameters (?, ?, ...) 
      
      for($i = 1; $i <= count($param); $i++) { //bind the parameters 1-by-1
        $stmt->bindParam($i, $param[$i]); 
      }

    } //the prepared statement has its values bound and ready for execution

    $stmt->execute();

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

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

  }

  $resultset = $stmt->fetchAll();
  return $resultset;

}
pdo mysql php
1 Answers

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 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->fetchAll();
}

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.

user_18394390
Related