phpmysqlpdogeolocationwhere-in

MYSQL PDO lat lon searching with multiple in clause

I cannot rack my brain around how to construct this sql query.

I have 3 tables.

Users table

  1. user_id
  2. name
  3. picture etc

tag_ref table

  1. user_id
  2. tag_id

geolocation table

  1. user_id
  2. geolat
  3. geolon

What I need to be able to do is find users based on location and what tags they have associated with them. I have both queries working seperately.

This is what I have used for the location query:

$sql_search_people = "SELECT user_id, ( 3959 * acos( cos( radians(?) ) * cos( radians( geolat ) ) * cos( radians( geolon ) - radians(?) ) + sin( radians(?) ) * sin( radians( geolat ) ) ) ) AS distance FROM geolocation 
        HAVING distance < '25'";

I then need to filter results by tag_id from tag_ref table. Users can search by multiple tags. I have done this seperatley here:

$sql_search_people = "SELECT b.user_id, b.name, b.picture, b.tagline, b.genres FROM tag_ref AS a LEFT JOIN users AS b ON a.user_id = b.user_id WHERE a.tag_id IN ($in) GROUP BY a.user_id";

Now I just need to figure out how to combine the two together into one query. Which I cannot figure out! I've tried joins and sub queries but am really struggling to understand these.

Any help would be greatly appreciated.

** UPDATE **

After playing about a bit more i managed to get this to work:

    $sql_search_people = "SELECT a.user_id, b.user_id, c.user_id, c.tag_id, b.name, ( 3959 * acos( cos( radians(?) ) * cos( radians( geolat ) ) * cos( radians( geolon ) - radians(?) ) + sin( radians(?) ) * sin( radians( geolat ) ) ) ) AS distance FROM geolocation AS a RIGHT JOIN tag_ref AS c ON a.user_id = c.user_id RIGHT JOIN users AS b ON a.user_id = b.user_id WHERE c.tag_id IN ($in) HAVING distance < '25' ";

But of course it is duplicating results where a user has more than one tag. When I add a group by to group by user_id the query fails?

Solution

  • I managed to work this out if anyone else is facing the same situation.

    I have no idea what I was originally trying to do. It's much simpler than that. Here is what i've done:

    Check each tag and add to array:

    $in = "";
    foreach ($tags as $i => $item)
    {
        
        if(!is_numeric($item)){
            return $response = array(
                'status' => 'error',
                'message' => 'invalid category',
                'code' => 400
                );
        }            
        
        $key = ":id".$i;
        $in .= "$key,";
        $in_params[$key] = $item; // collecting values into key-value array
    }
    $in = rtrim($in,",");

    The query:

     $sql = '
        SELECT l.user_id, l.city, u.name, u.tagline, u.photo_id, u.specialties, p.image
        FROM location l 
        LEFT JOIN user_categories AS c ON l.user_id = c.user_id
        LEFT JOIN user AS u ON l.user_id = u.id 
        LEFT JOIN photos AS p ON u.photo_id = p.id
        WHERE ST_Distance_Sphere(l.point, POINT(:lon, :lat)) < :distance AND c.tagid IN ('.$in.') 
        GROUP BY l.user_id
    ';


    $stmt = $conn->prepare($sql);

    $params = ['lat' => $lat, 'lon' => $lon, 'distance' => $distance];
    $stmt->execute(array_merge($params,$in_params));