基于三个数据库表的查询始终返回零结果

Question

I have a database with three tables in it:

places:

id    |  name  |  latitude  | longitude  |
------|--------|------------|------------|
1     | place1 | 11.123456  | 76.123456  |
------|--------|------------|------------|
2     | place2 | 23.123456  | 65.123456  |
etc ...

categorized_places:

id    | place_id | cat_id |
------|----------|--------|
1     |    1     |   2    |
------|----------|--------|
2     |    2     |   1    |
etc ...

places_visited:

id    | user_name | user_email | place_id |
------|-----------|------------|----------|
1     |   user_1  | [e-mail] |    2     |
------|-----------|------------|----------|
2     |   user_2  | [e-mail] |    2     |

There's also a fourth named categories, but it's not important in this.

I'm trying to filter the places from the places-table to show the user the nearest place, that he/she has not yet visited.

$cur_cat is set on the previous page, where the user selects which kind of place he/she would like to visit.

$cur_user and $cur_user_email are based on $_SESSION variables

$max_lat, $max_lon, $min_lat and $min_lon are based on the users current position

I'm using this code in php (with PDO), but it always returns zero results:

$get_places = $db->prepare("
    SELECT
        places.id,
        places.name,
        places.latitude,
        places.longitude
    FROM
        places,
        categorized_places,
        places_visited
    WHERE
        places.id = categorized_places.place_id
        AND categorized_places.cat_id = '$cur_cat'
        AND places.latitude <= '$max_lat'
        AND places.latitude >= '$min_lat'
        AND places.longitude <= '$max_lon'
        AND places.longitude >= '$min_lon'
        AND places_visited.user_name = '$cur_user'
        AND places_visited.user_email = '$cur_user_email'
        AND places.id != places_visited.place_id
");
$get_places->execute();

The code always shows 0 results and throws no error. I've also made sure, that the places are not already in the places_visited table.

I've stared at this for so very long now, and I just can't figure out the error. Any help would be very appreciated!

Answer 1

Your query is doing inner joins. So, it can only return places that the user has visited. No way that it can return places that a user hasn't visited. Before proceeding further, here is a simple rule: Never use commas in the from clause. ANSI standard explicit JOIN syntax has been around for over two decades, and you should use it. In fact, in this case, you need it, because you need an outer join:

SELECT p.id, p.name, p.latitude, p.longitude
FROM places p INNER JOIN
     categorized_places cp
     ON p.id = cp.place_id LEFT JOIN
     places_visited pv
     ON pv.place_id = p.id AND
        pv.user_name = '$cur_user' AND
        pv.user_email = '$cur_user_email'          
WHERE cp.cat_id = '$cur_cat' AND
      p.latitude <= '$max_lat' AND
      p.latitude >= '$min_lat' AND
      p.longitude <= '$max_lon' AND
      p.longitude >= '$min_lon' AND
      pv.place_id IS NULL;

What this does is it matches the conditions to all the places visited, using an outer join. Then the condition pv.place_id IS NULL chooses the ones that have not been visited. Note that the conditions on the places_visited table go in the ON clause. The conditions on the other two tables remain in the WHERE clause. In general, when using LEFT OUTER JOIN, the filters on the first table stay in the WHERE clause. The filters on the second table go in the ON clause.

I also introduced table aliases. These help make queries easier to write and to read.