Replace repeating character with array elements in PHP

Question

I hope this is blindingly obvious: I'm looking for the fastest way to replace a repeating element in a string with the elements in a given array, e.g. for SQL queries and parameter replacement.

$query  = "SELECT * FROM a WHERE b = ? AND c = ?";
$params = array('bee', 'see');

Here I would like to replace the instances of ? with the corresponding ordered array elements, as so:

SELECT * FROM a WHERE b = 'bee' and c = 'see'

I see that this might be done using preg_replace_callback, but is this the fastest way or am I missing something obvious?

Edit: I am already using prepared statements which achieves the above. I am looking for a way to get the raw query back to show as part of debug output.

Answer 1

Use PDO to do your SQL queries. It does parametrized queries, so you don't need to roll your own method.

For a more generic method, see sprintf (which won't escape data to make it safe, so don't use it for database access).

Answer 2

Are you looking for prepared statements?

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

Answer 3

I would also recommend using PDO as @David Dorward already suggests. Then:

$stmt = $dbh->prepare('SELECT * FROM a WHERE b = ? AND c = ?');
$stmt->execute(array('bee', 'see'));

Answer 4

I once found the following in an inherited codebase, which I thought was a nifty way of doing these kind of substitutions:

function do_substitution($query, $params) {
    $params = array_unshift($params, $query);
    $query = call_user_func_array('sprintf', $params);

    return $query;
}

Ofcourse, you are substituting %s, %d, etc. marks this way.