I need help with adding a filter to my already working sql query. I need to filter the result based on results in another table. Without adding the filter the code works fine, but once it's added I get the following error "Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, bool given in C:\xampp\htdocs\buff\marketplace.php on line 10".
Here's my query code:
$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";
Here is the line I added to filter the result:
marketplace.sid IN (SELECT sid FROM selectedservice WHERE uid=$uid AND is_approve=1)
And here is the rest of the code that's currently working without the filter.
$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 and marketplace are tables both having sid and uid fields. $uid is the logged in user's id that's available throughout the page.
I just need to know how to squeeze in the above filter to display results the user is enabled for rather than all results. Any help will be greatly appreciated.
