Link to home
Start Free TrialLog in
Avatar of Aimee Katherine
Aimee Katherine

asked on

How to use COUNT() among multiple joins in SQL query

Hi, I am trying to get the number count of results of this query by adding COUNT().  However, COUNT() in this query returns 0 even when there are results.  Is there something wrong with the way that it is written?  I just want the query to return the count.  Any help is so truly appreciated.  Thank you!

$data_query = mysqli_query($conn, "
SELECT ZIPCodes.zipcode, usersearch_answers.username,
       (3959 * acos(cos(radians(ZIPCodes.latitude)) *
       cos(radians(center.latitude)) *
       cos(radians(ZIPCodes.longitude ) -
       radians(center.longitude)) +
       sin(radians(ZIPCodes.latitude)) *
       sin(radians(center.latitude)))) AS distance, COUNT(*) as c
FROM
(
    (
        SELECT usersearch_answers.username,
            usersearch_answers.zipcode,
            ZIPCodes.latitude,
            ZIPCodes.longitude
        FROM
        (
            usersearch_answers JOIN ZIPCodes ON usersearch_answers.zipcode = ZIPCodes.zipcode
        )    
        WHERE (ZIPCodes.zipcode='33133')
    ) center, ZIPCodes
) INNER JOIN usersearch_answers ON ZIPCodes.zipcode = usersearch_answers.zipcode
WHERE (usersearch_answers.username <> '$userLoggedIn')
HAVING (distance < 5000)
ORDER BY distance");


I then use:
$row = mysqli_fetch_assoc($data_query);
echo $row['c'];
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aimee Katherine
Aimee Katherine

ASKER

Thank you!!!