peasepud
Programmer
- Jul 20, 2007
- 35
Hi,
Im developing a site which allows the user to search on a dozen different criteria each time narrowing down and displaying the results.
I currently have it working by merely adding the AND newsearchcriteria on the end of the last search so you get
SELECT * FROM table where person = female
returning 100 results so user clicks "taller than 1 metre"
to give
SELECT * FROM table where person = female and height > 12 (Im storing the likes of height in a dropdown)
to give 30 results then "age below 50"
to add "AND age < 50" to the query etc etc etc
the 'problem' I have (and its merely performance and tidyness) is that a user could also then click "taller than 1.5 metres" which would still give the same correct recordset back but would make the query...
SELECT * FROM table where person = female and height > 12 AND age < 50 AND height > 15
thereby giving an unneeded height, at the same time I need to allow the facility for two heights to be present to allow users to say > 1 metre and < 1.5 metres for instance.
The idea I have in my head (which is almost always wrong) is to have an array containing the start of each criteria as its added so the array in the case above (before the 2nd height) would be:
field value
"height >" 12
"age <" 50
and each time a new search is carried out it would check if the field existed, if so amend the value and if not add it as a new entry.
So (after 3 pages of waffle) does this make sense to do it this way or am I missing a simpler more sensible option?
Im developing a site which allows the user to search on a dozen different criteria each time narrowing down and displaying the results.
I currently have it working by merely adding the AND newsearchcriteria on the end of the last search so you get
SELECT * FROM table where person = female
returning 100 results so user clicks "taller than 1 metre"
to give
SELECT * FROM table where person = female and height > 12 (Im storing the likes of height in a dropdown)
to give 30 results then "age below 50"
to add "AND age < 50" to the query etc etc etc
the 'problem' I have (and its merely performance and tidyness) is that a user could also then click "taller than 1.5 metres" which would still give the same correct recordset back but would make the query...
SELECT * FROM table where person = female and height > 12 AND age < 50 AND height > 15
thereby giving an unneeded height, at the same time I need to allow the facility for two heights to be present to allow users to say > 1 metre and < 1.5 metres for instance.
The idea I have in my head (which is almost always wrong) is to have an array containing the start of each criteria as its added so the array in the case above (before the 2nd height) would be:
field value
"height >" 12
"age <" 50
and each time a new search is carried out it would check if the field existed, if so amend the value and if not add it as a new entry.
So (after 3 pages of waffle) does this make sense to do it this way or am I missing a simpler more sensible option?
