Search code examples
phpmysqlstring-parsing

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] => 21
    [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');
Solution

  • 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.