0

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?

Improve this question
2
  • 1
    Why do you want to split your query? If you want to build a customized query. You might do better working top-down, instead of down-top. Just concatenate with the pre-defined strings. The definition of those strings can then go through an if-else/switch process to check for validity and integrity. Commented Apr 8, 2013 at 8:04
  • No, it can't go that way, as the constructor works like the sprintf() function, so I pass the query and then, all involved parameters, which are taken with the func_get_args() function. The idea is not to have any fixed/assumed condition, so I must process the query after it has been given, not before Commented Apr 8, 2013 at 8:08

2 Answers 2

Reset to default
2

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!

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

1 Comment

Hmmm that gave me an idea about using the preg_replace_callback() function. Thanks for the hint! :)
0

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);
    }
}
Improve this answer

6 Comments

Still... I feel like there could be a better alternative using regexes... any suggestion?
Yeah, do you reckon you can make it clearer what the regex needs to do? e.g. do all the ? symbols need to be expanded out to the number of parameters? - will you actually have the number of parameters at hand?
I've added an answer with a callback and a global counter so you can do something different (replace) on each time the question-mark is matched.
I saw it, and it looks promising. Not copying the same code as passing [1,2,3,4,5,...,n] isn't what I look for. Given the number of parameters, I need to scan each question mark and check if its corresponding parameter is simple or multiple. If it's multiple, I need to re-adjust the question mark to put as many question marks as parameters are in the array being passed as parameter. If it's simple, no substitution is made. I'll slide an if () inside the callback function and see how it goes. Like I said, thanks a lot :)
@d'alar'cop I always have the parameters at hand, as my constructor works (like I said) like the sprintf() function and, hence, I will always have both the query string and the parameters.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.