Help with MYSQL query


I've currently got a query that returns results from a database using their longitude/latitude.

SELECT s.*, 6371.04 * acos( cos( pi( ) /2 - radians( 90 - latitude) ) * cos( pi( ) /2 - radians( 90 - '$lat' ) ) * cos( radians( longitude) - radians( '$lon' ) ) + sin( pi( ) /2 - radians( 90 - latitude) ) * sin( pi( ) /2 - radians( 90 - '$lat' ) ) ) AS distance, a.average_rating FROM cms_salon s LEFT JOIN (SELECT ROUND(AVG(rating)) AS average_rating, salon_id FROM comments GROUP BY salon_id) a on a.salon_id = s.salon_id WHERE s.salon_id > 0 OR s.salon_name LIKE '%$keyword%' GROUP BY s.salon_id HAVING distance < '$radius' ORDER BY distance ASC

I've recently been asked to amend the query so that it searches by both location and name. I've added the bit in bold but it doesn't really work well.

I know what I need to do, but I don't know how to do it. I think what I need is to be able to perform an 'OR' query on the distance and salon name.

For example..

WHERE distance < '20' OR s.salon_name LIKE '%some name%'

However I can't use distance within the WHERE, it only works using HAVING.

Can anyone help?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do two queries for the two different criteria and just combine them using UNION.
The 'or' should work in the having too ... try
    HAVING distance < '$radius' OR s.salon_name LIKE '%some name%'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SheppardDigitalAuthor Commented:
The issue I have with that is that the location based query has one additional column (distance)
SheppardDigitalAuthor Commented:
Yep, it does.

Thank you.
Hmmm didn't know MySQL allowed that.  Just note that as stated in, it is a non-standard extension done by MySQL.  The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.