Posted by himilecyclist on 05/16/06 03:27

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[txtSrchFirstName]', '$_POST[txtSrchMiddleName]')";

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 >= "ColemanAllen" 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!

[Back to original message]