I'm finding it hard to articulate exactly what's happening. Please bear with me.
Say I have the following records in the "city" field:
city
San Francisco, CA
San Francisco
San Jose
San Dimas
Their corresponding ratings are:
ratings
3
2
5
4
User searches for "San Francisco" and wants to sort by rating. I use the following fulltext query to pull up the results:
Code: Select all
SELECT *, (MATCH(details) AGAINST ('$search_term')) AS score
WHERE (MATCH(details) AGAINST ('$search_term')) ORDER BY ratings DESC, score DESCSan Jose 5
San Dimas 4
San Francisco, CA 3
San Francisco 2
When I would like:
San Francisco, CA 3
San Francisco 2
I want to somehow use the alias variable 'score' to order the results as well as take ORDER BY ratings into account.
How can I accomplish this?