I remember answering a question that was similar to this one, in that it also needed a stored procedure.
this code could use several stored procedures. I don't know how sprocs work in MySQL but I assume that you can still code them.
it looks to me like you are trying to write a lot of SQL Code in the PHP, you can do this but it is a waste of the PHP Server Resources, let the Database do the work it is supposed to be doing.
you should be able to do the Case Statement in SQL as well. all of this should be SQL Code not SQL inside of PHP Logic.