inserting points in postgis with pdo (php)

Question

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

• John Powell: 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 ".

Answer 1

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.

Answer 2

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.