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>
Improve this question
1
  • This question is not GIS related and should be posted on StackOverflow site.
    – TomazicM
    Jan 22, 2021 at 8:32

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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