Take data (points) from MySQL to Leaflet Map using PHP and Ajax

Question

I have records with lat and lngs in a MySQL database and I want to add them to a Leaflet map as markers. I have tried a couple of methods but none seem to work. When the webpage opens the map doesn't show up and I get "ReferenceError: Incidents is not defined" in the console.

Here is my php file (get.php)

<?php
$username = 'aname'; 
$password = 'apassword; 
$host = 'localhost'; 
$dbname = 'adb';

// open the database
try {
    $db = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
} catch(PDOException $e) {
    // send the PDOException message
    $ajxres=array();
    $ajxres['resp']=40;
    $ajxres['dberror']=$e->getCode();
    $ajxres['msg']=$e->getMessage();
    sendajax($ajxres);
}

try {
    $sql="SELECT protocol,jurisdiction,date,time,comments,video,lat,lng FROM Incidents";
    $stmt = $db->prepare($sql);
    $stmt->execute();
} catch(PDOException $e) {
    print "db error ".$e->getCode()." ".$e->getMessage();
}

$ajxres=array(); // place to store the geojson result
$features=array(); // array to build up the feature collection
$ajxres['type']='FeatureCollection';

// go through the list adding each one to the array to be returned   
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $lat=$row['lat'];
    $lng=$row['lng'];
    $prop=array();
    $prop['protocol']=$row['protocol'];
    $prop['jurisdiction']=$row['jurisdiction'];
    $prop['date']=$row['date'];
    $prop['time']=$row['time'];
    $prop['comments']=$row['comments'];
    $prop['video']=$row['video'];
    $f=array();
    $geom=array();
    $coords=array();

    $geom['type']='Point';
    $coords[0]=floatval($lng);
    $coords[1]=floatval($lat);

    $geom['coordinates']=$coords;
    $f['type']='Feature';
    $f['geometry']=$geom;
    $f['properties']=$prop;

    $features[]=$f;

}

// add the features array to the end of the ajxres array
$ajxres['features']=$features;
// tidy up the DB
$db = null;
sendajax($ajxres); // no return from there

function sendajax($ajx) {
    // encode the ajx array as json and return it.
    $encoded = json_encode($ajx);
    exit($encoded);
}
?>

EDIT: Here is my JavaScript (pd_map.js):

var map;
var centerlatlng = L.latLng(35.85199,-119.6577);

var southWest = L.latLng(3.3,-154.2),
northEast = L.latLng(61.5,-58.4),
bounds = L.latLngBounds(southWest, northEast);



var myIcon1 = L.icon({
iconUrl: 'http://www.broomfield.org/images/pages    /N315/blue%20heading%20icons_building.png',
iconSize: [30, 30]
});


var red = L.icon({
iconUrl: 'img/red.png',
iconSize: [30, 30]
});


$(document).ready(function (){


// Creating a tile layer using ESRI - World Physical Map
var aLayerOne = L.tileLayer('http://services.arcgisonline.com/arcgis /rest/services/World_Imagery/MapServer/tile/{z}/{y}/{x}.png', {
                maxZoom: 19

            });

// Creating a tile layer using MapBox
var aLayerTwo = L.tileLayer('http://api.tiles.mapbox.com /v4/davidjbailey.jg612ji1/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoiZGF2aWRqYmFpbGV5IiwiYSI6InFxSzA5bjgifQ.tDw01mG43kf6hWUIAtBEAw#4/33.75/-117.87', {
                attribution: 'Developed <a href="">DB</a>',
                maxZoom: 18
            });

// Creating a geojson layer using geojson file
// You can set the style using Leaflet Path Class - http://leafletjs.com/reference.html#path


// Cities
var aLayerThree = L.geoJson(aGeoJson1,  {
                    pointToLayer: function (feature, latlng) {
                        return L.marker(latlng,{icon: myIcon1});
                    }, // end of point to layer{

                    onEachFeature: function(feature, layer){

                        layer.on('load', function(e){
                            marker.openPopup();
                        }); //  event functio                    

                        layer.on('click', function(e){
                            map.setView([e.latlng.lat, e.latlng.lng], 11);
                            map.removeLayer(aLayerThree)
                            aDiv.innerHTML = "<center>" + "<strong>Welcome to </strong>" + "<strong>" + feature.properties.name + "!" + "</strong>" + "<br>" + "<h5>" + "Explore the data by zooming in" + "<br>" +
                            "and out and by clicking on points." + "</h5>" + "------------"  + "<h5>" + "Add an incident to the map by clicking on the" +
                            "<br>" + "marker symbol in the upper left corner of the map." + "</h5>" + "</center>"
                        }); //  event function

                        layer.on('mouseover', function(e){                          
                            aDiv.innerHTML = "<strong>" + feature.properties.name + "</strong>"

                            }); // End of mouseover event function
                        layer.on('mouseout', function(e){                           
                            aDiv.innerHTML = "<strong>Click on your city!</strong>"   


                    });   


                }});



//Create our Map Object
            map = L.map('myMap', {
                        center: centerlatlng,
                        zoom:   6,
                        maxBounds: bounds,
                        minZoom: 4,
                        maxZoom: 18,

                        layers: [aLayerThree, aLayerTwo]
    });

UserMarker = L.featureGroup().addTo(map);
map.addLayer(UserMarker);

L.drawLocal.draw.toolbar.buttons.marker = 'Add an incident to the map!';


var drawControl = new L.Control.Draw({
draw: {
polygon: false,
polyline: false,
rectangle: false,
marker:{
                icon: red 
            },
circle: false,
},

edit: {
featureGroup: UserMarker
}
});
map.addControl(drawControl);

map.on('draw:created', function (e) {
 var type = e.layerType,
    layer = e.layer;
    var coords = e.layer._latlng;
      console.log(coords);
 UserMarker.addLayer(layer);
 var popup = L.popup({maxWidth: 1400})
    .setLatLng(layer.getLatLng())
    .setContent('<form role="form" id="form" enctype="multipart/form-data" onsubmit="addMarker()">'+


             '<div class="form-group">'+
              '<label class="control-label col-sm-10"><strong>Was the person following protocol?</strong></label>'+ "<br>" +
              '<input type="text" placeholder="Yes, No, or I dont know" id="protocol" name="protocol" class="form-control"/>'+ 
          '</div>'+

             '<div class="form-group">'+
              '<label class="control-label col-sm-10"><strong>Jurisdiction </strong></label>'+ "<br>" +
              '<input type="text" placeholder="City, State, County, or Other" id="jurisdiction" name="jurisdiction" class="form-control"/>'+ 
          '</div>'+


           '<div class="form-group">'+
              '<label class="control-label col-sm-10"><strong>Date </strong></label>'+ "<br>" +
              '<input type="text" placeholder="8/1/2015" id="date" name="date" class="form-control"/>'+ 
          '</div>'+

          '<div class="form-group">'+
              '<label class="control-label col-sm-10"><strong>Time of day </strong> </label>'+ "<br>" +
              '<input type="text" placeholder="Morning, Afternoon, or Night" id="time" name="time" class="form-control"/>'+ 
          '</div>'+

          '<div class="form-group">'+
              '<label class="control-label col-sm-10"><strong>Comments </strong></label>'+ "<br>" +
              '<input type="text" placeholder="Briefly tell us what happened" id="comments" name="comments" class="form-control"/>'+ 
          '</div>'+

          '<div class="form-group">'+
              '<label class="control-label col-sm-10"><strong>Video Link </strong></label>'+ "<br>" +
              '<input type="text" placeholder="Do you have a video? Add link here" id="video" name="video" class="form-control"/>'+ 
          '</div>'+

          '<input style="display: none;" type="text" id="lat" name="lat" value="'+coords.lat.toFixed(6)+'" />'+
          '<input style="display: none;" type="text" id="lng" name="lng" value="'+coords.lng.toFixed(6)+'" />'+

          '<div class="form-group">'+
                '<div style="text-align:center;" class="col-xs-11"><button style="text-align:center;" type="submit" id="submit" value="submit" class="btn btn-primary trigger-submit">Submit</button></div>'+
          '</div>'+ "<br>" +

        '<strong>' + '-' + '</strong'+

          '</form>')
        .openOn(map);

        $('#form').submit(function(e){
        e.preventDefault();
        });

        $('#submit').click(function(e) {
        console.log('clicked!');


        //do your own request an handle the results
           $.ajax({
                url: 'submit.php',
                type: 'post',
                dataType: 'json',
                data: $("#form").serialize(),
                complete: function(data) {
                  console.log("DONE");
              window.location = "Map_member1.php"
            }


         });
});


}

)


//Adding a Scale Control
L.control.scale().addTo(map);


    //adding a Layer Control
var baseLayers = {
    "Satellite imagery": aLayerOne,
    "Streets": aLayerTwo
};

// create a variable holding the overlays
var overLays  = {
'Cities': aLayerThree,


};


function askForIndidents() {
    $.ajax({
        url: 'get.php',
        dataType: 'json',
        data: data,
        success: showIncidents
    });
}

function showIncidents(ajxresponse) {
    lyr = L.geoJson(ajxresponse, {onEachFeature: makePopup})
    lyr.addTo(map);
    }

L.control.layers(baseLayers, overLays, {collapsed: false}).addTo(map);

    $(document).ready(function() {
    $.ajaxSetup({cache:false});
    $('#myMap').css('height', ($(window).height() - 50));
    getUsers();
  });

  $(window).resize(function () {
    $('#myMap').css('height', ($(window).height() - 50));
  }).resize();

    ///Creating custom control here
// part 1/3 : Creates a control with the given position
var aControl = L.control({position: 'bottomright'});

// part 2/3 : Should contain code that creates all the neccessary DOM elements for the control
aControl.onAdd = function () {

        aDiv = L.DomUtil.create('div', 'aCustomC'); // create a div with a class "aCustomC"
        aDiv.innerHTML = "<strong>Click on your city!</strong>" // Each HTML element has an innerHTML property that defines both the HTML code and the text that occurs between that element's opening and closing tag.

                    return aDiv;
}; // end function onAdd

// part 3/3 : Add the control to the map
aControl.addTo(map);

    // create the geocoding control and add it to the map
  var searchControl = new L.esri.Controls.Geosearch({useMapBounds: 8}).addTo(map);

  // create an empty layer group to store the results and add it to the map
  var results = new L.LayerGroup().addTo(map);

  // listen for the results event and add every result to the map
  searchControl.on("results", function(data){
  results.clearLayers();
  for (var i = data.results.length - 1; i >= 0; i--) {
  results.addLayer(L.marker(data.results[i].latlng));


};
    });

});// end document ready

I am new to this and I'm hoping someone can help me in the right direction!

Answer 1

See code example Leaflet users map https://github.com/bmcbride/leaflet-users-map by Bryan McBride. There is used SQLite DB, but her easy replace on MySQL.

Answer 2

Your code:

Wont work because 'Incidents' isn't defined yet (hence your error message). Try running it without the 'Incidents' in here.

Or drop your code into this section:

function showIncidents(ajxresponse) {
    lyr.clearLayers();
    lyr.addData(ajxresponse);
}

Something like this:

function showIncidents(ajxresponse) {
    // Incidents geojson layer
    lyr = L.geoJson(ajxresponse, {
        onEachFeature: makePopup
    })
    lyr.addTo(map);
}

That might work. You're doing a lot at once, and it's a little unclear where it might go wrong!

You can have a look at one of my examples here, which uses ajax to display GeoJSON data: https://github.com/gccgisteam/maps-website/blob/master/graffiti.html

It's a little complex, but you should be able to generalise it from there.