How to insert a point into PostGIS from PHP?

Question

For the following line, I received an error in my PHP page, but the query is OK when I run it from pgmyadmin.

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... ^

Answer 1

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

Answer 2

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