php - MySQL 使用动态变量连接两个表

Question

我有两个函数，一个用于搜索(全文搜索)，返回最佳匹配，另一个用于查找距离当前纬度/经度最近的位置。

最终结果将是一个搜索功能，用于查找距离您所在位置最近的商店。

搜索:

SELECT *,MATCH(name) AGAINST('*McDonalds*' IN BOOLEAN MODE) AS `score_name_0`, MATCH(`city`)   
AGAINST('McDonalds*' IN BOOLEAN MODE) AS `score_city_0` FROM `stores` 

WHERE MATCH(name) AGAINST('*McDonalds*' IN BOOLEAN MODE) OR 
MATCH (`city`) AGAINST('McDonalds*' IN BOOLEAN MODE) 
ORDER BY  (`score_name_0` + `score_city_0`) DESC

查找最近的:

SELECT *, (3959 * acos(cos(radians('.$lat.')) * cos(radians(lat)) * cos(radians(lng) - 
radians('.$long.')) + sin(radians('.$lat.')) * sin(radians(lat)))) AS distance 

FROM pinpoints 
INNER JOIN stores WHERE stores.id=pinpoints.store  HAVING distance < 25 ORDER BY distance

我的尝试:

SELECT *,MATCH(name) AGAINST('*McDonalds*' IN BOOLEAN MODE) AS `score_name_0`, 
MATCH(`city`) AGAINST('McDonalds*' IN BOOLEAN MODE) AS `score_city_0` 

FROM `stores` 
JOIN pinpoints ON (3959 * acos(cos( radians(".$lat.")) * cos(radians(pinpoints.lat)) * cos(radians(pinpoints.lng) - 
radians(".$long.")) + sin( radians(".$lat.")) * sin(radians(pinpoints.lat)))) AS distance 

WHERE stores.id=pinpoints.id 
AND MATCH(name) AGAINST('*McDonalds*' IN BOOLEAN MODE) OR 
MATCH (`city`) AGAINST('McDonalds*' IN BOOLEAN MODE) 
HAVING distance < 25 ORDER BY distance, (`score_namn_0` + `score_stad_0`) DESC

被困了很长一段时间，似乎无法找到将它们结合起来的好方法，因此它会找到最接近的匹配项并相应地对其进行排序。

Answer 1

你的东西到处都是，我还没有测试过这个，但它应该可以工作......

SELECT *
    FROM (SELECT *, MATCH(name) AGAINST('*McDonalds*' IN BOOLEAN MODE) AS `score_name_0`, 
            MATCH(`city`) AGAINST('McDonalds*' IN BOOLEAN MODE) AS `score_city_0`,
        (3959 * acos(cos( radians(".$lat.")) * cos(radians(pinpoints.lat)) * cos(radians(pinpoints.lng) - radians(".$long.")) + sin( radians(".$lat.")) * sin(radians(pinpoints.lat)))) AS distance
        FROM `stores` 
            JOIN pinpoints ON stores.id=pinpoints.id
        WHERE stores.id=pinpoints.id 
            AND (MATCH(name) AGAINST('*McDonalds*' IN BOOLEAN MODE) OR 
            MATCH (`city`) AGAINST('McDonalds*' IN BOOLEAN MODE))
) AS tmp_tbl
WHERE distance < 25 
ORDER BY distance ASC, (`score_namn_0` + `score_stad_0`) DESC