Geometry field issue with MariaDB and PHP prepared statement

geouser 0
Viewed 26

From a PHP script, using mysqli prepared statement to INSERT a row, I'm getting an error returned from MariaDB on a field of type POINT. Code looks a something like this:

$sql = 'INSERT INTO reports (location) VALUES (?)';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $variable);
$variable = 'POINT(10,10)';
$stmt->execute();

location is intended to store GPS co-ordinates and is defined as point data type in it's column definition. The error which is returned in the resulting stmt object has errno = 1416 error = Cannot get geometry object from data you send to the GEOMETRY field.

Other columns of varchar type work without issue, so I'm guessing it is somewhere in the underlying table definition being POINT data type and the presentation of the intended data POINT (10, 10) as a string type in the prepared statement. I had initially thought about just storing lat, lon as individual DECIMAL columns but the POINT data type may serve future purposes much better.

In the MariaDB monitor, the statement:

INSERT INTO reports (location) VALUES (POINT(10,10));

will work fine and insert the row into the database table.

Also, if I were to use mysqli_query (which I don't intend using) as follows;

$variable = 'POINT(20, 20)';
$sql = "INSERT INTO reports (location) VALUES ($variable)";
mysqli_query($mysqli, $sql)

it works as intended.

So, my query is how in PHP, to replicate the working MariaDB monitor statement or mysqli_query() example via a mysqli prepared statement.

mariadb geometry mysqli mysql php
1 Answers

POINT isn't a string, it's an SQL function. MariaDB isn't smart enough to convert strings to SQL, so you cannot bind it as a string. You can bind the parameters of POINT but not the SQL itself.

The following should work fine:

$sql = 'INSERT INTO reports (location) VALUES (POINT(?,?))';
$stmt = $mysqli->prepare($sql);
$stmt->execute([10,10]);
Dharman0
Related