Hi Michel,
I assume that you have a 'client_id' type of field, assuming so, try
modifying your query to something like this:
select *,
(FirstName like $fname) as fname_Match,
(Name like $name) as name_Match,
(Phone like $phone) as phone_Match,
(Company like $comp) as company_Match
from contacts
where FirstName like $fname
or Name like $name
or Phone like $phone
or Company like $comp
group by client_id
If I am thinking straight, that will give you a 1 or 0 in those 'Match'
fields, and then you can check them.
Or _maybe_ this will work (the order by is, in my mind, what is most likely
to fail):
select *,
(FirstName like $fname) + (Name like $name)
+ (Phone like $phone) + (Company like $comp) as Match_Count
from contacts
where FirstName like $fname
or Name like $name
or Phone like $phone
or Company like $comp
group by client_id
order by Match_Count
Good luck, and let us (or at least me) know how it works.
Cheers,
Rich
At 03:05 PM 4/12/99 +0200, you wrote:
>Hi guru's,
>
>I'm trying to create a call logging system and want to find contacts in
>a kind of fuzzy way.
>
>I created a database with client names, their phone numbers and company
>names.
>
>I created a script that searches the corresponding database fields for
>something "like" the contents of those fields.
>
>the user can type something like
>
>name : johnson
>firstname : pete
>phone : 67890
>company : FastLane
>
>the search does:
>
>select * form contacts where FirstName like $fname or Name like $name or
>Phone like $phone or Company like $comp
>
>Now I want get the ones where I have the most "hits" in the multiple
>"or" on top.
>
>Do I search in the directions of aliases to do this? Do I need to make a
>temporary table?
>
>If anyone has got a sample of a comparable search......
>
>Regards
>
>Michel
--
Rich Gibson 303/271-1025 or 303-881-7299 (cell)
Rich@stripped - http://www.chilidog.com
ICQ #35295077
