Ranking by fields and then order by

Hi,

I have a table with a list of companies, with fields like this
(simplyfied):

ID (int, auto_increment)
CompanyName (text)
ContactPerson (text)
Description (text)
Partner (int)

Listing example:

ID:
15

CompanyName:
BeWide Technologies

ContactPerson:
Morten Tangeraas

Description:
Developing websites and internet applications and programming in php
with mysql.

Partner:
30


They are grouped in partner levels, where the highest priored partners
has ID 40 (in the Partner field) and the lowest has ID 10.

I am currently using a query like this to search the database (example
search string is foo bar):

SELECT * FROM Companies WHERE (CompanyName like '%foo%' OR
ContactPerson like '%foo%' OR Description like '%foo%') AND
(CompanyName like '%bar%' OR ContactPerson like '%bar%' OR Description
like '%bar%') ORDER BY Partner DESC

In many cases, this query doesn't give the most relevant result. What
can I do with my query to make a priority like this:

1. Companies with highest partner number first (this one is ok)

2. Companies with "foo" early in the CompanyName field, then companies
with "foo" later in the CompanyName field

3. The same as in point 2, only in the ContactPerson field, then the
Description field.

4. Then the same as in point 2 and 3, only with the word "bar"

How can I obtain this result with mySQL?

Thanks in advance!

Regards,
Morten Tangeraas
BeWide Technologies, Norway

Re: Ranking by fields and then order by

macmorten@gmail.com wrote:
> Hi,
>
> I have a table with a list of companies, with fields like this
> (simplyfied):
>
> ID (int, auto_increment)
> CompanyName (text)
> ContactPerson (text)
> Description (text)
> Partner (int)
>
> Listing example:
>
> ID:
> 15
>
> CompanyName:
> BeWide Technologies
>
> ContactPerson:
> Morten Tangeraas
>
> Description:
> Developing websites and internet applications and programming in php
> with mysql.
>
> Partner:
> 30
>
>
> They are grouped in partner levels, where the highest priored partners
> has ID 40 (in the Partner field) and the lowest has ID 10.
>
> I am currently using a query like this to search the database (example
> search string is foo bar):
>
> SELECT * FROM Companies WHERE (CompanyName like '%foo%' OR
> ContactPerson like '%foo%' OR Description like '%foo%') AND
> (CompanyName like '%bar%' OR ContactPerson like '%bar%' OR Description
> like '%bar%') ORDER BY Partner DESC
>
> In many cases, this query doesn't give the most relevant result. What
> can I do with my query to make a priority like this:
>
> 1. Companies with highest partner number first (this one is ok)
>
> 2. Companies with "foo" early in the CompanyName field, then companies
> with "foo" later in the CompanyName field
>
> 3. The same as in point 2, only in the ContactPerson field, then the
> Description field.
>
> 4. Then the same as in point 2 and 3, only with the word "bar"
>
> How can I obtain this result with mySQL?
>
> Thanks in advance!
>
> Regards,
> Morten Tangeraas
> BeWide Technologies, Norway

Locate('foo',CompanyName) returns 0 if the searchstring is not present
- which could confuse the results - but there's probably a workaround
for that.

Re: Ranking by fields and then order by

strawberry wrote:
> macmorten@gmail.com wrote:
> > Hi,
> >
> > I have a table with a list of companies, with fields like this
> > (simplyfied):
> >
> > ID (int, auto_increment)
> > CompanyName (text)
> > ContactPerson (text)
> > Description (text)
> > Partner (int)
> >
> > Listing example:
> >
> > ID:
> > 15
> >
> > CompanyName:
> > BeWide Technologies
> >
> > ContactPerson:
> > Morten Tangeraas
> >
> > Description:
> > Developing websites and internet applications and programming in php
> > with mysql.
> >
> > Partner:
> > 30
> >
> >
> > They are grouped in partner levels, where the highest priored partners
> > has ID 40 (in the Partner field) and the lowest has ID 10.
> >
> > I am currently using a query like this to search the database (example
> > search string is foo bar):
> >
> > SELECT * FROM Companies WHERE (CompanyName like '%foo%' OR
> > ContactPerson like '%foo%' OR Description like '%foo%') AND
> > (CompanyName like '%bar%' OR ContactPerson like '%bar%' OR Description
> > like '%bar%') ORDER BY Partner DESC
> >
> > In many cases, this query doesn't give the most relevant result. What
> > can I do with my query to make a priority like this:
> >
> > 1. Companies with highest partner number first (this one is ok)
> >
> > 2. Companies with "foo" early in the CompanyName field, then companies
> > with "foo" later in the CompanyName field
> >
> > 3. The same as in point 2, only in the ContactPerson field, then the
> > Description field.
> >
> > 4. Then the same as in point 2 and 3, only with the word "bar"
> >
> > How can I obtain this result with mySQL?
> >
> > Thanks in advance!
> >
> > Regards,
> > Morten Tangeraas
> > BeWide Technologies, Norway
>
> Locate('foo',CompanyName) returns 0 if the searchstring is not present
> - which could confuse the results - but there's probably a workaround
> for that.

Here's a very crude workaround. Shows how bad I am at maths!

SELECT *,

1000-((Locate('foo',CompanyName)/LOCATE('foo',CompanyName))* LOCATE('foo',Company))
as cn_pos

FROM Companies WHERE (CompanyName like '%foo%' OR
ContactPerson like '%foo%' OR Description like '%foo%') AND
(CompanyName like '%bar%' OR ContactPerson like '%bar%' OR Description
like '%bar%') ORDER BY Partner DESC,cn_pos DESC;

obviously just copy/paste for the other fields

Re: Ranking by fields and then order by

Thank you for the reply, it seem to work just fine! Have to try it out
with the database a little to see if it gives the wanted results...
but, what is LOCATE acctually doing? I can't find any good
documentation of it...

Regards,
Morten

strawberry wrote:

[...]

> SELECT *,
>
> 1000-((Locate('foo',CompanyName)/LOCATE('foo',CompanyName))* LOCATE('foo',Company))
> as cn_pos
>
> FROM Companies WHERE (CompanyName like '%foo%' OR
> ContactPerson like '%foo%' OR Description like '%foo%') AND
> (CompanyName like '%bar%' OR ContactPerson like '%bar%' OR Description
> like '%bar%') ORDER BY Partner DESC,cn_pos DESC;
>
> obviously just copy/paste for the other fields