3

I'm using Google Maps API and Google Geocoding for geocoding addresses. I have table named 'users' and column named 'location' in MySQL database. Column 'location' contains addresses. I need to connect to the database, then geocode those addresses and add them like markers to the map.

First I connected to the database with PHP:

  <?php

    // connection to mysql

    $username="someusername";
    $password="*****";
    $database="GISwork";
    $hostname = "somehostname.com";
    $table = "users";


    $conn = mysql_connect($hostname,$username,$password) or die("Connecting to MySQL failed");

    mysql_select_db($database, $conn)
    or die("Selecting MySQL database failed");

    if (!mysql_select_db($database))
    die("Can't select database");

    // sending query
    $result = mysql_query("SELECT location FROM {$table}");
    if (!$result) {
        die("Query to show fields from table failed");
    }
    ?>

Then I defined geocoding and markers:

var userLocation = //it should be that $result from database 

        var geocoder = new google.maps.Geocoder();
            geocoder.geocode( { 'address': userLocation}, function(results, status){
               if (status == google.maps.GeocoderStatus.OK) {
                  map.setCenter(results[0].geometry.location);
                  var marker = new google.maps.Marker({map: map, position: results[0].geometry.location });
               } else {
                  alert("Geocode was not successful for the following reason: " + status);
               }
            });


    }
    google.maps.event.addDomListener(window, 'load', initialize);

My question is: a) How to pass php variable $result to the javascript variable userLocation to be readable for Google Geocoding? b) Is that function for geocoding and adding markers enough for all markers or should it be different regards to I need to import all addresses from the database (multiple markers)? c) How to add info windows to all markers?

Thank you.

  • i'm a beginner, please reply me, how to get the map location by using address . can you please send me the code. – user39403 Oct 19 '14 at 10:20
  • Welcome to gis.stackexchange. This is not a code writing service, you are expected to show some research effort first, and post a question when you run into difficulties. – John Powell Oct 19 '14 at 10:35
  • @JohnBarça I disagree that there is no research effort on this question. I think the issue here is that the post is not focused enough and would benefit by reducing the number of questions into a single answerable question. – Aaron Oct 20 '14 at 14:09
  • 1
    @Aaron. Sorry, I completely messed up there. That was intended for the person who wrote the "please send me the code comment". Apologies to OP. – John Powell Oct 20 '14 at 14:18
  • did you find any solution of this??? – Divyesh Jesadiya May 18 '17 at 14:50
1

The Official Google Maps API v3 has a good tutorial on this.

Note: Some tutorials may suggest actually writing your map page as a PHP file and outputting JavaScript for each marker you want to create, but that technique can be problematic. By using an XML file as an intermediary between your database and your Google Map, it makes for a faster initial page load, a more flexible map application, and easier debugging. You can independently verify the XML output from the database and the JavaScript parsing of the XML. And at any point, you could even decide to eliminate your database entirely and just run the map based on static XML files.

https://developers.google.com/maps/articles/phpsqlajax_v3

Careful of Geocoding on the Server Side - The 2,500 request limit is per IP address https://developers.google.com/maps/articles/geocodestrat#server

-4

Java Script Code

<script type="text/javascript">
                                    //<![CDATA[
                                    var customIcons = {
                                        PAS: {
                                            icon: 'http://www.alldayrentacar.com/wp-content/uploads/2015/09/icoCountPassengerL.png',
                                            shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
                                        },
                                        CAB: {
                                            icon: 'https://cdn0.iconfinder.com/data/icons/park-pedestrian/48/Ryan-24-512.png',
                                            shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
                                        }
                                    };

                                    function load() {
                                        var map = new google.maps.Map(document.getElementById("map"), {
                                            center: new google.maps.LatLng(6.9536887, 79.8600295),
                                            zoom: 13,
                                            mapTypeId: 'roadmap'
                                        });
                                        var infoWindow = new google.maps.InfoWindow;
                                        // Change this depending on the name of your PHP file
                                        downloadUrl("street_lamp_data.php", function (data) {
                                            var xml = data.responseXML;
                                            var markers = xml.documentElement.getElementsByTagName("marker");
                                            for (var i = 0; i < markers.length; i++) {
                                                var id = markers[i].getAttribute("id");
                                                var street = markers[i].getAttribute("streetname");
                                                var lamp = markers[i].getAttribute("lamptype");

                                                var point = new google.maps.LatLng(
                                                        parseFloat(markers[i].getAttribute("lat")),
                                                        parseFloat(markers[i].getAttribute("lng")));
                                                var type = "PAS"
                                                var html = "";

                                                var icon = customIcons[type] || {};
                                                var marker = new google.maps.Marker({
                                                    id: id,
                                                    type: type,
                                                    map: map,
                                                    position: point,
                                                    icon: icon.icon,
                                                    shadow: icon.shadow
                                                });
                                                bindInfoWindow(marker, map, infoWindow, html, id);
                                            }
                                            document.getElementById('la_id').value = "";
                                            document.getElementById('addressTxt').value = "";
                                            document.getElementById('laTypeCombo').value = "";


                                        });






                                        xmlHttp = GetXmlHttpObject();
                                        if (xmlHttp == null) {
                                            alert("Browser does not support HTTP Request");
                                            return;
                                        }

                                        if (document.getElementById('la_idTxt').value == "") {
                                            document.getElementById('msg_box').innerHTML = "<div class='alert alert-warning' role='alert'><span class='center-block'>Code Not Enterd</span></div>";
                                            return false;
                                        }
                                        if (document.getElementById('addressTxt').value == "") {
                                            document.getElementById('msg_box').innerHTML = "<div class='alert alert-warning' role='alert'><span class='center-block'>Category Not Selected</span></div>";
                                            return false;
                                        }
                                        if (document.getElementById('laTypeCombo').value == "") {
                                            document.getElementById('msg_box').innerHTML = "<div class='alert alert-warning' role='alert'><span class='center-block'>Description Not Enterd</span></div>";
                                            return false;
                                        }
                                        if (document.getElementById('LatitudeTxt').value == "") {
                                            document.getElementById('msg_box').innerHTML = "<div class='alert alert-warning' role='alert'><span class='center-block'>Description Not Enterd</span></div>";
                                            return false;
                                        }
                                        if (document.getElementById('LongititudeTxt').value == "") {
                                            document.getElementById('msg_box').innerHTML = "<div class='alert alert-warning' role='alert'><span class='center-block'>Description Not Enterd</span></div>";
                                            return false;
                                        }



                                        var url = "stl_data.php";
                                        url = url + "?Command=" + "save_item";




                                        url = url + "&id=" + document.getElementById('la_idTxt').value;
                                        url = url + "&streetname=" + document.getElementById('addressTxt').value;
                                        url = url + "&lamptype=" + document.getElementById('laTypeCombo').value;

                                        url = url + "&latitude=" + document.getElementById('LatitudeTxt').value;
                                        url = url + "&longitude=" + document.getElementById('LongititudeTxt').value;


                                        xmlHttp.onreadystatechange = salessaveresult;
                                        xmlHttp.open("GET", url, true);
                                        xmlHttp.send(null);
                                    }

                                    function salessaveresult() {
                                        var XMLAddress1;

                                        if (xmlHttp.readyState == 4 || xmlHttp.readyState == "complete") {

                                            if (xmlHttp.responseText == "Saved") {
                                                document.getElementById('msg_box').innerHTML = "<div class='alert alert-success' role='alert'><span class='center-block'>Saved</span></div>";
                                                newent();
                                            } else {
                                                document.getElementById('msg_box').innerHTML = "<div class='alert alert-warning' role='alert'><span class='center-block'>" + xmlHttp.responseText + "</span></div>";
                                            }
                                        }
                                    }

                                    function bindInfoWindow(marker, map, infoWindow, html, id) {
                                        google.maps.event.addListener(marker, 'click', function () {
                                            infoWindow.setContent(html);
                                            infoWindow.open(map, marker);
                                            getdata(this.id, this.type);
                                        });
                                    }
                                    function downloadUrl(url, callback) {
                                        var request = window.ActiveXObject ?
                                                new ActiveXObject('Microsoft.XMLHTTP') :
                                                new XMLHttpRequest;
                                        request.onreadystatechange = function () {
                                            if (request.readyState == 4) {
                                                request.onreadystatechange = doNothing;
                                                callback(request, request.status);
                                            }
                                        };
                                        request.open('GET', url, true);
                                        request.send(null);
                                    }
                                    function GetXmlHttpObject() {
                                        var xmlHttp = null;
                                        try {
                                            // Firefox, Opera 8.0+, Safari
                                            xmlHttp = new XMLHttpRequest();
                                        } catch (e) {
                                            // Internet Explorer
                                            try {
                                                xmlHttp = new ActiveXObject("Msxml2.XMLHTTP");
                                            } catch (e) {
                                                xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
                                            }
                                        }
                                        return xmlHttp;
                                    }

                                    function getdata(cdata, cdata1) {

                                        xmlHttp = GetXmlHttpObject();
                                        if (xmlHttp == null) {
                                            alert("Browser does not support HTTP Request");
                                            return;
                                        }

                                        var url = "view_pend_data.php";
                                        url = url + "?Command=" + "get_data";
                                        url = url + "&id=" + cdata;
                                        url = url + "&ptype=" + cdata1;


                                        xmlHttp.onreadystatechange = assign_dt;
                                        xmlHttp.open("GET", url, true);
                                        xmlHttp.send(null);

                                    }




                                    function assign_dt() {

                                        var XMLAddress1;

                                        if (xmlHttp.readyState == 4 || xmlHttp.readyState == "complete") {

                                            XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("ptype");
                                            if (XMLAddress1[0].childNodes[0].nodeValue == "CAB") {

                                                document.getElementById('txt_did').value = "";
                                                document.getElementById('fname').value = "";
                                                document.getElementById('lname').value = "";
                                                document.getElementById('mob_no').value = "";


                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("did");
                                                document.getElementById('txt_did').value = XMLAddress1[0].childNodes[0].nodeValue;

                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("dfName");
                                                document.getElementById('fname').value = XMLAddress1[0].childNodes[0].nodeValue;

                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("dlName");
                                                document.getElementById('lname').value = XMLAddress1[0].childNodes[0].nodeValue;

                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("dMobileNo");
                                                document.getElementById('mob_no').value = XMLAddress1[0].childNodes[0].nodeValue;



                                            } else {

                                                document.getElementById('txt_hireid').value = "";
                                                document.getElementById('txthtime').value = "";
                                                document.getElementById('fname1').value = "";
                                                document.getElementById('lname1').value = "";
                                                document.getElementById('mob_no1').value = "";



                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("hireID");
                                                document.getElementById('txt_hireid').value = XMLAddress1[0].childNodes[0].nodeValue;

                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("hireT");
                                                document.getElementById('txthtime').value = XMLAddress1[0].childNodes[0].nodeValue;

                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("fname");
                                                document.getElementById('fname1').value = XMLAddress1[0].childNodes[0].nodeValue;

                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("lname");
                                                document.getElementById('lname1').value = XMLAddress1[0].childNodes[0].nodeValue;

                                                XMLAddress1 = xmlHttp.responseXML.getElementsByTagName("contact");
                                                document.getElementById('mob_no1').value = XMLAddress1[0].childNodes[0].nodeValue;


                                            }





                                        }

                                    }

                                    function doNothing() {

                                    }


                                    //]]>
        </script>




Data File



<?php

require("./connection_sql.php");


header("Content-type: text/xml");

function parseToXML($htmlStr) {
    $xmlStr = str_replace('<', '&lt;', $htmlStr);
    $xmlStr = str_replace('>', '&gt;', $xmlStr);
    $xmlStr = str_replace('"', '&quot;', $xmlStr);
    $xmlStr = str_replace("'", '&#39;', $xmlStr);
    $xmlStr = str_replace("&", '&amp;', $xmlStr);
    return $xmlStr;
}

// Select all the rows in the markers table
$sql = "SELECT * FROM as_stLamp ";


header("Content-type: text/xml");
echo '<markers>';

// Iterate through the rows, adding XML nodes for each
foreach ($conn->query($sql) as $row) {
    // ADD TO XML DOCUMENT NODE

    echo '<marker ';
    echo 'id="' . parseToXML($row['lId']) . '" ';
    echo 'streetname="' . parseToXML($row['lstname']) . '" ';

    echo 'lamptype="' . $row['ltype'] . '" '; 
    echo 'lat="' . $row['llatitude'] . '" ';
    echo 'lng="' . $row['lLongitude'] . '" ';
    echo 'type="CAB"';
    echo '/>';
}


// End XML file
echo '</markers>';




?>

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • 3
    what is this ... ? – pLumo Aug 25 '17 at 9:31
  • Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. A good answer should include an explanation about how and where to use your suggested solution, or info on how it answers the question. – Midavalo Aug 25 '17 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.