Match pattern, but not returning all of it

Question

I'm pretty new to regex. Say that I have a string like so:

UPDATE table_name SET column = :column WHERE column IN(:column_32, :column_34, :column_347)

And then assume that I have an array:

$arr = array(
    'column' => 'Test',
    'column_32' => 'Tester',
    'column_34' => 'Faux data',
    'column_347' => 'Column data'
);

What I want to do is replace the placeholders (:column, :column_32 etc) with their corresponding values in the array. Problem is, str_replace wont cut it as replacing :column with 'Test' will result in :column_32 becoming :Test_32 etc.

So, I've started writing some custom regex. Here's a test script I've been running:

foreach($arr as $k => $v){
    var_dump(preg_match_all('(:'.$v.'[^_])', $str, $matches));
}

It matches the correct strings. Problem is, it also returns "," and ")" as a part of the match. Am I overlooking something here?

This is exactly what `bindParam()` does in PHP's `PDO`. [See here; especially Example #1.](http://www.php.net/manual/en/pdostatement.bindparam.php) It's just odd because the `:xyz` syntax hints that your code used such a `bind` implementation to begin with, and now you seem to want to hack it via regex... — Andrew Cheong, Nov 26 '12 at 15:54
Are you trying to reinvent the wheel (aka. PDO prepared statements)? — inhan, Nov 26 '12 at 15:55
@acheong87 Writing a PDO wrapper class that automatically creates, prepares and executes prepared statements using an associative array. I'm replacing the placeholders with their corresponding values for debug purposes. — WtotheX, Nov 26 '12 at 15:57
Have you looked at the PDO `debugDumpParams` method? http://www.php.net/manual/en/pdostatement.debugdumpparams.php See also this SO question: http://stackoverflow.com/questions/2411182/how-to-debug-pdo-database-queries — Andy Lester, Nov 26 '12 at 17:06

Martin Ender · Answer 1 · 2012-11-26T21:16:01.057

This may be a bit of a hack, but how about you order your array by key length from longest to shortest. Then any key that can be a substring of another key will be checked after its containing string. So you reorder your array like this:

$arr = array(
    'column_347' => 'Column data'
    'column_32' => 'Tester',
    'column_34' => 'Faux data',
    'column' => 'Test',
);

You can, of course, also do this automatically. As of PHP 5.3.0 like this:

uksort($arr, function($a, $b) {
    if(strlen($a) > strlen($b))
        return -1;
    else
        return 0;
});

Below 5.3.0 you could either use this (as of PHP 4.0.1):

uksort($arr, create_function('$a, $b', '
    if(strlen($a) > strlen($b))
        return -1;
    else
        return 0;
'));

Or (which is maybe preferable, if you cannot use the nice anonymous function syntax, or if you use PHP before 4.0.1):

function compare($a, $b) {
    if(strlen($a) > strlen($b))
        return -1;
    else
        return 0;
}

uksort($arr, "compare");

I like this solution, simply because it gets rid of the need for regex. — WtotheX, Nov 26 '12 at 16:05

score 2 · Accepted Answer · answered Nov 26 '12 at 15:51

Basically you are avoiding all the non _ chars after your keyword. [^_] after your variable means "find a single char that is not an underscore", thus your regex correctly grabs the comma and the closing parenthesys.

Why don't you just try to find the identifiers? Searching for :column_347 for example would be fine. You may want to search for a word boundary after your placeholder to tell your regex "don't stop if this (eg column) is not a whole word.

This is obtained replacing the [^_] with \b which is a word boundary. This link shows how this works, note that the regex matches only the first "column"

score 2 · Answer 3 · answered Nov 26 '12 at 16:00

This doesn't answer your regex question, but I wanted to offer the PDO implementation anyway:

<?php

    $sql = 'UPDATE table_name SET column = :column WHERE column IN(:column_32, :column_34, :column_347)'

    $arr = array(
        'column' => 'Test',
        'column_32' => 'Tester',
        'column_34' => 'Faux data',
        'column_347' => 'Column data'
    );

    $sth = $dbh->prepare($sql);

    foreach ($arr as $k => $v) {
        $sth->bindParam(':' . $k, $v);
    }

    $sth->execute();

?>

If you are working with someone else's code and weren't aware of PDO, then this is probably the right way to go, for many reasons (including protection against injection attacks). However, if you're aware of PDO and desire an alternate, regex-based implementation anyway, then @m.buettner and @Gabber both provide sound advice.

@WtotheX - Sorry, I did not see your comment while posting this answer. So you're not only aware of `PDO` but actually _enhancing_ it; cool. I'll leave this answer up in case there _are_ others who are reinventing the wheel and might stumble upon this question. — Andrew Cheong, Nov 26 '12 at 16:07

Match pattern, but not returning all of it

3 Answers3