Complicated search form database

[abienz]

Hi there,

I've got page that allows a user to enter in a year to search a field in a database, this all sounds simple enough but this is where it gets tricky.

The field in the databse has results like these...

1822
1825-1930
1876

the user NEEDS to be able to search this field by typing into the form things like this...

19th Century - would return 1822, 1825-1930 and 1876
early 19th Century - would return 1822 and 1825-1930
mid 19th Century - would return 1825-1930
late 19th Century - would return 1876
circa 1850 - would return 1825-1930
approx 1850 - would return 1825-1930
1900-1922 - would return 1825-1930
1867 - wouldn't return any

So you see it's not as simple as entering a date into a field and searching by that.

How do you recommend I go about using the users input to search the field which is stored as a varchar datatype.

 

[derren]

Hi

Nasty. Why not create a series of drop down menus which will create the query for you? For example in list one put "early","mid","late","approx","circa","exactly","between".

Then in list two put lists of centuries for them to choose

List three would be a start date text box and list 4 would be an end date list box.

Then their query will be constructed by them in controlled bits. Here are some possible returns:

"early","18","","" - early 19th C
"approx","",1850","" - obvious one
"between","","1900","1922" - blindingly obvious.

Then it's up to you to check these values and construct the SQL query based on them. It will be reasonable to do as well, it just may take some head-banging time. Derren
[The only person in the world to like Word]