Help with MYSQL query
Posted on 2012-09-13
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.
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?