php - prioritizing MySQL v5.6 results

PHP 7.3 / MySQL v5.6 MyISAM multi-query is using BOOLEAN MODE:

We have a variety of musical instruments in the database. We would like to return a list of all guitars and prioritize the red or white guitars at the top of the result.

Based on the definition of the tilde operator, we thought it might be the right approach. Unfortunately with a query of...

~guitar red white
Copy code

... the results include all instruments, not just guitars. It looks as if "noise words" (in the definition) means that the word is marked irrelevant in the search query. The result is the same as sendingred white. Not sure of the value of this.

We've tried some other ideas...

~(+guitar) red white  // also throws out "guitar."

+(~guitar) red white  // matches nothing, I guess because we're matching a noise word.

+guitar >(red white) // matches all guitars and doesn't do anything with red or white.

+guitar (>red >white) // same as above.

+guitar +(red white) // matches only guitars that are red or white
Copy code

None of the above give us all guitars, prioritizing the red and white ones. We don't know what customers will type when searching, so red and white are just examples of attributes that should have higher priority. I'll be hard coding a special list to give those attributes the extra emphasis in the code.

Surely there's a simple solution?

By the way, we are using ORDER BY id DESC so the latest IDs come first, but we want the search queries to have precedence. Ideally, the results would show the latest red guitars, latest white guitars, and finally the rest of the guitars from the latest to the earliest.

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.