What I need is to get all the assigned values in a SQL queries. I am only interested in strings and numbers. For example:
SELECT * FROM my_table WHERE ((name="alex" AND age >= 24) OR gender =1)
AND date = CURDATE() ORDER BY active = 1 LIMIT 1
By using PHP's preg_match I would like to get an array containing the following values:
$values = array(
'alex'// name ,
24 //age,
1 //gender,
1 //active
);
I am a beginner with regular expressions and all I have is this:
preg_match_all('/\=\s*?(.*)\s*/', $sqlquery, $matches);
which will return a single match, the one after the first operator found
Something like
/[a-zA-Z0-9]+\s*[><!]?=\s*(["a-zA-Z0-9]+)(?=\)|\s|$)/
[a-zA-Z0-9]+ Matches the left side of the equals to
\s* Matches zero or more spaces.
[><!]? Character class, matches > or < or !
? Quantifier, ensures that the preceding character class occures zero or one time.= Matches the space.
(["a-zA-Z0-9]+) Matches the right hand side of equals.
(?=\)|\s|$) Positive look ahead. Checks if the right hand side of = is followed by \s or ) or end of string $
Test
$string = 'SELECT * FROM my_table WHERE ((name="alex" AND age >= 24) OR gender =1)
AND date = CURDATE() ORDER BY active = 1 LIMIT 1';
preg_match_all('/[a-zA-Z0-9]+\s*[><!]?=\s*(["a-zA-Z0-9]+)(?=\)|\s|$)/', $string, $matches);
print_r($matches[1]);
// Outputs
// Array ( [0] => "alex"
// [1] => 24
// [2] => 1
// [3] => 1 )
Try something like
[=>!<]\s?([\w"'\d\s]+(?:\(\))?)
|____| | |________| |_____|
Assign| op | |
optional s|pace Optional function call
Any word, number, ", ' and space
But why do you want that? Maybe you can make that from PHP side without parsing SQL?