Recover Mysql data and save to a vector

Question

Rephrasing the question.. I have a Mysql database, inside it contains a table called map, inside the table map I have the fields idmapa (auto_increment), lat (which stores the latitude of a map position), lng (which stores the longitude of a map position), detail (which stores the details that the user can enter, referring to the position of the map) and image (which stores only the name of the image referring to the position of the map). The ajax script below sends the data I want to save in the database to a php file. Inside the php file, I have an if, which will receive the data sent by ajax to save in the database inside the map table the respective data according to the fields of the database cited above.

What I want to do: I need to do the reverse process, that is, request the data from the map table for php, using ajax in the request, but, after requested, I want to save this data in specific vectors, so that later I can create a loop to add this data to a marking on the map. NOTE: I am using Google maps API Javascript.

PHP saving in the bank

 if($_GET['acao']=='btn_finaliza'){

 $latitude = $_GET['latitude'];
 $longitude = $_GET['longitude'];
 $detalhe = $_GET['detalhe'];
 $imagem = $_GET['imagem'];

 $SQL = "INSERT INTO mapa (lat, lng, detalhe, imagem) VALUES ('$latitude','$longitude','$detalhe','$imagem')";

 $re = mysql_query($SQL, $serve)or die(mysql_error()); 
 echo $SQL;
 }

Ajax script that sends data to php

  $.ajax({

  type: "get",
  url: $server+"/conecta.php",
                 data: "latitude="+$tlati+"&longitude="+$tlong+"&detalhe="+$detalhe+"&imagem="+$imagem+"&acao=btn_finaliza",
                                success: function(data) {
                                    location.href='#page_mapa';
                                    navigator.notification.alert('Problema cadastrado', '', 'ok');}

  });

After evaluating the tips, I’m trying this way:

PHP:

if($_GET['acao']== 'marcacao'){

 $SQL = "SELECT * FROM mapa";
 $resultados = mysql_query($sql)or die (mysql_error());
 $res=mysql_fetch_array($resultados); 

 if ($linha = @mysql_num_rows($resultados) == 0){
 echo 0;
 }

 else{
     echo $linha['lat'];
     echo $linha['lng'];
     exit;
}
}

Java Script and Ajax:

     function marcacao(){       
        $.ajax({
        type: "get",
        url: $server+"/conecta.php",
        data: 'acao=marcacao',
        success: function(data) {
        // le o retorn de data, que já será um array/vetor, e popula seu mapa com API do Google Maps

            var map = new google.maps.Map(
                    document.getElementById("map"), 
                    { 
                      center : new google.maps.LatLng($lat, $lng), 
                      zoom : 5, 
                      mapTypeId : google.maps.MapTypeId.ROADMAP 
                    }
            );

            var image = 'images/ray.png';

            var marker = new google.maps.Marker(
                {
                        title : "VOCÊ ESTÁ AQUI: "+$lat+", "+$lng,
                        position : new google.maps.LatLng($lat, $lng),
                        map: map,
                        icon: image
                 });

            marker.setMap(map);  
          }

    });

   }

However, I think the way I’m passing the php variables to javascript (echo $line["lat"] and echo $line["lng"]]) is wrong, and so the markings on the map are not being made.

Answer 1

@G.Vilela

You’ve got 90% of the way, so it’s easy.

IMPORTANT: I’m not a PHP programmer.

BACKEND - REST API

Instead of making a PHP to answer everything conecta.php, create a more expensive API structure even that will make your code more organized, semantic and responsible.

/api
/api/mapa/
/api/mapa/get.php
/api/mapa/post.php
/api/mapa/put.php
/api/mapa/delete.php

Where:

• GET: To recover data
• POST: To enter data
• PUT: To change data
• DELETE: To delete data

So your /api/mapa/post.php, responsible for entering MAP data, will look something like this:

 $latitude = $_POST['latitude'];
 $longitude = $_POST['longitude'];
 $detalhe = $_POST['detalhe'];
 $imagem = $_POST['imagem'];

 $SQL = "INSERT INTO mapa (lat, lng, detalhe, imagem) VALUES ('$latitude','$longitude','$detalhe','$imagem')";

 $re = mysql_query($SQL, $serve) or die(mysql_error()); 

And to retrieve map data, do the /api/mapa/get.php something like that:

 $latitude = $_GET['latitude'];
 $longitude = $_GET['longitude'];

 $SQL = "SELECT * FROM mapa WHERE lat = $latitude AND lng = $longitude";

 $re = mysql_query($SQL, $serve) or die(mysql_error()); 

 // Trecho para retornar o resultado da consulta em JSON
 echo "[";
 // Para cada linha retornado pelo SELECT
    echo "{\"lat\": $lat, \"lng\": $lng, \"detalhe\": \"$detalhe\", \"imagem\": \"$imagem\" }"
 echo "]";

FRONDEND - JQUERY AJAX

Already in your script, to insert data is like this:

var data = {
    latitude: $tlati,
    longitude: $tlong,
    detalhe: $detalhe,
    imagem: $imagem
};

$.ajax({
  type: "post",
  url: $server+"/api/mapa/post.php",
  data: JSON.Stringfy(data),
  success: function(data) {
      location.href='#page_mapa';
      navigator.notification.alert('Problema cadastrado', '', 'ok');
  }
});

And to consult:

$.ajax({
  type: "get",
  url: $server+"/api/mapa/get.php?lat="+$lat+"&lng="+lng,
  success: function(data) {
      // le o retorn de data, que já será um array/vetor, e popula seu mapa com API do Google Maps
  }
});