mysqli_num_rows returns 1 even when returned join result is empty

Question

Here's my query:

$query = 'SELECT b.author as author, b.review as review FROM location_final_similar a LEFT JOIN location_reviews b ON (a.uuid = b.uuid AND a.originalLocationID = b.originalLocationID) WHERE a.finalLocationID = "'.$locationID.'"';

As you can see above, my only concern is to get author and review (which is retrieved from table location_reviews).

When I tested with $locationID that has data inside location_reviews, mysqli_num_rows would return the right count.

But when I tested with $locationID that has no data inside location_reviews, mysqli_num_rows would still return 1, but with author & review null. So how can I avoid this? I need mysqli_num_rows to return 0 if there's no review for the given ID.

Answer 1

You have to use INNER JOIN (or maybe RIGHT JOIN), e.g.

SELECT b.author as author, b.review as review FROM location_final_similar a INNER JOIN location_reviews b ON (a.uuid = b.uuid AND a.originalLocationID = b.originalLocationID) WHERE a.finalLocationID = 1

see Difference between INNER and OUTER joins for a good explanation.