0

I want to send geographic data (latitude & longitude) to a mySQL database with Ajax call and PHP script. Both Ajax and PHP scripts look ok for me but nothing goes to the database. I've try different syntax and option (like PDO with array) to manage MySQL but still nothing... Do you have an idea of what is going wrong ?

Thank you very much for your help, Flo.

The Jquery code of my Html page is simple :

        function getCoordPosition(){

        if(navigator.geolocation){
        navigator.geolocation.getCurrentPosition(function(position){
        var latitude = position.coords.latitude;
        var longitude = position.coords.longitude;
        $.ajaxSetup({
           url: "insert-in-bdd.php",
           type: "POST",
        });

        $.ajax({
            data: 'latitude='+latitude+'&longitude='+longitude,       
            success: function (msg) {
            alert (msg);},
            error: function (XMLHttpRequest, textStatus, errorThrown)
            {   
            alert('Error submitting request.'); 
            }

            }); 

    });
}

}

The first PHP script I try (insert-in-bdd.php) is:

<?php
header('Content-type: text/html; charset=ISO-8859-1');
try
{
if(isset($_POST['latitude']) && isset($_POST['longitude'])){
$latitude = ($_POST['latitude']);
$longitude = ($_POST['longitude']);
$db = mysql_connect(localhost, root, "");
$select = mysql_select_db(madb, $db);
mysql_query('INSERT INTO location (lat,lng)
           VALUES (:longitude, :longitude)');
}}
 catch(Exception $e)
{
    echo 'Erreur : '.$e->getMessage().'<br />';
    echo 'N° : '.$e->getCode();
}
?>

The second PHP script I try (with PDO and array), same name : insert-in-bdd.php is:

<?php
 header('Content-type: text/html; charset=ISO-8859-1');
try
{
if(isset($_POST['latitude']) && isset($_POST['longitude'])){
$pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
 $bdd = new PDO('mysql:host=localhost;dbname=madb', 'root', '');
$req = $bdd->prepare('INSERT INTO location(lat, lng) VALUES(:lat, :lng)');
//$req = $bdd->prepare('UPDATE location SET lat = :lat');
$req->execute(array(
'lat' => $_POST['latitude'],
'lng' => $_POST['longitude']
));
}}
catch (Exception $e)
{
    die('Erreur : ' . $e->getMessage());
}
?>
Improve this question
7
  • Basic debugging: What do you see in var_dump($_POST) in PHP? Commented Feb 12, 2012 at 19:07
  • mysql_query() doesn't accept input parameters like :latitude. Commented Feb 12, 2012 at 19:08
  • uhm, why the header? Also first code won't work, second looks ok Commented Feb 12, 2012 at 19:08
  • Also, in PDO, the input param keys must match those declared in the SQL. If I recall, that means they need the : as in :lat, :lng rather than lat, lng inside the execute() call. Commented Feb 12, 2012 at 19:10
  • @Michael I believe both ways are accepted; there's a comment in the PDO manual page confirming this, IIRC Commented Feb 12, 2012 at 19:11

2 Answers 2

Reset to default
1

I would do:

$query="insert into location (lat,long) values('".$_POST['latitude']."','".$_POST['longitude']."');";
mysql_query($query,$connection);

You seem to have forgotten the connection from the mysql_query(), and I'm not sure about using :latitude etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I think the connection from the mysql_query() is $bdd in the code. I have try with your 2 lines like this but nothing goes to the database.
Make sure you are passing the name of the database as a string, in your example you have missed the ''s it should be: mysql_select_db('madb',$db);
Tx. I've modify the $select line with the ''s and writted the $query as you'd do. A blank popup windows open but still nothing into the database :-(
Find someway to display $query, echoing it at the top of your page is the easiest thing to do, then you can check the query is valid MYSQL, if it is then the problem is likely with your table/database.
1

For your first code block try the following for the mysql query:

"INSERT INTO location (lat,lng)  VALUES ('" . $latitude . "', '" . $longitude . "')"

Also, it's very important to clean up your data before inserting into the database - this kind of code leaves you open to sql injection.

You should use the function mysql_real_escape_string around your variables. You can either put it in when you assign the post variables to the $longitude and $latitude, or in the query itself.

You need to have a mysql connection available when you call the function.

Improve this answer

3 Comments

Hello, when I code the first block with this it gives me a popup windows with "error submiting request". The query is : mysql_query "INSERT INTO location (lat,lng) VALUES ('" . $latitude . "', '" . $longitude . "')";
The text 'error submitting request' is coming from your callback function. Try doing an alert with the textStatus instead. That might give you more insight.
It's also always wise to test the script directly from the browser before calling it via ajax.

Your Answer

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