php - 使用来自另一个表的结果过滤 mysql 查询结果

Question

我需要帮助来向我已经工作的 sql 查询添加过滤器。我需要根据另一个表中的结果过滤结果。在不添加过滤器的情况下，代码可以正常工作，但是一旦添加，我就会收到以下错误“警告：mysqli_num_rows() 期望参数 1 为 mysqli_result，在第 10 行的 C:\xampp\htdocs\buff\marketplace.php 中给出的布尔值” .

这是我的查询代码：

$sql = "SELECT id, uid, jobId, serviceName, budget, description, start_date, end_date, date_created, priority, lat, lng, distance
                FROM (
                SELECT z.id,
                      z.uid,
                      z.jobId,
                      z.serviceName,
                      z.budget,
                      z.description,
                      z.start_date,
                      z.end_date,
                      z.date_created,
                      z.priority,
                      z.lat, z.lng,
                      p.radius,
                      p.distance_unit
                               * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                               * COS(RADIANS(z.lat))
                               * COS(RADIANS(p.longpoint - z.lng))
                               + SIN(RADIANS(p.latpoint))
                               * SIN(RADIANS(z.lat)))) AS distance
                FROM marketplace AS z
                JOIN (   /* these are the query parameters */
                      SELECT  $lat  AS latpoint,  $lng AS longpoint,
                              50.0 AS radius,      111.045 AS distance_unit
                  ) AS p

                WHERE marketplace.sid IN (SELECT sid FROM selectedservice WHERE uid=$uid AND is_approve=1) AND status=1 AND uid != $uid AND z.lat
                   BETWEEN p.latpoint  - (p.radius / p.distance_unit)
                       AND p.latpoint  + (p.radius / p.distance_unit)
                  AND z.lng
                   BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                       AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                ) AS d
                WHERE distance <= radius
                ORDER BY distance
                LIMIT 15";

这是我添加的用于过滤结果的行：

marketplace.sid IN (SELECT sid FROM selectedservice WHERE uid=$uid AND is_approve=1)

这是当前在没有过滤器的情况下工作的其余代码。

$sql = "SELECT id, uid, jobId, serviceName, budget, description, start_date, end_date, date_created, priority, lat, lng, distance
                FROM (
                SELECT z.id,
                      z.uid,
                      z.jobId,
                      z.serviceName,
                      z.budget,
                      z.description,
                      z.start_date,
                      z.end_date,
                      z.date_created,
                      z.priority,
                      z.lat, z.lng,
                      p.radius,
                      p.distance_unit
                               * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                               * COS(RADIANS(z.lat))
                               * COS(RADIANS(p.longpoint - z.lng))
                               + SIN(RADIANS(p.latpoint))
                               * SIN(RADIANS(z.lat)))) AS distance
                FROM marketplace AS z
                JOIN (   /* these are the query parameters */
                      SELECT  $lat  AS latpoint,  $lng AS longpoint,
                              50.0 AS radius,      111.045 AS distance_unit
                  ) AS p

                WHERE status=1 AND uid != $uid AND z.lat
                   BETWEEN p.latpoint  - (p.radius / p.distance_unit)
                       AND p.latpoint  + (p.radius / p.distance_unit)
                  AND z.lng
                   BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                       AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                ) AS d
                WHERE distance <= radius
                ORDER BY distance
                LIMIT 15";

selectedservice 和 marketplace 是具有 sid 和 uid 字段的表。$uid 是整个页面中可用的登录用户 ID。

我只需要知道如何挤入上述过滤器以显示用户启用的结果而不是所有结果。任何帮助将不胜感激。

标签： phpmysqldatabasejointabletablefilter