Hi All, I need some advice regarding the best way to run more specific filtering upon the recent query result list ?
Let say I have run query for listing specific product list and displayed them on the web (using php), and then I want to allow the users to make more specific filtering based on price range (lowest price to highest price).
The more specific filtering supposed to be performed upon the query results on the server side (not client side)
I still have no idea if there is any way to maintain the query results inside active session though the php scripts already ended.
What is the only thing in my thought right now is to extend the where clause (re-starting the query from the very beginning again), which will look something like this:
select * from prodtable where ProdCategoryID=x AND price>$$$ AND price<$$$
In a simple case, the above query will be fine. However my concern is if the query will get more complex, then there will be a lot of “AND” in the where clause.
My point is, if there is any way to maintain the existing query results, and to run filtering upon this existing results instead of getting the sql server to perform query from the very beginning again.
but that adds overhead to the original query, doesn’t it… and there’s no guarantee that additional filtering is always going to happen, so you would be making the original query slower for all users, with the aim of making subsequent filtering easier for some users
my opinion is, if the original query returns all of its results to the application (e.g. php code), than additional filtering should be done there
alternatively, if the original query returns too many rows to display easily, then a re-query with additional filter conditions should be run