ST_GeomFromText in PDO returns error (php)

Question

I am currently coding the backend service for my android apps, and since it is not my specialty, I am confused as to why i got this error.

The purpose is to save a list of location coordinate into geometry data in my database from my apps. At first, I simply use mysql_query to make a communication with my database, but then I learn about "sql injections" so I convert all my code into using PDO.

Here is my code with mysql_query:

$submitRoute = mysql_query("INSERT INTO route(id, route) VALUES('$id', ST_GeomFromText('LINESTRING($route)'))");

At this point, I succesfully send and also fetch and display the routes I have sent. But when I changed it into PDO style, everthing works fine except in this query I received "http error 500".

Here is my code with PDO style:

$submitRoute = $conn->prepare("INSERT INTO route(id, route) VALUES(:id, :route");
$result = $submitRoute->execute(array(
    ':id'       => $_POST['id'],
    ':route'    => ST_GeomFromText('LINESTRING($_POST['route'])'))
));

I have also tried:

$submitRoute = $conn->prepare("INSERT INTO route(id, route) VALUES(:id, :route");
$result = $submitRoute->execute(array(
    ':id'       => $_POST['id'],
    ':route'    => ST_GeomFromText('LINESTRING(' . $_POST['route'] . ')'))
));

And this, because I thought maybe the ST_GeomFromText only works inside a query string:

$submitRoute = $conn->prepare("INSERT INTO route(id, route) VALUES(:id, ST_GeomFromText('LINESTRING(:route)'))");
$result = $submitRoute->execute(array(
    ':id'       => $_POST['id'],
    ':route'    => $_POST['route']
));

But the last 2 ends up in an error exception catch. Can anyone tell me where I did it wrong? Thank you

Answer 1

This is how it works for me:

    $query = "INSERT INTO geo (`id`, `gps_coordinates`) VALUES (:id, ST_GeomFromText(:point))";
    $values = [
        ':id' => 5,
        ':point' => 'POINT(' . $lat . ' ' . $lng . ')'
    ];