使用INNER JOIN关联3张表查询匹配工种及指定距离内工作的问题

修正原SQL语法错误 #

你现有查询的WHERE子句位置错误，WHERE必须放在所有JOIN操作之后，先修正基础查询结构：

// 修正后的基础查询（仅工种匹配）
$stmt = $pdo->prepare("
    SELECT jobs.*, clients.clientPostcode, traders.traderPostcode,
           clients.lat as client_lat, clients.lng as client_lng,
           traders.lat as trader_lat, traders.lng as trader_lng
    FROM jobs 
    INNER JOIN traders ON FIND_IN_SET(jobs.tradeType, traders.tradeTypes)
    INNER JOIN clients ON jobs.clientEmail = clients.clientEmail
    WHERE traders.traderEmail = :traderEmail
");
$stmt->bindParam(':traderEmail', $traderEmail);
$stmt->execute();

距离筛选实现 #

前提：你需要提前将邮编转换为经纬度，存储到clients和traders表中（新增lat、lng字段即可，邮编更新时同步更新对应经纬度），仅邮编无法直接计算地理距离

方案1：SQL层面直接过滤（推荐，性能更高） #

使用Haversine公式直接在查询时计算距离并筛选，无需在PHP层循环过滤：

$maxDistance = 20; // 自定义最大距离，单位：公里
$stmt = $pdo->prepare("
    SELECT jobs.*, 
        (6371 * acos(cos(radians(:trader_lat)) * cos(radians(clients.lat)) 
        * cos(radians(clients.lng) - radians(:trader_lng)) 
        + sin(radians(:trader_lat)) * sin(radians(clients.lat)))) AS distance
    FROM jobs 
    INNER JOIN traders ON FIND_IN_SET(jobs.tradeType, traders.tradeTypes)
    INNER JOIN clients ON jobs.clientEmail = clients.clientEmail
    WHERE traders.traderEmail = :traderEmail
    HAVING distance <= :maxDistance
");
// 先查询当前交易员的经纬度
$traderStmt = $pdo->prepare("SELECT lat, lng FROM traders WHERE traderEmail = ?");
$traderStmt->execute([$traderEmail]);
$traderInfo = $traderStmt->fetch();
// 绑定参数
$stmt->bindParam(':traderEmail', $traderEmail);
$stmt->bindParam(':trader_lat', $traderInfo['lat']);
$stmt->bindParam(':trader_lng', $traderInfo['lng']);
$stmt->bindParam(':maxDistance', $maxDistance);
$stmt->execute();

// 直接循环输出符合条件的结果即可
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
?>
<div class="card col-lg-12 mt-5 text-center">
    <div class="card-body">
        <h6 class="card-title text-primary">Job Type: <?php echo $row['tradeType'] ?> (Job Title: <?php echo $row['jobTitle'] ?> ) </h6>
        <p class="card-text"><?php echo $row['jobDescription'] ?></p>
        <p>距离：<?php echo round($row['distance'],2) ?>公里</p>
        <a class="btn btn-primary" href="">
        <i class="fas fa-edit fa-xs"></i> Send Interest</a>
        <a class="btn btn-success" href="" target="_blank">
        <i class="fas fa-glasses fa-xs"></i> Shortlist</a>
    </div>
</div>
<?php
}

方案2：PHP层面过滤（适合数据量较小的场景） #

如果你不想修改SQL结构，也可以在循环中通过PHP计算距离后过滤：

// 定义距离计算函数
function calculateDistance($lat1, $lng1, $lat2, $lng2, $unit = 'km') {
    $earthRadius = ($unit == 'km') ? 6371 : 3959; // 公里/英里
    $dLat = deg2rad($lat2 - $lat1);
    $dLng = deg2rad($lng2 - $lng1);
    $a = sin($dLat/2) * sin($dLat/2) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin($dLng/2) * sin($dLng/2);
    $c = 2 * atan2(sqrt($a), sqrt(1-$a));
    return $earthRadius * $c;
}

$maxDistance = 20; // 最大允许距离
$stmt = $pdo->prepare("
    SELECT jobs.*, clients.lat as client_lat, clients.lng as client_lng, traders.lat as trader_lat, traders.lng as trader_lng
    FROM jobs 
    INNER JOIN traders ON FIND_IN_SET(jobs.tradeType, traders.tradeTypes)
    INNER JOIN clients ON jobs.clientEmail = clients.clientEmail
    WHERE traders.traderEmail = :traderEmail
");
$stmt->bindParam(':traderEmail', $traderEmail);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $distance = calculateDistance($row['trader_lat'], $row['trader_lng'], $row['client_lat'], $row['client_lng']);
    if($distance <= $maxDistance){
        // 输出卡片代码和之前一致
    }
}

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