PHPで開発した中古リサイクルサイトにユーザーの閲覧ルート推奨機能を実装

CREATE DATABASE recycle_db;
USE recycle_db;
CREATE TABLE sites (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL
);
CREATE TABLE user_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    site_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (site_id) REFERENCES sites(id)
);

navigator.geolocation.getCurrentPosition(function(position) {
    const lat = position.coords.latitude;
    const lon = position.coords.longitude;
    // 将经纬度信息发送给后端
    $.post("recommend.php", {lat: lat, lon: lon}, function(data) {
        // 处理后端返回的推荐结果
        console.log(data);
    });
});

<?php
$lat = $_POST["lat"];
$lon = $_POST["lon"];
// 连接数据库
$conn = mysqli_connect("localhost", "username", "password", "recycle_db");
$query = "SELECT * FROM sites ORDER BY SQRT(POW(latitude-$lat,2)+POW(longitude-$lon,2))";
$result = mysqli_query($conn, $query);
// 处理查询结果
$data = [];
while ($row = mysqli_fetch_assoc($result)) {
    $data[] = $row;
}
// 返回结果
echo json_encode($data);
?>

$query = "SELECT site_id FROM user_history WHERE user_id=$user_id";
$result = mysqli_query($conn, $query);
$visited_sites = [];
while ($row = mysqli_fetch_assoc($result)) {
    $visited_sites[] = $row["site_id"];
}
// 根据站点访问次数进行排序
$query = "SELECT site_id, COUNT(*) AS visit_count FROM user_history WHERE site_id NOT IN (" . implode(",", $visited_sites) . ") GROUP BY site_id ORDER BY visit_count DESC";
$result = mysqli_query($conn, $query);
// 处理查询结果
$data = mysqli_fetch_assoc($result);
$next_site_id = $data["site_id"];