Page 1 of 2
Matching placeholders
Posted: Thu Aug 31, 2006 12:53 pm
by bartz
Hello board,
For some time, I've been trying to fix something using a regex, which I just can't get to work.
Imagine some SQL statements (SELECT, INSERT, UPDATE) with placeholders, for instance:
Code: Select all
INSERT INTO table VALUES(:1,:2,:5,:10,:11)
What I am looking for is a regex which I can iterate, to replace those :bla with their appropriate values, something like:
Code: Select all
for($i=1; $i<count($values); $i++)
{
$query = preg_replace("/:$i/", $values[$i], $query);
}
However, all regexs I can come up with, match not only for instance :1, but also :10, which is obviously not desired. Can anybody help me please? Thanks a million.
Posted: Thu Aug 31, 2006 12:55 pm
by Luke
why don't you use easier replacement values like {value1} and {value2} ?
Posted: Thu Aug 31, 2006 12:57 pm
by bartz
That does make sense, but requires me to change too much stuff in an already live application... Only now I ran into sql queries with more than 9 placeholders

.
Posted: Thu Aug 31, 2006 1:56 pm
by feyd
I would suggest using
preg_replace_callback() and using a pattern of possibly
Posted: Thu Aug 31, 2006 2:36 pm
by volka
in addition:
preg_replace_callback ( mixed pattern, callback callback, mixed subject [, int limit [, int &count]] )
callback always mean you can pass an array with an object reference and a method name.
e.g. preg_replace_callback($pattern, array($obj, 'item'), $subject);
Posted: Fri Sep 01, 2006 5:28 am
by bartz
The callback looks promising, but I'll still not entirely sure about the regex. The one specified above will match any number of digits, whereas I want to match a specific digit, such as :1 and not :10. Or maybe I'm mistaken?
Posted: Fri Sep 01, 2006 5:46 am
by volka
why do you want :10 not to be a match?
Posted: Fri Sep 01, 2006 5:49 am
by bartz
Because :10 will resolve to a different value to replace than :1...
Posted: Fri Sep 01, 2006 6:00 am
by volka
and?
INSERT INTO table VALUES(:1,:2,:5,:10,:11)
I would expect :1 to be replaced by the first and :10 by the 10th parameter, no?
Posted: Fri Sep 01, 2006 6:06 am
by bartz
That's true

, however, imagine this.
I have a class to abstract DB operations, which uses a prepare and execute method.
Something along the lines of:
Code: Select all
$qry = 'INSERT INTO table values(:1, :2, :3)';
$statement = $dbh->prepare($qry);
$result = $statement->execute(1, $username, $password);
The replacing part takes place in the execute() method. It escapes sql values, and replaces them in the query. So I need a regex that matches only :1 when needed (not :11 and so on), only :2 (not :20 and so on) when needed and so on.
Posted: Fri Sep 01, 2006 6:11 am
by volka
When and how do you know :20 is not needed? Why not possibly match it anyway? As the Doctor says: Doesn't kill, doesn't wound, doesn't maim.

Posted: Fri Sep 01, 2006 6:20 am
by bartz
Sorry if this may sound pedantic, but I think the answer to your question goes without saying. I'll write it out anyways.
Imagine this:
Code: Select all
$qry = 'INSERT INTO users VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)';
$res = $dbh->prepare($qry)->execute($id, $username, $pw, etc);
When my matching loop kicks in, it'll search for :1 first, and replace it with $id. It matches :1, which is good, but then it will also match :1 with a 0 after it, so it'll match the :1 of :10 and replace that with $id as well. So now the last part reads:
which is obviously undesired, because :10 should be replaced with the 10th argument to execute().
Once again, sorry if this may sounds pedantic. I'm just looking for a way to match only :1, then only :2 and so on.
Posted: Fri Sep 01, 2006 6:20 am
by Ollie Saunders
Posted: Fri Sep 01, 2006 6:23 am
by bartz
Yes, I know that, but I'm building for a generalised DB abstraction, to easily swap out databases, using an Abstract Factory, and derived classes for each database flavour. So, I definately want to do my handling in PHP.
Posted: Fri Sep 01, 2006 6:24 am
by volka
bartz wrote:Once again, sorry if this may sounds pedantic.
No, it does sound wrong
Try this one
Code: Select all
<?php
$a = array(1=>'x', 2=>'y', 11=>'z');
echo preg_replace('!(\d)+!e', '$a[\\1]', '1|2|11');
?>
(modifier e instead of preg_replace_callback because I'm too lazy to write more code right now)
pcre runs in greedy mode by default, matching as much characters as possible for each symbol. Therefore \d+ matches 11 as one token instead of two times 1.
bartz wrote:Yes, I know that, but I'm building for a generalised DB abstraction, to easily swap out databases, using an Abstract Factory, and derived classes for each database flavour. So, I definately want to do my handling in PHP.
pdo provides all of that.
http://www.php.net/pdo