Passing data from mysql/PHP into Leaflet

Question

I'm trying to add points to a Leaflet map.

The points are stored in a MySQL database as latitude and longitude.

I would like some leads and recommendations.

<?php
    $showpoint = $bdd->prepare("SELECT t.Depart AS tDepart, v1.ville_latitude_deg AS lat1, v1.ville_longitude_deg AS long1,t.Arriver AS tArriver, v2.ville_latitude_deg AS lat2, v2.ville_longitude_deg AS long2 FROM trajet t, villes_france_free v1, villes_france_free v2 WHERE t.Depart = v1.ville_nom_reel AND t.Arriver = v2.ville_nom_reel");
    $showpoint->execute();
    $nbRows = $showpoint->rowCount();
?>

<script>
    //Définir les coordonnées du centre de la carte et le seuil de zoom
    var mymap = L.map('mapid').setView([43.665,7.193], 6);
    // Ajouter le fond de carte
    L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
        attribution:'&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
        }).addTo(mymap);

    <?php
        $donnees = $showpoint->fetch();
        for ($i = 0;$i< $nbRows; $i++)
            {
                L.marker($lat1[$i], $long1[$i]).addTo(mymap) .bindPopup("<b>Popup n°0</b>");
            }
    ?>

</script>

So I would post all starting points and points to arrive.

Answer 1

OK, so the main struggle (and obvious error) is the newbie mistake of not controlling which code runs in PHP in the server, which code runs in JS in the browser, and not having control over which variable holds what.

Do read: «How to pass variables and data from PHP to JavaScript?»

Seriously, read that. If you copy-paste my code, the stackoverflow gods will kill a kitten. Think of the kitten.

---

<?php
    // These are all PHP variables. The web browser doesn't know about them.

    $showpoint = $bdd->prepare("SELECT foo from bar");
    $showpoint->execute();
    $nbRows = $showpoint->rowCount();

    // Yes, $donnees is also a PHP variable

    $donnees = $showpoint->fetch();
?>

<script>
    var mymap = L.map('mapid').setView([43.665,7.193], 6);

    L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
        attribution:'&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
        }).addTo(mymap);

    // $donnees is a PHP var. We dump its JSON representation into a
    // javascript variable definition and initialization for "var data".
    var data = <?php echo JSON_encode($donnees); ?>;

    // Note "var i", not "$i". This is the browser iterating through "data".
    for (var i = 0; i < data.length; i++)
    {
        // Note how "L.marker()" runs only in the browser,
        // well outside of the <?php ?> tags. PHP doesn't know, nor 
        // it cares, about Leaflet.
        L.marker(data[i].lat, data[i].lng).addTo(mymap);

        // Accessing the properties of the data depends on the structure
        // of the data. You might want to do stuff like
        console.log(data);
        // while remembering to use the developer tools (F12) in your browser.
    }

</script>

Whenever you're starting to code in PHP, it's critical that you press Ctrl+U in your browser to See page source. Read, and understand what PHP is sending to the web browser, and how the browser is running that code+data. You cannot just put a bunch of code together and expect it to work.