From: tisniewaarhe
Date: April 14 1999 9:44am
Subject: Re: Group by order by can't use expresions so how do I
List-Archive: http://lists.mysql.com/mysql/1818
Message-Id: <37146379.EFEA6CBC@hotmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Rich,

Thanks for the example you gave.

the

 my $sth = $dbh->prepare("select
Company.Comp_ID,Company.Name,Customer.Name,FirstName,Phone,Cust_ID,
                                          4-((Company.Name like '$cmp') + (
Customer.Name like '$cust' ) +
                                          ( Customer.FirstName like '$fnme' ) +
( Phone like '$phn' )) as score
                         from   Company,Customer
                         where  (Company.Name like '$cmp' or Customer.Name like
'$cust' or Customer.FirstName like '$fnme'
                                       or  Phone like '$phn' ) and
Company.Comp_ID = Customer.Comp_ID
                         order  by score
                            ");
$sth->execute();

works as I hoped it would

Regards,

Michel



Rich Gibson wrote:

> 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
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread1733@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.