1

I have a problem with sql insert statement using PDO (php, postgis, postgres). I have tried to use different combinations, but none of them work. Here is what I ve got:

$latitude= 46.07175;
$longitude=16.866976;
...
$sql = "insert into table (geom, name, type) values ('(st_setsrid(st_makepoint('|| $longitude ||',' || $latitude || '),4326))'::geom, :name, :type");
  • You are mixing " and ' in a very weird way. When you start concatenating strings with || you need to end the existing string input with whatever you started with, in this case ". – John Powell Jun 21 '16 at 17:55
2

Other than your quotes being funky, if you're using PDO. Use placeholders all the way through. I prefer the ? and the array().

/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('
  INSERT INTO myTable (geom, name, type) VALUES
    ( ST_SetSRID(ST_Makepoint(?, ?), 4326), ?, ? );
');

$sth->execute(array( $longitude, $latitude, $name, $type ));

This has the advantage of

  1. caching the plan, and not having to replan the query for each insert
  2. not having to escape the $long and $lat or worry about sql injection attacks

As a side note if you're using 4326 you should consider the geography type.

1

Try this:

$sql = "insert into table (geom, name, type) values (st_setsrid(
st_makepoint($longitude, $latitude), 4326))";

In double quotes variable substitution is made by PHP, || is the SQL string concatenation operator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.