1

I want to save my coordinate from my Android application to a MySQL database. For this I created an API in PHP, but my code is not working.

Here is my PHP code:

<?php
  include_once 'db.php';

  $nop = $_POST['nop'];
  $plot_bng = $_POST['plot_bng'];

  $result = mysqli_query($con, "INSERT INTO sp_house (geom, d_nop)
                                VALUES (STGeomFromText('POINT($plot_bng)'), '$nop')");

  echo json_encode(array("value"=>1));

  mysqli_close($con);
?>

When I try an INSERT query in phpMyadmin, the data is successfully stored in the database.

jkdev
  • 11,360
  • 15
  • 54
  • 77
Ryuta
  • 31
  • 1
  • 5
  • 2
    https://dev.mysql.com/doc/refman/5.6/en/spatial-types.html -- mainly look at the data format – popeye Jul 17 '18 at 06:13
  • Are you sure you have data in `$nop` and `$plot_bng`? And if you just simply try to insert data into the database without any kind of inspection you are widely open to SQL Injection. Try using `prepared statements` and `mysqli_real_escape_string` before interacting with the database! – squancy Jul 17 '18 at 07:51

2 Answers2

2

Check the right property for the columns

First of all, make sure you have created the right spatial columns in the database by using the GEOMETRY keyword.

CREATE TABLE sp_house (geom GEOMETRY, d_nop VARCHAR(255));

Insert data into the database with authentication

After you created the columns with the right property you can insert the data into your database. However, your code is widely open to SQL Injection and other kind of database hackings since you insert data directly without any kind of authentication. In order to avoid it, use prepared statements and the mysqli_real_escape_string function. Also, check that you have the right syntax for the query and replace STGeomFromText to ST_GeomFromText.

<?php
  include_once 'db.php';

  $nop = $_POST['nop'];
  $plot_bng = $_POST['plot_bng'];

  // You can also check that the variables are empty or not ...

  // Clean the variables and prepare for inserting
  $plot_bng = mysqli_real_escape_string($con, $plot_bng);
  $nop = mysqli_real_escape_string($con, $nop);
  $sql = "INSERT INTO sp_house (geom, d_nop)
                                VALUES (ST_GeomFromText(POINT(?)), ?)";

  // Prepared statement for inserting
  $stmt = $conn->prepare($sql); // prepare statement for inserting
  $stmt->bind_param("ss",$plot_bng,$nop); // replace question marks with values
  $stmt->execute(); // execute command
  $stmt->close(); // close connection

  echo json_encode(array("value"=>1));

  mysqli_close($con);
?>

Reference and further reading

Creating Spatial Columns in MySQL
Populating Spatial Columns
How to avoid SQL Injection?
How to use prepared statements?

squancy
  • 565
  • 1
  • 7
  • 25
  • only geom is a geometry data type. I will try your code, In code $stmt->bind_param("ss",$plot_bng,$nop); what is that >> "ss" ? – Ryuta Jul 18 '18 at 03:26
  • First of all, read the reference that is below the code, then you will get an overall knowledge about prepared statements. To answer your question, `s` stands for `string` type `i` stands for integer type `d` stands for `double` and `b` statnds for `BLOB`. – squancy Jul 18 '18 at 05:59
  • It means that the two variables `$plot_bng` and `$nop` are both of `string` types. It is a necessary parameter for the `bind_param` function. – squancy Jun 06 '19 at 21:48
0

You're not writing your var correctly, try like this

$result = mysqli_query($con, "INSERT INTO sp_house (geom, d_nop)
VALUES (STGeomFromText('POINT(".$plot_bng.")'), '".$nop."')");

As you writing it, the vars are just normal text...

FixFaier
  • 237
  • 1
  • 2
  • 11
  • I just tried your code, and I testing in postman, status in postman "200 OK", but when I check my DB, i didn't find new data inserted. – Ryuta Jul 17 '18 at 07:01
  • You will always get "200 OK" ass you sending always json_encode(array("value"=>1)) out... – FixFaier Jul 17 '18 at 10:47
  • Include a check `if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } else { echo json_encode(array("value"=>1)); }` – FixFaier Jul 17 '18 at 10:49