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.

Dominic Rodger · Accepted Answer · 2010-05-17 13:48:02Z

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);
  }
}
?>

answered May 17, 2010 at 13:48

Dominic Rodger

100k37 gold badges204 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Will Croft Over a year ago

David & Dominic: thanks, 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.

nuqqsa · Accepted Answer · 2010-05-17 13:51:41Z

1

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'));

answered May 17, 2010 at 13:51

nuqqsa

4,5211 gold badge28 silver badges30 bronze badges

Comments

NullUserException · Accepted Answer · 2011-11-13 17:09:27Z

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).

edited Nov 13, 2011 at 17:09

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

answered May 17, 2010 at 13:47

Quentin

949k136 gold badges1.3k silver badges1.4k bronze badges

2 Comments

Felix Kling Over a year ago

Of course you can use sprintf for database stuff, but mysql_real_escape_string should be used to escape the parameters.

Quentin Over a year ago

I said "don't" not "you can't". This would be pointless wheel reinvention, there are good parametrized query systems in PHP already.

Marijn van Vliet · Accepted Answer · 2010-05-17 14:23:45Z

0

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.

answered May 17, 2010 at 14:23

Marijn van Vliet

5,4092 gold badges36 silver badges46 bronze badges

1 Comment

Will Croft Over a year ago

Thanks Rodin. Looks like the fastest way is a straight loop and strpos - there are rarely more than 3-5 parameters in a query.

Brad · Accepted Answer · 2013-09-12 02:57:27Z

0

Found this years after it was asked. For anyone else looking and not satisfied with the other answers. This is how I did ended up doing it:

echo preg_replace(array_fill(0,count($params),'/\?/'),$params,$query,1);

Note: This is purely for debugging and does no data cleaning.

This could probably be expanded, if you knew the intended datatype for each parameter, to add in any quotes around values. These are automatically created when using something like sqlsrv_query($conn,$query,$params);

answered Sep 12, 2013 at 2:57

Brad

1

Collectives™ on Stack Overflow

Replace repeating character with array elements in PHP

5 Answers 5

1 Comment

Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related