Regex to parse a MySQL query for JOIN's

Question

I am using regex to parse a MySQL query for JOIN's, currently using this:

if (preg_match_all('/\s(LEFT|INNER|OUTER|RIGHT)?\sJOIN\s(.*)/i', $sql, $matches)) {
    print_r($matches); exit;
}

This doesn't quite work as I want an array of strings like this:

[0] => formats ON articles.article_format_id = formats.format_id
[1] => article_categories ON articles.article_id = article_categories.article_id

My current regex returns this:

Array
(
    [0] => Array
        (
            [0] =>  INNER JOIN formats ON articles.article_format_id = formats.format_id INNER JOIN article_categories ON articles.article_id = article_categories.article_id 
        )

    [1] => Array
        (
            [0] => INNER
        )

    [2] => Array
        (
            [0] => formats ON articles.article_format_id = formats.format_id INNER JOIN article_categories ON articles.article_id = article_categories.article_id 
        )

)

Any ideas?

Answer 1

The greedy .* might take everything to the end. So change the regex to:

/(?:LEFT|INNER|OUTER|RIGHT)?\sJOIN\s((?:(?!(?:LEFT|INNER|OUTER|RIGHT)?\sJOIN).)+)/i

See here: rubular

And you might have to use preg_match_all to find all matches.