PHP - regex/function to replace the nth ocurrence of a character within a string

Question

I have a SQL query string like this:

SELECT * FROM `oc_product` WHERE `manufacturer_id` = ? AND `date_added` < ?  `product_id` IN (?) AND `price` > ? ORDER BY `product_id` ASC;

This is handed over to a Query class constructor like this:

$query = new Query("SELECT * FROM `oc_product` WHERE `manufacturer_id` = ? AND `date_added` < ?  `product_id` IN (?) AND `price` > ? ORDER BY `product_id` ASC;", 27, date("Y-m-d H:i:s"), [17,18,29,30,46,47], 27.75);

I currently have a function which explodes the string with the question mark as the delimiter, count the number of marks and traverse the parameters, re-composing the query string so, for each parameter which is an array, I sustitute its question mark with as many question marks as parameters are in the array (so, instead of being IN (?) it gets corrected like IN (?,?,?,?,?,?)).

However, I don't like how my function looks like and would like to find a better equivalente or, if possible, a regex/function/SPL-driven procedure to make the substitution far easier than I have currently done.

Could you give me a hint?

Answer 1

Try something like this;

$params = array(27, date("Y-m-d H:i:s"), '[17,18,29,30,46,47]', 27.75);
$counter = 0;

$query = "SELECT * FROM `oc_product` WHERE `manufacturer_id` = ? AND `date_added` < ?  `product_id` IN (?) AND `price` > ? ORDER BY `product_id` ASC;";

echo preg_replace_callback('/\?/', function($matches) {
    $GLOBALS['counter']++;

    return $GLOBALS['params'][$GLOBALS['counter']-1];
}, $query);

Output

SELECT * FROM `oc_product` WHERE `manufacturer_id` = 27 AND `date_added` < 2013-04-08 10:29:43 `product_id` IN ([17,18,29,30,46,47]) AND `price` > 27.75 ORDER BY `product_id` ASC;

Not sure about the IN ([17,18,29,30,46,47]) part, but you can modify the string to your liking in the $params array.

Good luck!

Answer 2

Found a solution. Just for the SQL query part but it's pretty fast and looks simple:

// takes ~1.25ms to complete (on average)
function str_replace_nth($haystack, $needle, $occurrence, $replacement) {
    $tmp = explode($needle, $haystack);

    if ($occurrence > sizeof($tmp)) {
        return $haystack;
    } else {
        $tmp[$occurrence - 1] .= "{$replacement}{$tmp[$occurrence]}";
        unset($tmp[$occurrence]);
        return implode($needle, $tmp);
    }
}