0

How do I insert Leaflet Draw coordinates in the MySQL database with id longitude-latitude columns?

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Drone fika</title>
    <!-- <link rel="stylesheet" href="src/leaflet.css"/> -->
    <link rel="stylesheet" href="https://unpkg.com/leaflet@1.7.1/dist/leaflet.css"/>
    <!-- Make sure you put this AFTER Leaflet's CSS -->
    <!-- <script src="src/leaflet.js" ></script> -->
    <script src="https://unpkg.com/leaflet@1.7.1/dist/leaflet.js"></script>
    <link rel="stylesheet" href="src/plugins/leaflet-draw/leaflet.draw.css">
        <!--    ***************  Begin Leaflet.Draw-->

    <script src="src/plugins/leaflet-draw/Leaflet.draw.js"></script>
    <script src="src/plugins/leaflet-draw/Leaflet.Draw.Event.js"></script>
    <link rel="stylesheet" href="src/plugins/leaflet-draw/leaflet.draw.css" />
    <link rel="stylesheet" href="src/">
    <script src="src/plugins/leaflet-draw/Toolbar.js"></script>
    <script src="src/plugins/leaflet-draw/Tooltip.js"></script>

    <script src="src/plugins/leaflet-draw/ext/GeometryUtil.js"></script>
    <script src="src/plugins/leaflet-draw/ext/LatLngUtil.js"></script>
    <script src="src/plugins/leaflet-draw/ext/LineUtil.Intersect.js"></script>
    <script src="src/plugins/leaflet-draw/ext/Polygon.Intersect.js"></script>
    <script src="src/plugins/leaflet-draw/ext/Polyline.Intersect.js"></script>
    <script src="src/plugins/leaflet-draw/ext/TouchEvents.js"></script>

    <script src="src/plugins/leaflet-draw/draw/DrawToolbar.js"></script>
    <script src="src/plugins/leaflet-draw/draw/handler/Draw.Feature.js"></script>
    <script src="src/plugins/leaflet-draw/draw/handler/Draw.SimpleShape.js"></script>
    <script src="src/plugins/leaflet-draw/draw/handler/Draw.Polyline.js"></script>
    <script src="src/plugins/leaflet-draw/draw/handler/Draw.Circle.js"></script>
    <script src="src/plugins/leaflet-draw/draw/handler/Draw.Marker.js"></script>
    <script src="src/plugins/leaflet-draw/draw/handler/Draw.Polygon.js"></script>
    <script src="src/plugins/leaflet-draw/draw/handler/Draw.Rectangle.js"></script>

    <script src="src/plugins/leaflet-draw/edit/EditToolbar.js"></script>
    <script src="src/plugins/leaflet-draw/edit/handler/EditToolbar.Edit.js"></script>
    <script src="src/plugins/leaflet-draw/edit/handler/EditToolbar.Delete.js"></script>

    <script src="src/plugins/leaflet-draw/Control.Draw.js"></script>

    <script src="src/plugins/leaflet-draw/edit/handler/Edit.Poly.js"></script>
    <script src="src/plugins/leaflet-draw/edit/handler/Edit.SimpleShape.js"></script>
    <script src="src/plugins/leaflet-draw/edit/handler/Edit.Circle.js"></script>
    <script src="src/plugins/leaflet-draw/edit/handler/Edit.Rectangle.js"></script>
    <script src="src/plugins/leaflet-draw/edit/handler/Edit.Marker.js"></script>

    <!--    **************  End of Lealet Draw-->
</head>
<style>
    #map {
        height: 500px;
    }
</style>

<body>
    <div>
        <!--map display  -->
        <div id="map"></div>
        <?php
        $conn = new mysqli("localhost", "root", "", "dronfikamap");
        $result = mysqli_query($conn, "SELECT latitude,longitude,timeN FROM maps WHERE latitude is not NULL or longitude is not NULL or timeN is not NULL");
        if (mysqli_connect_errno()) {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
            exit();
        }
        echo "<table>";
        while ($row = mysqli_fetch_assoc($result)) {
            echo "<tr>";
            echo "<td align='center' width='200'>" . $row['latitude'] . "</td>";
            echo "<td align='center' width='200'>" . $row['longitude'] . "</td>";
            echo "<td align='center' width='200'>" . $row['timeN'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        mysqli_free_result($result);
        // mysqli_close($conn);
        ?>
    </div>
        


    <!-- mappinmjdkljsdljscript -->
    <script>
        var map = L.map('map').setView([-0.390771, 36.968123], 13);

var openStreet= L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
    attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
}).addTo(map);

var maerker= L.marker([-0.390771, 36.968123]).addTo(map)
    .bindPopup('A pretty CSS3 popup.<br> Easily customizable.')
    .openPopup();
    <?php
$conn = new mysqli("localhost" ,"root", "","dronfikamap");
$result = mysqli_query($conn, "SELECT latitude,longitude,timeN FROM maps WHERE latitude is not NULL or longitude is not NULL or timeN is not NULL");
while ($row = mysqli_fetch_assoc($result)) {
    ?>
     var marker = L.marker([<?php echo $row['latitude']?>,<?php echo $row['longitude'] ?>]).addTo(map).bindPopup('<?php echo $row['timeN']?>')
    .openPopup(); 
     <?php
}
?> 
var baseMaps = {
    "openStreet": openStreet,
};


var drawnItems = new L.FeatureGroup();
     map.addLayer(drawnItems);
     var drawControl = new L.Control.Draw({
        draw: {
            circle: true,
          },
         edit: {
             featureGroup: drawnItems
         }
     }).addTo(map);
     map.addControl(drawControl);
    map.on('draw:created', function (e) {
        var type = e.layerType;
        var layer = e.layer;

        var shape = layer.toGeoJSON()
        var shape_for_db = JSON.stringify(shape);
        
        drawnItems.addLayer(e.layer);
        console.log(e);
        var latitude=layer.getLatLng().lat;
        var lagitude=layer.getLatLng().lng;
        // <?php
        // $sql = "INSERT INTO c_map 
        // (latitude,longitude) 
        //  VALUES ('latitude','longitude',
        //  '".mysql_real_escape_string($modelJson)."');";
        ?>

    });
    var overlayMaps = {
    "Drawn items": drawnItems,
    "maeker":maerker,
    "device Locations":marker
};
L.control.layers(baseMaps, overlayMaps).addTo(map);
    </script>
    
</body>

</html>
1
  • This question is not GIS related and should be posted on StackOverflow site.
    – TomazicM
    Jan 22, 2021 at 8:32

1 Answer 1

1

Create a new php file and readout the passed data:

insert.php

<php
$lat = $_GET["lat"];
$lng = $_GET["lng"];

$conn = new mysqli("localhost", "root", "", "dronfikamap");

// prepare and bind
$stmt = $conn->prepare("INSERT INTO c_map  (latitude, longitude) VALUES (?,?)");
$stmt->bind_param("ss", $lat, $lng);
$stmt->execute();

$stmt->close();
$conn->close();

?>

Send the data in the js file to the php file:

map.on('draw:created', function (e) {
        var type = e.layerType;
        var layer = e.layer;
        var latitude=layer.getLatLng().lat;
        var lagitude=layer.getLatLng().lng;

        fetch('./insert.php?lat='+latitude+'&lng='+lagitude)
});

Prepare Insert

You can extend the insert to whatever you want.

This code is not tested but it should work. Try first if the php file is working on a direct call over the browser: localhost/insert.php?lat=1&lng=1

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.