MySQL Order Of Importance

[The Little Guy]

OK... I Have a query here... I need this:
I need these to have an importance rate, where order of importance is as follows:
1. artist
2. title
3. album
4. lyric

artist is heaviest, and should be displayed higher in the returned results.
So... My question is... How do I get them to weigh what I want?

[code]$query = "SELECT artist,lyric,title,album,
MATCH(artist, album, title, lyric) AGAINST ('$trimmed' IN BOOLEAN mode)
AS score
FROM lyrics
WHERE 
MATCH(artist, album, title, lyric) AGAINST ('$trimmed' IN BOOLEAN mode) ORDER BY score DESC";[/code]

[the_oliver]

could simply run it as four searches.  Serch artist first, then title etc.... Have each search add on to the previous results in a table/list/whatever..... Thats the simplest way, but if your pulling large amouts form your table, or have large numbers of quieryes running syimoltainusly it could slow things down.  Depending on what database your using it would have to be quite a lot though! (maybe if its large you should consider postgreSQL?)

Alternitivly asign a number to each (artist = 1, title = 2 etc), and have your database sort its table numericaly.

Hope this helps

[Psycho]

Or, just compute the four different scores in one query and do the weighting within the php
[code]$query = "SELECT artist,lyric,title,album,
MATCH(artist) AGAINST ('$trimmed' IN BOOLEAN mode)
AS score1
MATCH(album) AGAINST ('$trimmed' IN BOOLEAN mode)
AS score2
MATCH(title) AGAINST ('$trimmed' IN BOOLEAN mode)
AS score3
MATCH(lyric) AGAINST ('$trimmed' IN BOOLEAN mode)
AS score4
FROM lyrics
WHERE 
MATCH(artist, album, title, lyric) AGAINST ('$trimmed' IN BOOLEAN mode) ORDER BY score DESC";[/code]

Then in the php calculate $score as ($score1 * 4) + ($score2 * 3) + ($score3 * 2) + ($score4 * 1)

or whatever weighting you want.

[The Little Guy]

[code]$query = "SELECT artist,lyric,title,album,
MATCH(artist) AGAINST ('$trimmed' IN BOOLEAN mode) AS score1,
MATCH(album) AGAINST ('$trimmed' IN BOOLEAN mode) AS score2,
MATCH(title) AGAINST ('$trimmed' IN BOOLEAN mode) AS score3,
MATCH(lyric) AGAINST ('$trimmed' IN BOOLEAN mode) AS score4
FROM lyrics
WHERE 
MATCH(artist, album, title, lyric) AGAINST ('$trimmed' IN BOOLEAN mode) ORDER BY score1 ASC";[/code]

I modified your so it works didn't bring up errors...

for [b]ORDER BY score1[/b], do I need to make it like it is now or like this:
[b]ORDER BY score1, score2, score3, score4 ASC[/b]

Now what do you mean use php to calculate $score?  I have never done something like that.

[Psycho]

Well, many times when doing such a ranking you display the results with a value indicating their overall relevance. In this case you have four different numbers. What I was trying to say is that you could come up with a "total" score based upon whatever weight you want to assign to each field match. In my example I was weighting the first field 4x, the 2nd field 2x, the 3rd fiel 2x and the 4th field at 1x. You could do those calculations and add it all up to come up with a combined score if you so wish.

Also, in your query, you should add sorting for the 2nd, 3rd, & 4th values as well.

[The Little Guy]

How do I get the score of each one from that query? By saying [b]AS score1[/b], will it automatically make score1 become a variable, and same with score2, 3, and 4?  If so... what value do those receive?

[printf]

I don't know what your doing with creating a boolean relevance search in that manner, it doesn't make much sense to do the same search repeatably, when all your wanting to do is order / return the listing by field order, based on the matches found and return the [b]single relevance value for each relevance ordered row returned[/b]. If that's not want your trying to do then please disregard my comment. If that is what your trying to do, tell me and I'll show how to do it the right way.


printf

[The Little Guy]

Basically it is a site search to search all the lyrics within my database... Here is the page http://d-top.org/ the search is on the top right side of the page... You tell me how you think that should be done. Please.