PHP/MySQL Fulltext Index and Matching

I need to order a search by relevance

[mattbrand]

Hey folks,

I have a search that I am looking for a keyword in a name field AND a description field. I want to order the results by first if the keyword is found in the name, and then if the keyword is found in the description.

I created separate fulltext indexes on the name and the description field, and the following query successfully executes:

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

Ultimately I believe 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, once I add the additional match statements at the beginning of the query string, I then get nothing in the results. I've tried it with or without the ordering at the end, and still get nothing.

Anyone have any advice?

Thanks in advance...

[whoisgregg]

Have you tried echoing your $query variable and copying it directly into PHPMyAdmin to see if it throws errors there?

Often, PHPMyAdmin will identify syntax errors (like missing/extra commas, quotes).

[mattbrand]

Thank you, that is actually exactly what I ended up doing. And it turns out that you have to select * first, before doing the matching. Once I took care of this, it worked.