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.usernam e,
(3959 * acos(cos(radians(ZIPCodes. latitude)) *
cos(radians(center.latitud e)) *
cos(radians(ZIPCodes.longi tude ) -
radians(center.longitude)) +
sin(radians(ZIPCodes.latit ude)) *
sin(radians(center.latitud e)))) AS distance, COUNT(*) as c
FROM
(
(
SELECT usersearch_answers.usernam e,
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.userna me <> '$userLoggedIn')
HAVING (distance < 5000)
ORDER BY distance");
I then use:
$row = mysqli_fetch_assoc($data_q uery);
echo $row['c'];
$data_query = mysqli_query($conn, "
SELECT ZIPCodes.zipcode, usersearch_answers.usernam
(3959 * acos(cos(radians(ZIPCodes.
cos(radians(center.latitud
cos(radians(ZIPCodes.longi
radians(center.longitude))
sin(radians(ZIPCodes.latit
sin(radians(center.latitud
FROM
(
(
SELECT usersearch_answers.usernam
usersearch_answers.zipcode
ZIPCodes.latitude,
ZIPCodes.longitude
FROM
(
usersearch_answers JOIN ZIPCodes ON usersearch_answers.zipcode
)
WHERE (ZIPCodes.zipcode='33133')
) center, ZIPCodes
) INNER JOIN usersearch_answers ON ZIPCodes.zipcode = usersearch_answers.zipcode
WHERE (usersearch_answers.userna
HAVING (distance < 5000)
ORDER BY distance");
I then use:
$row = mysqli_fetch_assoc($data_q
echo $row['c'];
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER