|
|
Posted by Rik on 05/16/06 19:28
himilecyclist@yahoo.com wrote:
> The application is a database of vital event records (death records in
> this case). The users want to be able to enter a name to search for
> and then have the list box populated with names, starting with the
> name entered to search for, and then proceeding for 200 records,
> sorted by last, first, middle.
>
> I considered the solution you have suggested, but am almost certain it
> won't fit our needs. For example, if the user searches for last name
> "Smith" and first name "Susan", the search will indeed start with
> Susan Smith, but as soon as last name "Sorensen" and first name
> "Anna" is reached, this record will not be included since "Anna" is
> not >= "Susan".
Why give your users an option to search on that if you don't want that
search executed?
If they want Anna Sorensen, they shouldn't set Firstname.....
Maybe you threw me off by stating:"the user has an option to search by name
fields in the database", while you actually mean: "the user has an option to
pick the starting point for the next 200 records in a sorted list".
If all you want to do is display the next 200 people from a list starting at
a certain name, sorted by Last, First, Middle:
SELECT fields FROM table
WHERE
(Last = $_POST[Last] AND First = $_POST[First] AND Middle
>=$_POST['Middle'])
OR (Last = $_POST[Last] AND First >= $_POST[First])
OR (Last > '$_POST[Last]')
ORDER BY Last, First, Middle
LIMIT 200
> I still can't think of any solution other than padding each of the
> names involved (search strings and names returned from the database)
> with spaces. Not being very familiar with SQL, I'm not sure how to do
> that on the database side.
That way:
You include all names with the exact Lastname, from Firstname on, and ALL
Firstnames of following different Lastnames. Effectively only really
searching on Lastname, if that's what you want, implement it like that.
I'd think long and hard how fuzzy you want your search to be, and
specificate EXACTLY how you want your results to be. The current
implementation starts of with a couple of wrong assumptions.
In this case, you might think about SOUNDEX(), LIKE %string% and functions
like that, it depends on what functionality you want to give the user.
Maybe more something like:
SELECT First, Middle, Last, ((Last='$_POST[Last]') + (Middle
='$_POST[Middle]') + (First = '$_POST[First]')) as score
FROM table
WHERE SOUNDEX(Last) = SOUNDEX('$_POST[Last]' OR Last LIKE '%$_POST[Last]'%'
OR
SOUNDEX(First) = SOUNDEX('$_POST[First]' OR First LIKE '%$_POST[First]'%' OR
SOUNDEX(Middle) = SOUNDEX('$_POST[Middle]' OR Middle LIKE
'%$_POST[Middle]'%' OR
ORDER BY score, Last, First, Middle
LIMIT 200
But I'm not a man for fuzzy searches, I assume some people here or more
experienced in that matter.
Grtz,
--
Rik Wasmus
Navigation:
[Reply to this message]
|