使用ajax从传单映射中获取数据

Question

I have a leaflet map on my website. What i want to do is to get the position data from the marker a user put and transfer it to my database via session .I cant get it working my database userPos isnt updating. Can you explain what am i doing wrong? (dbconnect.php already works ,tested)

this is my home.php file where the map is :

<?php
 ob_start();
 session_start();
 require_once 'dbconnect.php';

 if( !isset($_SESSION['user']) ) {
  header("Location: index.php");
  exit;
 }
 $res=mysqli_query($conn,"SELECT * FROM `users` WHERE userId=".$_SESSION['user']);
 $userRow[]=mysqli_fetch_array($res);

echo json_encode($userRow);
?>
<script>
    $(document).ready(function(){
       var mymap = L.map('map').setView([51.505, -0.09], 13);
       L.tileLayer('https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=xxx', {
    attribution: 'Map data &copy; <a href="http://openstreetmap.org">OpenStreetMap</a> contributors, <a href="http://creativecommons.org/licenses/by-sa/2.0/">CC-BY-SA</a>, Imagery © <a href="http://mapbox.com">Mapbox</a>',
    maxZoom: 18,
    id: 'mapbox.streets',
    accessToken: 'xxx'
}).addTo(mymap);
var marker;
mymap.on('click', function (e) {
  if (marker) {
    mymap.removeLayer(marker);
  }
  marker = new L.Marker(e.latlng).addTo(mymap);
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        var user = <?php echo json_encode($userRow['userId']); ?>;
        xmlhttp.open("GET","homemappositions.php?q="+ e.latlng + "&r=" + user, true);
        xmlhttp.send();
});
});

</script>

and this is homemappositions.php :

<?php
include_once('dbconnect.php');
  $r = $_GET['r'];
  $q = $_GET['q'];
  $query2 = mysqli_query($conn,"UPDATE users SET userPos =". $q ."WHERE userId=". $r ); 
  echo "succesfull" ;
?>

Answer 1

I hope the following might help a little - as the original code was vulnerable to SQL injection the following uses prepared statements to mitigate against that threat. The update statement does not use the % wilcard as I felt sure that was probably the cause of the update failing - though your last comment leads me to think there is another issue.

The ajax function does not necessarily need a callback function but it would aid debugging if there was one - in the code below it is just a simple alert statement but could/should be more sophisticated.

<?php
    ob_start();
    session_start();

    require_once 'dbconnect.php';

    if( !empty( $_SESSION['user'] ) ){
        exit( header('Location: index.php') );
    }

    $uid=false;
    $user=$_SESSION['user'];

    /* The only column used later is userid so only select that column */
    $sql='select `userid` from `users` where userid=?';

    /* Prepare the sql */
    $stmt=$conn->prepare( $sql );

    if( $stmt ){

        /* Bind the parameters to the sql statement */
        $stmt->bind_param( 's', $user );
        $res=$conn->execute();

        /* Assign retrieved results */
        if( $res && $stmt->num_rows==1 ){
            $stmt->bind_result( $uid );
            $stmt->fetch();
            $stmt->free_result();
            $stmt->close();
        }
    }
?>

<script>
    $(document).ready(function(){
        var mymap = L.map('map').setView([51.505, -0.09], 13);
        L.tileLayer( 'https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=xxx', {
            attribution: 'Map data &copy; <a href="http://openstreetmap.org">OpenStreetMap</a> contributors, <a href="http://creativecommons.org/licenses/by-sa/2.0/">CC-BY-SA</a>, Imagery © <a href="http://mapbox.com">Mapbox</a>',
            maxZoom: 18,
            id: 'mapbox.streets',
            accessToken: 'xxx'
        }).addTo( mymap );

        var marker;
        mymap.on( 'click', function(e) {
            if( marker ) {
                mymap.removeLayer( marker );
            }
            marker = new L.Marker(e.latlng).addTo(mymap);

            <?php
                echo "var user='$uid';";
            ?>

            var xmlhttp = window.XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject('Microsoft.XMLHTTP');
            xmlhttp.onload=function(){
                alert( this.response );
            };
            xmlhttp.onerror=function( err ){
                alert( err );
            };
            xmlhttp.open( 'GET', 'homemappositions.php?q='+e.latlng+'&r='+user, true );
            xmlhttp.send();
        });
    });
</script>







<?php

    include 'dbconnect.php';

    $r = !empty( $_GET['r'] ) ? $_GET['r'] : false;
    $q = !empty( $_GET['q'] ) ? $_GET['q'] : false;

    if( $r && $q ){
        $sql='update `users` set `userpos`=? where `userid`=?';
        $stmt=$conn->prepare( $sql );
        if( $stmt ){
            $stmt->bind_param( 'ss', $q, $r );
            $result=$stmt->execute();
            echo $result ? 'good' : 'bad'; //ugly
        }
    } else {
        exit('Bad Foo - two parameters are required');
    }
?>