使用 INNER JOIN 连接三个表并计算距离

SELECT jobs.*, clients.clientPostcode, traders.traderPostcode
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;

<?php
$stmt = $pdo->prepare("SELECT jobs.*, clients.clientPostcode, traders.traderPostcode 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)){
    $origin      = $row['traderPostcode'];
    $destination = $row['clientPostcode'];

    // 使用 Google Distance Matrix API 或其他服务计算距离
    $distance = calculateDistance($origin, $destination);

    // 根据距离判断是否显示
    if ($distance < $maxDistance) {
        ?>
        <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>
                <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
    }
}

// 示例距离计算函数 (需要实现)
function calculateDistance($origin, $destination) {
    // 使用 Google Distance Matrix API 或其他服务计算距离
    // 返回距离，单位为公里或其他单位
    // 注意：需要处理 API 密钥、请求限制等问题
    // 例如，使用 cURL:
    // $url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=".urlencode($origin)."&destinations=".urlencode($destination)."&key=YOUR_API_KEY";
    // ...
    return 0; // 占位符，需要替换为实际计算结果
}
?>