MySQL query only returning one result

Question

I have a query that gets places based on the Haversine algorithm.

SELECT
 id, description, name, 
 lat, `long`, 
 ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( `long` ) - radians($long) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance 
FROM 
 places 
HAVING 
 distance < 10 
ORDER BY 
 distance 
LIMIT 0, 20;

Then I echo it out in a JSON array like this:

$location = mysql_fetch_assoc($getlocations);
return print_r(json_encode($location));

However, it only returns one row when there should be at least two. Anyone know why it might be doing this? Thanks!

Why are you using HAVING instead of WHERE and without GROUP BY. Is this query working?? — niktrs
– niktrs, Commented Jul 5, 2011 at 6:38
@niktrs Yeah, it works flawlessly except for the error I mentioned. — iamandrus
– iamandrus, Commented Jul 5, 2011 at 6:39
I think because mysql_fetch_assoc starts at the first pointer, to get the second or so, you need to iterate. — tradyblix
– tradyblix, Commented Jul 5, 2011 at 6:39
When you have more than 1 row result you must use loop to retrieve all result! — Hamid
– Hamid, Commented Jul 5, 2011 at 6:41

Hamid · Accepted Answer · 2011-07-05 06:42:51Z

2

while( $row = mysql_fetch_assoc($getlocations)){
    $location[] = $row;
}
return print_r(json_encode($location));

answered Jul 5, 2011 at 6:42

Hamid

1,7603 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chandresh M · Accepted Answer · 2011-07-05 06:39:42Z

-1

you need to use mysql_fetch_assoc() function in while loop for that may be.

i.e:
while($location = mysql_fetch_assoc($getlocations));

print_r($location);

thanks.

answered Jul 5, 2011 at 6:39

Chandresh M

3,4932 gold badges27 silver badges48 bronze badges

Collectives™ on Stack Overflow

MySQL query only returning one result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related