Ranking by fields and then order by

[macmorten]

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

[strawberry]

ma*******@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.

[strawberry]

strawberry wrote:

ma*******@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

[macmorten]

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