[macgyvr64]

I'm working on a web based project where I'd like to provide the user with the ability to build their own search queries using a set of filters like the ones seen in Mail.app's "Rules" pane or Acquisition's "Filter Results" option. I'm using MySQL, PHP, and JavaScript to handle it all, but I have a few troubles I was hoping to get some ideas/pointers with.

Let me point out that the list of things available for filtering is stored in MySQL such that, to create a popupmenu of choices, a query must be run to retrieve the list (for the sake of discussion, think along the lines of Pet Type, Name, and Fur Color). The reason for this is that the list of data to be stored can have fields added or removed, and thus the changes need to be reflected in the search popumenu.

Something like this interface is the one I had in mind:


I'm wondering:

• How can the popupmenu be built upon (or during -- PHP is being used) page load taking the above into account?
• Once that single "search row" is built, how can more be created in the DOM with the (+) and (-) buttons without incurring an XMLHttpRequest delay? Can a single "ideal" or default row be built, stored in a JavaScript memory container of some sort, and finally duplicated upon request?
• After the whole set of rules is built by the user, how can I go about turning that into a decent SQL query?
• Are there any security considerations (SQL injection)? While this will be used only by people I know, it can't hurt to think about possible flaws.

The whole thing is subject to change, so if something fundamentally different needs to be done, it may well be possible. I'm not looking for a complete solution from anyone; I'm simply searching for a jumping-off point. Any suggestions would be greatly appreciated.

[Millennium]

Don't generate raw SQL on the client side. It makes you vulnerable to a type of attack known as SQL injection, which allows an attacker to gain more or less complete control of your database, just by passing different SQL strings in.

What you should do instead is build up some other representation of the search on the client side, pass that to your PHP-based backend, and let the backend translate it into SQL. For example, you could dump whatever object you use to store the query on the client side directly out into JSON, and then use a PHP-based JSON parser to translate that into the SQL you need for the search.