2

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;

}
deanstreet
  • 165
  • 1
  • 8
  • 4
    Just pass `$param` to the `execute` no need for that `bindparam`. – user3783243 Jul 21 '21 at 12:58
  • 2
    `foreach ($param as $key => $value) $stmt->bindParam($key, $value);`… Then pass `$param` as either numerically index array or associative array… – deceze Jul 21 '21 at 13:06
  • 3
    IMO, there's no need to downvote this post. OP has shown clear research, formatted their question well and is simply stuck on an implementation detail. – waterloomatt Jul 21 '21 at 13:18

1 Answers1

4

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.

Dharman
  • 26,923
  • 21
  • 73
  • 125
  • you're right. I could have asked the caller to supply ['name1' => value1, 'name2' => value2, ...] instead of [value1, value2, ...] when the supplied query uses named params. I have accordingly updated my function inside the question. – deanstreet Jul 22 '21 at 08:45
  • @deanstreet Why do you still have so much code? Look at what I wrote. It does exactly the same thing with only 3 lines. You've only added more unnecessary code. There's no need to strip `:` – Dharman Jul 22 '21 at 11:22
  • I see your code works and much more terse, but I prefer verbosity to increase clarity (for myself) and I find bindParam() more clear (again for me). – deanstreet Jul 22 '21 at 12:41