loop through mysql result and apply function to each row before parsing as json

Question

I have a php Mysql query which returns a recordset containing id, Name, Type, Latitude, Longitude, Distance

I have searched for a solution to encode the results as json but all the examples show the array being treated as a whole.

$json = array(); 
if(mysql_num_rows($result)){ 
    while($row=mysql_fetch_row($result)){ 
        $json['markers'][]=$row; 
    } 
} 

echo json_encode($json);

Which works fine and produces the following json:

{"markers":[
["2110","AP","Nans Sous Sainte Anne","46.976810","5.998910","1.60316506124051","0","0","0"],
["3484","AC","Salins Les Bains","46.946568","5.878649","6.8092722205639","0","0","0"],     ["2136","AC","Levier","46.959862","6.132840","6.8490368219444","0","1","0"],
["3472","APN","Salins Les Bains","46.936852","5.876290","7.28462233818928","0","0","0"],["3466","ASN","Salins Les Bains","46.932541","5.878990","7.36798013180542","0","2","0"],["2158","FP","Domaine d'Esprits","47.035751","5.824800","8.6152043630634","0","0","0"]]}

But what I want to do is loop through each row and use the Latitude and Longitude in a function to get the bearing using the following function; $center_lat and $center_lng are passed in the url as the search center.

$bearing=getCompassDirection(getBearing($center_lat, $center_lng, $row['Latitude'], $row['Longitude']));

The function 'getCompassDirection' is valid and works, but how do I loop through my_sql result and apply the function to each row?

u_mulder · Accepted Answer · 2013-07-24 17:55:32Z

1

Okay, here's your code:

$json = array(); 
if(mysql_num_rows($result)){ 
    while($row=mysql_fetch_row($result)){ 
        $json['markers'][]=$row; 
    } 
}

Every $row is numeric indexed array. As far as I can see from your current data lattitude is item with index 3, and longtitude is item with index 4 (or vice versa). So, you can change your code:

$json = array(); 
if(mysql_num_rows($result)){ 
    while($row=mysql_fetch_row($result)){ 
        $row[] = getCompassDirection(getBearing($center_lat, $center_lng, $row[3], $row[4]));
        $json['markers'][]=$row; 
    } 
}

Or you can use mysql_fetch_assoc instead. In this case your result json will have different structure.

$json = array(); 
if(mysql_num_rows($result)){ 
    while($row=mysql_fetch_assoc($result)){ 
        // check out keys names for lattitude and longtitude first
        $row['bearing'] = getCompassDirection(getBearing($center_lat, $center_lng, $row['lattitude'], $row['longtitude']));  
        $json['markers'][]=$row; 
    } 
}

And in the end - stop using mysql_ functions, as they're out of date and deprecated.

edited Jul 24, 2013 at 17:55

answered Jul 24, 2013 at 17:50

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

1 Comment

lifeson Over a year ago

Thanks that has worked great. I appreciate the mysql functions are out of date but I am on a steep learning curve and started with a google map demo that suggested using myql php and Xml and have just found out that json is preffered over xml so am just learning json. Its hard to find a tutorial that is current and explains all the tech in one place.

Meeran · Accepted Answer · 2013-07-24 17:45:36Z

1

you can use this

    $json = array(); 
if(mysql_num_rows($result)){ 
    while($row=mysql_fetch_row($result)){ 
       $row['bearing']=getCompassDirection(getBearing($center_lat, $center_lng, $row['Latitude'], $row['Longitude']));
        $json['markers'][]=$row; 
    } 
} 

echo json_encode($json);

answered Jul 24, 2013 at 17:45

Meeran

772 bronze badges

Collectives™ on Stack Overflow

loop through mysql result and apply function to each row before parsing as json

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related