How to insert a point into postgis from php?

Question

I do not know how should I explain. It is really strange.

For the following line i got an error in my php page. But It should not be. the query is OK when I run it from pgmyadmin. But it does not work if I run it from php. :( I am trying to insert a point into my postgis from php which contains name, address and a geometric point. Here, p_id is primary key and it is an integer.

$sql = "INSERT INTO app (p_id, p_fname, p_lname, p_saddr, p_caddr, the_geom) VALUES(intval($p_id), $fname, $lname, $street, $city, ST_GeomFromText(POINT($long $lat), 4326))";
$result = pg_query($dbh, $sql);

Error:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "," LINE 1: ...me, p_saddr, p_caddr, the_geom) VALUES(intval(5), , , , , ST... ^ in /Applications/XAMPP/xamppfiles/htdocs/HostMobileApp/requestProcessor.php on line 66
Error in SQL query: ERROR: syntax error at or near "," LINE 1: ...me, p_saddr, p_caddr, the_geom) VALUES(intval(5), , , , , ST... ^

Thanks, in advance.

Answer 1

It looks like you are having a problem with quotes when referring to your column names. Here is the documentation for an Insert Query from the PostgreSQL website: Documentation: Manuals: Insert

The gist is that when you are stating an Insert Into clause, where you name the column names, you do not put these in quotes. You only put the values you are inserting in single quotes, or in your case, you use the variables so no quotes are necessary.

I think your structure should look like this:

$sql = "INSERT INTO app (p_id, p_fname, p_lname, p_saddr, p_caddr, the_geom)
     VALUES(intval($p_id), $fname, $lname, $street, $city, ST_GeomFromText(POINT($long $lat), 4326))";  

$result = pg_query($dbh, $sql);

Answer 2

This a really a duplicate of your previous question, and my answer is the same. Don't mix SQL and WKT! Use ST_MakePoint if you need to embed parameters into an SQL statement.

$sql = 'INSERT INTO app(p_id, p_fname, p_lname, p_saddr, p_caddr, the_geom) '
 . 'VALUES(intval($p_id), $fname, $lname, $street, $city, ST_SetSRID(ST_MakePoint($long, $lat), 4326))';
$result = pg_query($dbh, $sql);