MySQL/PHP Selection and Ordering Results

[mattbrand]

Hey folks.

 

I have a table of products that has a name and a description field. I have a fulltext index on both the fields, because I want to do a search that looks in both fields.

 

My problem is that I want to sort the results by products that have the keyword in the name first, followed by those that only have the keyword in the description.

 

And I have no idea how to do it. Here is my statement:

 

select * from luna_products where match(luna_products_name,luna_products_description) against ('$keywordlist')

 

Thanks for any and all help.

[fenway]

You'd have to fulltext index each column separately if you want to treat them differently for sorting.

[mattbrand]

You'd have to fulltext index each column separately if you want to treat them differently for sorting.

 

OK. Thanks for the advice. So I created a fulltext index on each column separately, as suggested. And I am now playing around with relevance for the results.

 

So far, this query works for me:

 

$query = "SELECT * FROM luna_products WHERE (MATCH(luna_products_name) AGAINST ('$keywordlist') + MATCH(luna_products_description) AGAINST('$keywordlist'))";

 

But now I want to order the results based on name first, then description. I think ultimately my query should look like this:

 

$query = "SELECT MATCH(luna_products_name) AGAINST('$keywordlist') AS relevance_title,

MATCH(luna_products_description) AGAINST('$keywordlist') AS relevance_description,

* FROM luna_products WHERE (MATCH(luna_products_name) AGAINST ('$keywordlist')) +

(MATCH(luna_products_description) AGAINST ('$keywordlist'))

ORDER BY relevance_title DESC,relevance_description DESC";

 

However, when I add the additional match statements to the beginning of the query in order to generate the relevance fields, I then get nothing in my results. I have tried it with and without the order section at the end, with no results either way.

 

Any additional advice is greatly appreciated.

[fenway]

I'd leave your original where clause intact, and just use the individual relevances for the scoring/ordering.

[mattbrand]

I'd leave your original where clause intact, and just use the individual relevances for the scoring/ordering.

 

I am unsure how to do that without adding the match statements to the beginning of the select...

[fenway]

I'd leave your original where clause intact, and just use the individual relevances for the scoring/ordering.

 

I am unsure how to do that without adding the match statements to the beginning of the select...

You should add them to the select... just not the where clause.

[mattbrand]

I'd leave your original where clause intact, and just use the individual relevances for the scoring/ordering.

 

I am unsure how to do that without adding the match statements to the beginning of the select...

You should add them to the select... just not the where clause.

 

I am kinda confused as to specifically how to do that. Sorry if I sound dumb...

 

I think you may be saying to do it like this:

 

$ProductsSelectStrStart = "SELECT * FROM luna_products WHERE (MATCH(luna_products_name) AGAINST ('$keywordlist')) +

(MATCH(luna_products_description) AGAINST ('$keywordlist')),

MATCH(luna_products_name) AGAINST('$keywordlist') AS relevance_title,

MATCH(luna_products_description) AGAINST('$keywordlist') AS relevance_description

ORDER BY relevance_title DESC,relevance_description DESC";

 

I took the 2 match statements that generate the relevance information, and put them after the where clause. But that still gives me no results...

[fenway]

I mean this.

SELECT MATCH(luna_products_name) AGAINST('$keywordlist') AS relevance_title,
MATCH(luna_products_description) AGAINST('$keywordlist') AS relevance_description,
* FROM luna_products where match(luna_products_name,luna_products_description) against ('$keywordlist')
ORDER BY relevance_title DESC,relevance_description DESC

[mattbrand]

I mean this.

SELECT MATCH(luna_products_name) AGAINST('$keywordlist') AS relevance_title,
MATCH(luna_products_description) AGAINST('$keywordlist') AS relevance_description,
* FROM luna_products where match(luna_products_name,luna_products_description) against ('$keywordlist')
ORDER BY relevance_title DESC,relevance_description DESC

 

Isn't that basically what I posted in post #3, but joining the match statements for the where clause? If so, I don't think that will work, as I now have separate fulltext indexes for the 2 fields I am matching on.

[fenway]

Sorry, I didn't mean to *remove* the 2-column index.... you'll need to add the 2 news ones.

[mattbrand]

Sorry, I didn't mean to *remove* the 2-column index.... you'll need to add the 2 news ones.

 

Actually, I got it to work. I needed to have the * before the other matches that were being selected. So what worked is:

 

SELECT *, MATCH(luna_products_name) AGAINST('$keywordlist') AS relevance_title,

MATCH(luna_products_description) AGAINST('$keywordlist') AS relevance_description

FROM luna_products

WHERE MATCH(luna_products_name) AGAINST ('$keywordlist') +

MATCH(luna_products_description) AGAINST ('$keywordlist')

ORDER BY relevance_title DESC,relevance_description DESC;";

 

Thanks for all your advice.

[fenway]

Yes, I didn't notice that, * always has to be first.  Just be aware the the scoring will be fundamentally different: P(A) + P(B) != P(A+B).