Posted by himilecyclist on 05/17/06 01:13

Rik,

You are correct. What we want is for the user to pick the starting
point in the list of names and then load the next 200. I tried your
suggested method and it works great. It is also noticeably faster in
loading the 200 records than my approach with the RPADs.

Thank you very much! We will use this new method.


Rik wrote:
> 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

[Back to original message]