如何使用 PHP 在 MySQL 中插入空间数据? [英] How to insert spatial data in MySQL with PHP?

问题描述

我想将我的坐标从我的 Android 应用程序保存到 MySQL 数据库.为此，我用 PHP 创建了一个 API，但我的代码不起作用.

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.

这是我的 PHP 代码:

<?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);
?>

当我在 phpMyadmin 中尝试 INSERT 查询时，数据成功存储在数据库中.

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

推荐答案

检查列的正确属性

首先，确保您使用GEOMETRY 关键字在数据库中创建了正确的空间列.

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));

通过身份验证将数据插入数据库

创建具有正确属性的列后，您可以将数据插入到数据库中.但是，您的代码对 SQL 注入 和其他类型的数据库黑客攻击很开放，因为您无需任何身份验证就直接插入数据.为了避免它，使用prepared statements和mysqli_real_escape_string函数.另外，检查您的查询语法是否正确，并将 STGeomFromText 替换为 ST_GeomFromText.

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);
?>

参考和进一步阅读

在 MySQL 中创建空间列
填充空间列
如何避免 SQL 注入?
如何使用准备好的语句?

这篇关于如何使用 PHP 在 MySQL 中插入空间数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！