How to extract the string below using regex (preg_match or preg_replace):
SELECT id FROM table WHERE 1 would match " id "
SELECT name, (SELECT price FROM table2 WHERE id = '1') var FROM table1 WHERE 1 would match " name, (SELECT price FROM table2 WHERE id = '1') var "
If your goal is to do some advanced things with your queries, the best way is to use a sql parser ("php sql parser" for example, as suggested). To only get the content between SELECT and FROM, you can use this:
$test_queries = array(
'SELECT id FROM table WHERE 1',
'SELECT name, (SELECT price, (SELECT 1+1) FROM table2) var FROM table1',
'SELeCT FROMAGE, \'\\\'((\', \'"\")(\', "\'\"\\\" FrOM table');
$pattern = <<<'LOD'
~
# definitions
(?(DEFINE)
# content inside quotes
(?<quoted> (["']) (?> [^"'\\]++ | \\{2} | \\. | (?!\g{-1})["'] )*+ \g{-1} )
# nested parenthesis
(?<nested> ( \( (?> [^()"']++ | \g<quoted> | (?-1) )*+ \) ) )
# content between SELECT and FROM
(?<content> (?> [^(F"']++ | \BF | F(?!ROM\b) | \g<quoted> | \g<nested> )++ )
)
# pattern
\bSELECT\b (?<result> \g<content> ) FROM\b
~xis
LOD;
foreach ($test_queries as $k => $test_query) {
if (preg_match($pattern, $test_query, $match))
echo '<br/>' . $k . ') ' . trim($match['result']);
}
