CODEWITHSUNDEEP
phpsqlregex
Benjamin
Benjamin

Reputation: 439

PHP Regex fetch groups SQL like queries

I am trying to create a system for our clients to where instead of having them learn how to work with MySQL or SQL in that term. I am creating a basic command like system that allows dynamic flexibility. So this is what I have but I am not very successful right now.

Here is an example of a full scale search function our clients can use to generate statistics.

FIND ALL USERS, ASSIGNMENTS, SECTIONS WHERE ASSIGNMENT IS Assignment 1 AND SECTION IS Section 1 AND ASSIGNMENT IS FINISHED AND ASSIGNMENT IS COMPLETED BEFORE ON ‘02-01-1984’ LIMIT 100

The regex I have so far is something like this.

^find\s+(all\s+)?(.+)(where\s+(.+))(limit\s+([0-9]+))

Matched/Group that I am getting right now

Match 1:    FIND ALL USERS, ASSIGNMENTS, SECTIONS WHERE ASSIGNMENT IS Assignment 1 AND SECTION IS Section 1 AND ASSIGNMENT IS FINISHED AND ASSIGNMENT IS COMPLETED BEFORE ON ‘02-01-1984’ LIMIT 100      0     183
Group 1:    ALL          5       4
Group 2:    USERS, ASSIGNMENTS, SECTIONS         9      29
Group 3:    WHERE ASSIGNMENT IS Assignment 1 AND SECTION IS Section 1 AND ASSIGNMENT IS FINISHED AND ASSIGNMENT IS COMPLETED BEFORE ON ‘02-01-1984’         38     136
Group 4:    ASSIGNMENT IS Assignment 1 AND SECTION IS Section 1 AND ASSIGNMENT IS FINISHED AND ASSIGNMENT IS COMPLETED BEFORE ON ‘02-01-1984’       44     130
Group 5:    LIMIT 100      174       9
Group 6:    100    180       3

Now If I do something like 

FIND ALL USERS, ASSIGNMENTS, SECTIONS

It will fail, and not give me anything, I would like to have it succeed upon where clause (where it is optional) just as the limit is also optional

The first group should be ALL to determine whether to grab all information LIMIT becomes ignored when "ALL",

The second group should be the tables that I have predefined to match up. The first one is always the primary table.

The third group I will be ignoring

The forth group I will be using to determine the "Where Clause", and I can figure the regex out for this part

The fifth group is ignored too

The sixth group is the limit amount

So I can do something like

FIND ALL USERS, ASSIGNMENTS, SECTIONS LIMIT 100

OR

FIND ALL USERS, ASSIGNMENTS, SECTIONS WHERE ASSIGNMENT IS abc

All these should succeed.

Any help would be much appreciated.

Thanks!

Upvotes: 0

Views: 74

Answers (1)

Barmar
Barmar

Reputation: 781096

Make the third and fifth groups optional. And use non-greedy quantifiers in the 2nd and 4th groups, otherwise they'll eat the optional clauses after them.

^find\s+(all\s+)?(.+?)(?:\s+where\s+(.+?))?(?:\s+limit\s+([0-9]+))?$

For the groups you're ignoring, make them non-capturing groups.

DEMO

Upvotes: 1

Related Questions