Querying a MySQL database step by step - Best method?

Question

Hi,

I am building a web application that uses PHP 5, MySQL 5 and Javascript with jQuery and AJAX.

The application is a quite complicated selection process with several pulldown menus that the user can select from in any order. Each select in a pulldown will give some more selection critera, that will limit the choices in the other pulldowns and finally give a limited result set.

I am using Javascript on the client side with AJAX connecting to PHP on the server and the MySQL database, the idea is that each time the user selects something from a pulldown I send a AJAX request to update all the other pulldowns. When all possible parameters have been set a result set will be presented.

(I do want to keep the logic on the server side for various reasons, therefor using AJAX.)

Now, I am not sure what is the best way to handle this "iterative" process for each user:

I have a table with all the criteria that each selection result in. Since each pulldown selection will result in a set if selection criteria I could store a query string on the server that is being added to for each selection. I then run the currently stored query string to find out what should go into each pulldown, send that data to the client, then store the query for later when more criteria will be added, run, send etc, etc... Then I have to store that query string on the server for each user, perhaps in a text file. Or perhaps as a stored procedure, see below....

Another approach would be to just run the query with the new criteria when a selection is done, send the data to update the pulldowns, then store the selection in a temporary table that I run the next query on etc etc. So the temporary table will then shrink for each selection until I present the result.

The app can have several users working at the same time, so must probably identify the users by cookie/session ID somehow....(?)

And the stored query or temp table must die away after some time when each user leaves....how?

Selection methods to add more and more criteria to select from a table, allowing for complicated calculations for selection:

• Using join

• Using a temporary table that is being more and more limited as records are removed when new criteria are added.

• Using subselections.

Storage methods to keep on track between each step for each user:

• Store parameters in a server file, and insert them into sql or stored procedure for each selection step, doing a new query on the whole database for each step, but each time with more criteria added.

• Store a sql query in a file(s), and extend it for each step....

• Store a sql query in a table/field.....

• Store a sql query in a stored procedure .....

• Store the actual limited result set of previous selections for each user in a temopary table that will be smaller and smaller as more and more records are removed for each step.

• Store the parameters on the client side during the session, resending them all to the server for each selection

Could anyone help with this decision?

Rgds PM

Answer 1

The best approach that I would recommend would be to run a different query each time a value in the drop down box is changed by a user. If you have 5 drop down boxes, when the user changes the value in the first box you run a query from your AJAX page:

SELECT * FROM table WHERE val1 = x;

Then once the user decides to change another value, you can run a more narrowed query, e.g.

SELECT * FROM table WHERE val1 = x AND val2 = y;

Then you can repeat this process until all drop down boxes have been changed.

As for using a temporary table or stored procedure. I would say this probably isn't necessary for what you are trying to achieve and is likely just to add unneeded complexity to the project.

If you know which fields need to be checked from the front-end, then you should just send the selected values from the drop down boxes to the AJAX page so that it can decide what query needs to be run and return the results (XML/JSON would be great for this).