多表联接与外部API集成：基于交易类型和距离筛选任务的SQL与PHP实践

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
// 假设 $pdo 已经是一个有效的 PDO 实例
// 假设 $traderEmail 已经定义并包含要查询的交易员邮箱

$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)) {
//     // ... 后续距离计算和筛选逻辑
// }
?>

<?php
// ... (PDO 连接和 SQL 查询部分同上) ...

// 假设 $traderEmail 已经定义
$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();

// 设定最大允许距离（例如，50公里）
$maxDistanceKm = 50; 

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $originPostcode = $row['traderPostcode'];
    $destinationPostcode = $row['clientPostcode'];

    // --- 在这里集成 Google Distance Matrix API 调用 ---
    // 这是一个概念性的函数调用，你需要根据Google API文档实现它
    // function getDistance(origin, destination, apiKey) { ... }
    // 假设 getDistance 返回距离（单位：米）或 false/null 如果失败
    $distanceMeters = callGoogleDistanceMatrixApi($originPostcode, $destinationPostcode, YOUR_GOOGLE_API_KEY); 

    // 将米转换为公里
    $distanceKm = $distanceMeters !== null ? $distanceMeters / 1000 : null;

    // 检查距离是否在允许范围内，并且API调用成功
    if ($distanceKm !== null && $distanceKm <= $maxDistanceKm) {
        // 如果条件满足，则显示任务卡片
        ?>
        <div class="card col-lg-12 mt-5 text-center">
            <div class="card-body">
                <h6 class="card-title text-primary">Job Type: <?php echo htmlspecialchars($row['tradeType']); ?> (Job Title: <?php echo htmlspecialchars($row['jobTitle']); ?>) </h6>
                <p class="card-text"><?php echo htmlspecialchars($row['jobDescription']); ?></p>
                <p class="card-text">Distance: <?php echo round($distanceKm, 2); ?> km</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
    }
}

/**
 * 这是一个占位函数，实际应用中需要实现对 Google Distance Matrix API 的 cURL 请求
 * 
 * @param string $origin 起点邮政编码
 * @param string $destination 终点邮政编码
 * @param string $apiKey 您的 Google API Key
 * @return float|null 距离（米），如果失败则返回 null
 */
function callGoogleDistanceMatrixApi(string $origin, string $destination, string $apiKey): ?float {
    // 实际的 API 请求逻辑
    // 示例 URL: https://maps.googleapis.com/maps/api/distancematrix/json?origins=ORIGIN&destinations=DESTINATION&key=YOUR_API_KEY
    $url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" . urlencode($origin) . "&destinations=" . urlencode($destination) . "&key=" . $apiKey;

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    $response = curl_exec($ch);
    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($http_code === 200 && $response) {
        $data = json_decode($response, true);
        if (isset($data['rows'][0]['elements'][0]['distance']['value'])) {
            return (float)$data['rows'][0]['elements'][0]['distance']['value']; // 返回距离（米）
        }
    }
    // 错误处理或 API 调用失败
    error_log("Google Distance Matrix API call failed for {$origin} to {$destination}. Response: " . $response);
    return null;
}
?>