Regular expression replace

id IN (2,400) with id IN (4, 3)

[mcibor]

Hi all!

I encountered a small problem, to which I know 2 solutions, however one requires a regex in which I'm completely lame.

I have a string of numbers separated by comma in variable $in and would like to replace the IN part of the query

$sql = "SELECT * FROM tbl WHERE name='name' AND id IN (2,400,23,4,1203) ORDER BY id";

$in = "3,4,2,300";

How to do that with ereg_replace? or other regex function (which?)?

That will suffice, however if you have nothing to do, I've got a better problem!
Sometimes the string can be without the IN part, and then I want to put it there. Till now I've been doing it by
$sql = str_replace("ORDER BY", "AND id IN ($in) ORDER BY", $sql);
Thanks for any answers!

Best regards
Michal Cibor

PS. The structure will be always the same - AND id IN (numbers+commas) ORDER BY or just ORDER BY

PS2. The second solution requires a bunch of strpos and substr so I would prefer to forget it!

[ergophobe]

I'm not sure I understand why it's not just

$in = "3,4,2,300";
$sql = "SELECT * FROM tbl WHERE name='name' AND id IN ($in) ORDER BY id";

so I'm not sure this is the right answer, but here goes.

$in = "3,4,2,300";
$sql = "SELECT * FROM tbl WHERE name='name' AND id IN (2,400,23,4,1203) ORDER BY id";

$pattern = '/(SELECT.*FROM.*WHERE.*AND id IN \()[\d\s,]+(\).*ORDER BY.*)/U';
$replace = '$1' . $in . '$2';
$new_sql = preg_replace($pattern, $replace, $sql);

Haven't tested, but give it a try.

[arran]

Hi mcibor,

Here's my less than graceful solution until someone comes up with the regexp:

list ($start, $dummy, $end) = split ('[()]', $sql);
$sql_cooked = $start."(".$in.")".$end;

Cheers,
arran.

<added>Too late!</added>

[mcibor]

Why not IN($ins)?

I use iframe to show table (it's very long and needs right-left scrolling, no up-down). So to pass query to the iframe I use a db, where I store the last used query (without the limit). That's the main problem.

Also the problem would still be there if I used another form, as I take usually only some of the ids :

from IN (2,3,5,7,8,9,10,14,165,188)

the user can choose eg:

IN (2,3,5,7,8,9,10,188) - leave out the 14 and 165.
The checking of ids is a much more complicated way (I use a javascript to do it on user side), so I get in the cookie plain 2,3,5,7,8,9,10,188.

Thanks Ergo and Arran!
Michal Cibor

PS. No way to include the missing IN? :)

[ergophobe]

PS. No way to include the missing IN? :)

Hmmm. I thought that's what I was doing. I wonder if you can give two literal examples of input and what the expected output would be. I think I'm still just not understanding the question.

[mcibor]

Ergo! What you answered is the bigger half of my problem! And thank you for that!

However what I need is also to put the ids when they're not there:

So to summarize I need to change:

SELECT bla,blal FROM blala WHERE bla='bla' AND blabla='blabla' ORDER BY name
into
SELECT bla,blal FROM blala WHERE bla='bla' AND blabla='blabla' AND id IN (2,3,5) ORDER BY name

OR

SELECT bla,blal FROM blala WHERE bla='bla' AND blabla='blabla' AND id IN (1,2,3,5,7,11,13,200,3987) ORDER BY name
into
SELECT bla,blal FROM blala WHERE bla='bla' AND blabla='blabla' AND id IN (2,3,5) ORDER BY name

The regex you provided does only the second part. I am wondering if there's a regex that would do it all :)

Best regards
Michal Cibor

PS. It would be the same hard for me to store the query as SELECT $select FROM $from...
Especially that I need that info, not just the mainframe of the query

[ergophobe]

It's always like learning what they used to call "story problems" in grade school - the hard part is figuring out what you're supposed to do. So the trick here is to capture the "AND IN (12, 123, 123)" and always throw it away. So you need to capture it conditionally

Anyway, the fact that the AND IN () always comes immediately before the ORDER BY simplifies things greatly. The changed parts are bolded.

$in = "AND IN (3,4,2,300)";

$sql1 = "SELECT * FROM tbl WHERE name='name' AND id IN (2,400,23,4,1203) ORDER BY id";
$sql2 = "SELECT * FROM tbl WHERE name='name' ORDER BY id";

$pattern = '/(SELECT.+?)(\s+AND id IN \([^\)]+\))?([\s]+ORDER BY.*?)$/';
$replace = '$1 ' . $in . '$3';
$new_sql = preg_replace($pattern, $replace, $sql);

Notes:
- the first replacement group has a trailing space since we throw away any whitespace preceding the AND id IN so it's '$1_' not '$1'.
- we use individual "lazy" operators rather than the /U flag because we want some of these to be greedy (namely the ones that capture whitespace before AND id IN and ORDER BY).
- This works for SQL batch queries and it should work for a single query in a string too as long the query ends at a EOL.
- I've tested the regex, but not the PHP, and it matches and replaces as desired on a file consisting of

SELECT * FROM tbl WHERE name='name' AND id IN (2,400,23,4,1203) ORDER BY id;
SELECT * FROM tbl WHERE name='name' ORDER BY id;

[edited by: ergophobe at 8:53 pm (utc) on Oct. 4, 2005]

[jd01]

I think you were looking for this replacement pattern:

$replace = '$1 AND id IN (2,3,5) $3';

Justin

Added: Don't have time to test it, but the second replace would be something like this:

$pattern = '/(SELECT.+?)[\s]+(ORDER BY.*?)$/';
$replace = '$1 AND id IN (2,3,5) $2';

$pattern and $replace can be stored as an array() to change both SELECTS with a single preg_replace.

[ergophobe]

Actually, you don't need an array. The pattern I gave above will work for both because if it finds the ADD id IN(...) it just throws it away because it doesn't need it. Since it is conditional, it matches in either case. Despite being conditional, the () means that it captures a group (though sometimes empty), at least with the regex engine I'm testing with (like I said, I'm using a grep tool to test the regex, not PHP's PCRE engine).

Also, I used the $in variable in the pattern because I was assuming that the new numbers will be coming from a variable somewhere, but of course you could plug them straight into the replacement group.

[mcibor]

Thanks both of you!
Ergo you're great! It works like a charm!

See you round
Michal Cibor

[jd01]

Missed the conditional part -- I hate it when I do that =)

Justin