preg_match difficulty

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ChibiGuy
Forum Newbie
Posts: 22
Joined: Fri Jul 11, 2003 11:23 pm
Location: USA
Contact:

preg_match difficulty

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ($).
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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..
ChibiGuy
Forum Newbie
Posts: 22
Joined: Fri Jul 11, 2003 11:23 pm
Location: USA
Contact:

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Post Reply