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!
