Sawan Khandelwal Sawan Khandelwal - 1 month ago 4
MySQL Question

I want to store child nodes on xml file in mysql server

I am working on google maps api and i want to store places data in my sql server, i am getting data in xml file and from the below code i can store following data (name,place_id,visinity) but apart form it i want to store (lat,lng) as well..

$url ="https://maps.googleapis.com/maps/api/place/nearbysearch/xml?location=22.722822,75.887055&radius=2000&type=pharmacy&keyword=medical&key=*************************";
$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_URL, $url);

$data = curl_exec ($ch);
curl_close($ch);

$xml = simplexml_load_string($data); foreach ($xml -> result as $row) {

$name = $row -> name;
$place_id= $row -> place_id;
$vicinity= $row -> vicinity;



$sql = "INSERT INTO google_coordinate (name,address,place_id) VALUES ('$name','$vicinity','$place_id')";

$result = mysql_query($sql);
if(!$result){
echo 'error';
}else{
echo "success";
}
}

Answer

Simply follow the data structure like this

foreach ($xml->result as $row) {

    $name       = $row->name;
    $place_id   = $row->place_id;
    $vicinity   = $row->vicinity;
    $lat        = $row->geometry->location->lat;
    $lon        = $row->geometry->location->lon;

    $sql = "INSERT INTO google_coordinate 
                    (name,address,place_id,lat,lon) 
            VALUES ('$name','$vicinity','$place_id', '$lat', '$lon')";

    $result = mysql_query($sql);
    if(!$result){
        echo 'error';
    }else{
        echo "success";
    }
}