Extract WHERE components from MySQL string dynamically

Say I have the following string (forget that it is a MySQL statement):

SELECT * FROM users WHERE name = 'fred bloggs' AND age=21 AND address='Mountain View, CA 94043' LIMIT 1

I need a way to extract the field name and value in the WHERE clause so I have an array like:

Array
(
    [name] => fred bloggs
    [age] => 2开发者_StackOverflow社区1
    [address] => Mountain View, CA 94043
)

Remember this is a dynamic MySQL string so I can't hard-code name, age or address.

The problems I can foresee are:

• Finding the field names, the function would have to know all valid operators so to match each field name (see array to use below)
• Spaces are not guaranteed (e.g. age=21)
• Finding the values within ' and ' but not breaking when a ' is within the string
• Finding values not within ' and ' e.g. numbers or other field names (would need to be treated seperately)

Does anyone have any ideas how to do this either with regex or string functions?

Here are the operators if required:

$operators = array('+', '-', '*', '/', '^', '=', '<>', '!=', '<', '<=', '>', '>=', 'like', 'clike', 'slike', 'not', 'is', 'in', 'between', 'and', 'or');

---

You can try with this kind of tool : http://code.google.com/p/php-sql-parser/

You will get a tree (an array) describing the query, this will help you to build the array you want.

---

You should create an expresssion parser for that, it cannot be achieved with regex . E.g., your resulting array would contain an expression tree with all node types you specified (OR, AND, IN, BETWEEN, etc.) There's an example on the web: here

---

There are probably a few ways to approach this:

1) Look for metadata from the query. I know you can get info about the resultset but Im not sure about the original query. Its in there somewhere but might need to be teased out.

2) Use a prepared statement to build your query from pieces. Pass in the various where conditions in an array. Its kind of backwards (IE starting with the answer you want and then building the query) though.