CODEWITHSUNDEEP
phpregexselectparsing
ant
ant

Reputation: 1

split select statement with regular expression

i need to split any mysql select statement in its main parts: SELECT, FROM, ALL THE JOINS(if there are any), WHERE(if it exists),GROUP BY(if it exists), HAVING(if it exists), ORDER BY(if it exists), LIMIT(if it exists)...

i tried using regular expressions, but i'm not very good with them... for the SELECT the regex was simple (any SELECT that comes at the begining of the string-^SELECT), but after that i stumbled... all i know it's that the 'FROM' must not be contained in () - which will mean it's from a subselect... and i think that all the other parts of the select (JOINS, WHERE, GROUP BY, etc) will match this rule... but i don't know how to write such a rule... and i hope someone can help me...

for the select statement:

SELECT SQL_CALC_FOUND_ROWS (SUM(TIME_TO_SEC(audioMelodii.durata))/60) AS durataTotal, (SELECT COUNT(audioMelodii.id) FROM audioMelodii) AS nrMelodii, audioArtisti.nume AS artist, audioAlbume.*
FROM audioAlbume
LEFT OUTER JOIN audioMelodiiAlbume ON (audioMelodiiAlbume.idAlbum=audioAlbume.id)
LEFT OUTER JOIN audioMelodii ON (audioMelodii.id=audioMelodiiAlbume.idMelodie)
LEFT OUTER JOIN audioArtisti ON (audioAlbume.idArtist=audioArtisti.id)
WHERE audioAlbume.idArtist='$idArtist'
GROUP BY audioAlbume.id
ORDER BY dataLansare DESC, id DESC
LIMIT 0,10

the result i'm looking for would be something like this:

$STATEMENT['SELECT']='SELECT SQL_CALC_FOUND_ROWS (SUM(TIME_TO_SEC(audioMelodii.durata))/60) AS durataTotal, (SELECT COUNT(audioMelodii.id) FROM audioMelodii) AS nrMelodii, audioArtisti.nume AS artist, audioAlbume.*';
$STATEMENT['FROM']='FROM audioAlbume';
$STATEMENT['JOINS']='LEFT OUTER JOIN audioMelodiiAlbume ON (audioMelodiiAlbume.idAlbum=audioAlbume.id)
LEFT OUTER JOIN audioMelodii ON (audioMelodii.id=audioMelodiiAlbume.idMelodie)
LEFT OUTER JOIN audioArtisti ON (audioAlbume.idArtist=audioArtisti.id)';
$STATEMENT['WHERE']="WHERE audioAlbume.idArtist='$idArtist'";

etc...

thank you very much! all the best!

Upvotes: 0

Views: 1652

Answers (2)

Jim Garrison
Jim Garrison

Reputation: 86774

You can't do this easily with regular expressions due to the recursive complexity of the SQL (sub-queries, sub-sub-queries, which can occur in several places.

Maybe if you explain your ultimate objective we could help you find a better solution.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562378

SQL is not a language that can be parsed with regular expressions.
You really need to use a full-blown SQL parser for this task.

See also:

Upvotes: 2

Related Questions