douxing1353
douxing1353
2014-12-07 16:04

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

  • php
  • mysql

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  | x@mail.com |    2     |
------|-----------|------------|----------|
2     |   user_2  | y@mail.com |    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!

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答