List:General Discussion« Previous MessageNext Message »
From:Rich Gibson Date:April 12 1999 10:37pm
Subject:Re: Group by order by can't use expresions so how do I
View as plain text  
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



Thread
Group by order by can't use expresions so how do Itisniewaarhe12 Apr
  • Re: Group by order by can't use expresions so how do IRich Gibson13 Apr
Re: Group by order by can't use expresions so how do Itisniewaarhe14 Apr