MySQL PHP Query returning duplicate results even using DISTINCT

Question

I'm having this problem where the php will return duplicate rows in the json echo, it works in the database itself, just the php is messing it up somewhere and I'm unsure where.

<?php   
    $con = mysql_connect("host","user","pass");
    if (!$con)
    {
        die('could not connect: ' . mysql_error());
    }
    mysql_select_db("db", $con);

    $sql = "SELECT DISTINCT INFO, ( 6364 * acos( cos( radians(55.952) ) * cos( radians( lat   ) ) * cos( radians( lng ) - radians(-3.187) ) + sin( radians(55.952) ) * sin( radians( lat ) ) ) ) AS distance FROM table HAVING distance <0.5 ORDER BY distance LIMIT 0 , 20";


    $result = mysql_query($sql,$con);
    if (!$result)
    {
        echo "db error\n";
        echo 'MySQL Error : ' . mysql_error();
    }
    while ($row = mysql_fetch_array($result)) 
    {
        $output[]=$row["INFO"]; //added ["INFO"] to protect my location and make the problem easier to see.
        echo (json_encode($output));
    }
?>

it returns:

["This is where I study"]["This is where I study","this is where I live"]["This is where I study","this is where I live","this is where I could swim"]

i.e. = [0][0,1][0,1,2] where I need it to return simply [0,1,2] for java manipulation.

Thank you in advance, I'm a newb to php/mysql so any comment it welcome.

You're echoing output as you build it up with each row returned in each iteration of the while loop... echo after finishing the while loop — Mark Baker
– Mark Baker, Commented Jan 21, 2014 at 0:03

Mike Purcell · Accepted Answer · 2014-01-21 00:08:14Z

2

Mark is correct, you need to do something like this:

while ($row = mysql_fetch_array($result)) 
{
    $infos[]=$row["INFO"]; //added ["INFO"] to protect my location and make the problem easier to see.        
}

foreach ($infos as $info) {
    echo (json_encode($info));
}

answered Jan 21, 2014 at 0:08

Mike Purcell

20k10 gold badges54 silver badges94 bronze badges

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

Collectives™ on Stack Overflow

MySQL PHP Query returning duplicate results even using DISTINCT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related