I'm using the Haversine formula with this query and it works until. The goal is to check if each row has certain categories in an value of $deflin which looks like $deflin = category1, category2, category3. The results will show rows within 50km and if it contains any of the categories defined by $deflin. Not sure how to approach this either with WHERE IN or LIKE. Any help is appreciated.
MySQL for query
$awaka = "SELECT *,
( 6371 * acos( cos( radians(?) ) * cos( radians(job_latitude) ) *
cos(radians(?) - radians(job_longitude) ) + sin( radians(?) ) *
sin( radians(job_latitude) ) ) ) AS distance FROM job, users
WHERE job.listee_id = users.user_id AND job.job_category LIKE ?
HAVING distance < 50";
$result = $this->db->query($awaka, array($conlat, $conlong, $conlat, $deflin));
I don't think you like to use the LIKE operator since it only searches for patterns within a column and you only wan't to return rows that really has one of your categories. Instead you should use an IN clause to check if a job has one of your categories:
// contains the id for each category
$categories = array(1, 4, 5, 6);
$deflin = implode(',', $categories);
// if job_category is a text column you could do like this instead
$categories = array('category1', 'category2', 'category3');
$deflin = implode(',', $categories);
$awaka = "SELECT *,
( 6371 * acos( cos( radians(?) ) * cos( radians(job_latitude) ) *
cos(radians(?) - radians(job_longitude) ) + sin( radians(?) ) *
sin( radians(job_latitude) ) ) ) AS distance FROM job, users
WHERE job.listee_id = users.user_id AND job.job_category IN ($deflin)
HAVING distance < 50";