广告门户GEO邮编搜索异常求助：瑞士/奥地利区域结果偏差

核心问题分析 #

从现象和代码来看，瑞士/奥地利邮编搜索返回德国广告的根本原因是geo表查询未匹配到正确的目标国家邮编记录，或坐标计算逻辑存在偏差，具体可能的点如下：

1. 重复邮编的国家冲突 #

瑞士（6000卢塞恩）和奥地利（6000布雷根茨）存在相同邮编，当前代码未区分国家，fetch()仅取查询结果的第一条记录。若geo表中德国存在错误录入的4位邮编（如6000），或奥地利的6000记录排在瑞士之前，会导致后续距离计算基于错误的坐标执行，最终返回德国/奥地利周边的广告。

2. geo表数据缺失或格式错误 #

若瑞士邮编6000未录入geo表，或存储格式带国家前缀（如CH-6000），代码提取的6000无法匹配到正确记录，$row为空时后续计算会出现未定义行为，可能误匹配德国的邮编。

3. 未处理查询无结果的边界情况 #

代码未对$row = $get->fetch()的空结果做处理，若未找到对应邮编，$lat/$lon会为null，导致距离计算逻辑失效，可能返回大量无关结果。

分步解决方案 #

1. 验证geo表数据正确性 #

执行以下SQL检查邮编6000的记录：

SELECT geo_plz, geo_bg AS lat, geo_lg AS lon
FROM geo 
WHERE geo_plz = '6000';

• 若返回德国的错误记录：删除或修正该记录
• 若无瑞士的6000记录：补充该邮编的正确坐标（卢塞恩：lat=47.05017, lon=8.30931），或修正存储格式（如去掉CH-前缀）

2. 添加国家区分逻辑 #

在geo表中新增country_code字段（存储DE/CH/AT），并修改查询逻辑：

// 假设通过用户选择或IP识别到国家代码$country_code
$stmt = $this->db->db->prepare("
    SELECT geo_lg AS lon, geo_plz, geo_bg AS lat 
    FROM geo 
    WHERE ".$this->dbValue." = ? AND country_code = ?
");
$stmt->execute([$this->search_code, $country_code]);
$row = $stmt->fetch();

3. 处理查询无结果的情况 #

添加空结果判断，避免后续逻辑出错：

$row = $stmt->fetch();
if (!$row) {
    // 返回无匹配的条件，避免返回无关广告
    return "AND 1=0";
}

4. 优化正则匹配精度 #

替换瑞士/奥地利的正则，避免误匹配：

// Switzerland Postcode (1000-9699)
preg_match('/\b(1[0-9]{3}|[2-8][0-9]{3}|9[0-6][0-9]{2})\b/is', $this->search_code, $ch);
// Austria Postcode (1000-9999)
preg_match('/\b([1-9][0-9]{3})\b/is', $this->search_code, $at);

if (!empty($ch[0])) {
    $this->search_code = $ch[0];
} elseif (!empty($at[0])) {
    $this->search_code = $at[0];
}

5. 修复SQL注入风险 #

将所有直接拼接的SQL改为预处理语句：

// 距离查询改为预处理
$stmt = $this->db->db->prepare("
    SELECT geo_plz AS plz, 
           ( ? * SQRT(2*(1-cos(RADIANS(geo_bg)) * cos(?) * 
           (sin(RADIANS(geo_lg)) * sin(?) + cos(RADIANS(geo_lg)) * cos(?)) 
           - sin(RADIANS(geo_bg)) * sin(?)))) AS Distance
    FROM geo 
    WHERE ? * SQRT(2*(1-cos(RADIANS(geo_bg)) * cos(?) * 
           (sin(RADIANS(geo_lg)) * sin(?) + cos(RADIANS(geo_lg)) * cos(?)) 
           - sin(RADIANS(geo_bg)) * sin(?))) <= ?
    ORDER BY Distance
");
$stmt->execute([
    $this->earthRadius, $lat, $lon, $lon, $lat,
    $this->earthRadius, $lat, $lon, $lon, $lat,
    $this->search_radius
]);
$rowString = $stmt->fetchAll();

内容的提问来源于stack exchange，提问作者Vlado Pranjic