Page 1 of 1

preg_match difficulty

Posted: Tue Sep 02, 2003 2:18 pm
by ChibiGuy
THanks for any advice.

I'm trying to use preg_match to match a particular SQL statement. The sql statement has to be in the following format:

1: The words SELECT followed by some text
2: The words FROM followed by some text following
3: (OPTIONAL) WHERE with some text following
4: (OPTIONAL) LIMIT with some text following
5: (OPTIONAL) ORDER with some text following

When I run the following statement

Code: Select all

$query = "SELECT (*) FROM ('school') WHERE id=0 LIMIT 0,10 ORDER id ASC";
preg_match('/SELECT (.+) FROM (.+)( WHERE (.+))?( LIMIT (.+))?( ORDER (.+))?/', $query, $m);
The results would be:

Code: Select all

Array
(
    [0] => SELECT (*) FROM ('school') WHERE id=0 LIMIT 0,10 ORDER id ASC
    [1] => (*)
    [2] => ('school') WHERE id=0 LIMIT 0,10 ORDER id ASC
)
What I want it to do is seperate the WHERE, LIMIT, and ORDER parts so that I can evaluate them later on in the code.
What seems to be the problem here? Am I doing anything wrong?
Thanks

Posted: Tue Sep 02, 2003 4:00 pm
by volka
try

Code: Select all

preg_match('/SELECT (.+) FROM (.+)( WHERE (.+))?( LIMIT (.+))?( ORDER (.+))?$/U', $query, $m);
you have to make sure FROM's (.+) doesn't take all remaining characters (otption U, ungreedy) but the pattern still reaches the end of the line ($).

Posted: Tue Sep 02, 2003 4:01 pm
by Stoker

Code: Select all

/SELECT\\s(.+?)\\sFROM\\s(.+?)(\\sWHERE\\s(.+?))?(\\sLIMIT\\s(.+?))?(\\sORDER\\sBY\\s(.+?))?/
Hopefully the forum will represent all that right.. :wink:

when using preg_match the result array would be something like
0 - The whole matched string
1 - The selected columns
2 - The from tables
3 - The whole WHERE clause
4 - The where conditions
5 - the whole LIMIT clause
6 - the limit parameters
7 - the whole ODER BY clause
8 - the order by parameters

What are you using this for? its unusual to "compile" sql statements like that, at least with a PHP script/app I would say..

Edit/Add: I didnt see Volkas before posting, they are fairly similar but I used the ? to make the quantifiers non-greedy, as well as I allowed for any whitespacechar inbetween, actually there should be a +? after all \\s to allow any whitespace..

Posted: Tue Sep 02, 2003 5:37 pm
by ChibiGuy
THANKS SO MUCH.
I've been on this for so long, I can't remember. I looked up preg_match in the php manual, but never found the 'U' modifier.

Posted: Tue Sep 02, 2003 6:08 pm
by volka