|
|
Posted by Rik on 05/16/06 04:14
himilecyclist@yahoo.com wrote:
> 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?
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[txtSrchFirstName]'
AND Middle >='$_POST[txtSrchMiddleName]'
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[txtSrchFirstName]'
AND Middle >='$_POST[txtSrchMiddleName]'
ORDER BY Last, First, Middle
LIMIT 200
Grtz,
--
Rik Wasmus
Navigation:
[Reply to this message]
|