Retrieving and sorting names from MySQL

[himilecyclist@yahoo.com]

I have a query screen where the user has an option to search by name
fields in the database. There are first, middle and last name fields
and the results returned should be sorted last, first, middle. Here is
the WHERE part of my query:

WHERE CONCAT(Last, First, Middle) >= CONCAT('$_POST[txtSrchLastName]',
'$_POST[txtSrchFirstNam e]', '$_POST[txtSrchMiddleNa me]')";

There is an ORDER BY and LIMIT 200 at the end of this and the results
are loaded into a list box.

This seemed to work fine until I ran into a case where the user was
searching for "Allen Coleman". The search returns started with "Stuart
Cole". The "Allen Coleman" record appears down the list a ways. What
is happening is the concatenated database fields "ColeStuart " satisfy
the >= "ColemanAll en" and thus this record is wrongly included.

One solution would be to append spaces to the ends of all 6 of the name
fields, padding them to their full length of 50 characters. But, I'm
not able to find the sort of syntax to use in SQL for padding the
strings to their maximum length.

Or, is there a better solution?

Any hints greatly appreciated!

[Rik]

Re: Retrieving and sorting names from MySQL

himilecyclist@y ahoo.com wrote:[color=blue]
> I have a query screen where the user has an option to search by name
> fields in the database. There are first, middle and last name fields
> and the results returned should be sorted last, first, middle. Here
> is the WHERE part of my query:
>
> WHERE CONCAT(Last, First, Middle) >= CONCAT('$_POST[txtSrchLastName]',
> '$_POST[txtSrchFirstNam e]', '$_POST[txtSrchMiddleNa me]')";
>
> There is an ORDER BY and LIMIT 200 at the end of this and the results
> are loaded into a list box.
>
> This seemed to work fine until I ran into a case where the user was
> searching for "Allen Coleman". The search returns started with
> "Stuart Cole". The "Allen Coleman" record appears down the list a
> ways. What is happening is the concatenated database fields
> "ColeStuart " satisfy the >= "ColemanAll en" and thus this record is
> wrongly included.
>
> One solution would be to append spaces to the ends of all 6 of the
> name fields, padding them to their full length of 50 characters.
> But, I'm not able to find the sort of syntax to use in SQL for
> padding the strings to their maximum length.
>
> Or, is there a better solution?[/color]


Yep, don't CONCAT. What's the particular use you had in mind for this
anyway?

SELECT fields
FROM table
WHERE Last >='$_POST[txtSrchLastName]'
AND First >='$_POST[txtSrchFirstNam e]'
AND Middle >='$_POST[txtSrchMiddleNa me]'
ORDER BY Last, First, Middle
LIMIT 200

Or if you want to keep your CONCAT:

SELECT CONCAT(Last, First, Middle)
FROM table
WHERE Last >='$_POST[txtSrchLastName]'
AND First >='$_POST[txtSrchFirstNam e]'
AND Middle >='$_POST[txtSrchMiddleNa me]'
ORDER BY Last, First, Middle
LIMIT 200

Grtz,
--
Rik Wasmus

[himilecyclist@yahoo.com]

Re: Retrieving and sorting names from MySQL


Rik wrote:
[color=blue]
> Yep, don't CONCAT. What's the particular use you had in mind for this
> anyway?
>
> SELECT fields
> FROM table
> WHERE Last >='$_POST[txtSrchLastName]'
> AND First >='$_POST[txtSrchFirstNam e]'
> AND Middle >='$_POST[txtSrchMiddleNa me]'
> ORDER BY Last, First, Middle
> LIMIT 200
>
> Or if you want to keep your CONCAT:
>
> SELECT CONCAT(Last, First, Middle)
> FROM table
> WHERE Last >='$_POST[txtSrchLastName]'
> AND First >='$_POST[txtSrchFirstNam e]'
> AND Middle >='$_POST[txtSrchMiddleNa me]'
> ORDER BY Last, First, Middle
> LIMIT 200
>
> Grtz,
> --
> Rik Wasmus[/color]

Thanks, Rik.

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".

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.

Any more ideas? Thanks!

[himilecyclist@yahoo.com]

Re: Retrieving and sorting names from MySQL

I just figured it out. The following code does just what I need:

WHERE CONCAT(RPAD(Las t, 50, ' '),
RPAD(First, 50, ' '),
RPAD(Middle, 50, ' ')) >=
CONCAT(RPAD('$_ POST[txtSrchLastName]', 50, ' '),
RPAD('$_POST[txtSrchFirstNam e]', 50, ' '),
RPAD('$_POST[txtSrchMiddleNa me]', 50, ' '))";

Thanks again for the response!

[Rik]

Re: Retrieving and sorting names from MySQL

himilecyclist@y ahoo.com wrote:[color=blue]
> 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".[/color]

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[color=blue]
>=$_POST['Middle'])[/color]
OR (Last = $_POST[Last] AND First >= $_POST[First])
OR (Last > '$_POST[Last]')
ORDER BY Last, First, Middle
LIMIT 200
[color=blue]
> 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.[/color]


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

[himilecyclist@yahoo.com]

Re: Retrieving and sorting names from MySQL

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:[color=blue]
> himilecyclist@y ahoo.com wrote:[color=green]
> > 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".[/color]
>
> 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[color=green]
> >=$_POST['Middle'])[/color]
> OR (Last = $_POST[Last] AND First >= $_POST[First])
> OR (Last > '$_POST[Last]')
> ORDER BY Last, First, Middle
> LIMIT 200
>[color=green]
> > 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.[/color]
>
>
> 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[/color]