This query works ok, but when it shows the results it shows them alphabetically, rather than with the searched results first.

Say I searched for Baby Diapers, It will show Baby Diapers but at alphabetic position B, rather than Baby Diapers being the first results shown.

Adult diapers Baby diapers Casual diapers Disposable diapers

I would like the results to be like this.

Baby diapers Adult diapers Disposable diapers Casual diapers

There are other relevant results being shown obviously So they all are found and displayed together.

How should i amend this query so that the Baby Diapers will be the first result amongst them all.

select * from TheTable WHERE MATCH (`title`) AGAINST ('$search') AND (`category`) LIKE '%$category%' AND (`genre`) LIKE '$genre%' ORDER BY `title` ASC LIMIT $offset, $no_of_records_per_page

CodePudding user response：

If you want certain hits to come up first in your result set you need to fiddle with your ORDER BY clause. Try something like this:

ORDER BY `title` LIKE `Baby Diapers%` DESC, `title`

If the title starts with that string, the LIKE clause has a value of 1, otherwise a value of 0. So ordering by it in descending order makes the rows it matches come first.

Do keep in mind that FULLTEXT search works peculiarly when you don't have very many rows in your tables; it relies to some extent on word frequency, and when there aren't many words the frequencies can be distorted. This can be very confusing when you test your stuff with small datasets.

And if you use ORDER BY ... MATCH (title) AGAINST ('$search') you'll get the rows in order of FULLTEXT's quality of match, for what it's worth.

Beware SQL injection attacks.

(Notice that this business of the values 1 and 0 coming from expressions such as title LIKE 'Baby Diapers%' is a MySQL-specific hack. Other makes and models of table server work differently.)