multiple params in pg_prepare

Hi,

I'm trying to set up a prepared sql statement with 3 parameters and
then execute with array of 3 elements (this is to a postgresql
database with postgis):

$result = pg_prepare($dbconn, "my_query", 'INSERT INTO gtest VALUES ($1,
GeomFromText(\'POINT($2 $3)\', 4326))');

$result = pg_execute($dbconn, "my_query", array("phptest", "23", "24"));

But when I ran it, it gave me this error:
Warning: pg_execute(): Query failed: ERROR: bind message supplies 3
parameters, but prepared statement "my_query" requires 1 in
/home/dlamoris/phptest.php on line 15

Am I missing something?

Thanks,
Doris

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: multiple params in pg_prepare

dlamoris@ucla.edu wrote:
> Hi,
>
> I'm trying to set up a prepared sql statement with 3 parameters and then
> execute with array of 3 elements (this is to a postgresql database with
> postgis):
>
> $result = pg_prepare($dbconn, "my_query", 'INSERT INTO gtest VALUES ($1,
> GeomFromText(\'POINT($2 $3)\', 4326))');
>
> $result = pg_execute($dbconn, "my_query", array("phptest", "23", "24"));
>
> But when I ran it, it gave me this error:
> Warning: pg_execute(): Query failed: ERROR: bind message supplies 3
> parameters, but prepared statement "my_query" requires 1 in
> /home/dlamoris/phptest.php on line 15

Probably because of the single quotes around the point() function.

Try the heredoc type syntax suggested on http://php.net/pg_prepare:

$my_query = << INSERT INTO gtest VALUES($1, GeomFromText('POINT('$2, $3')', 4326));
MYQRY;


Also I'd suggest naming the columns in the first part:

insert into gtest(col1, col2, col3) values (....);

so it's always in the same order and you're not relying on any
behaviour. If you add a column in the middle of your table later on,
this is going to break a lot of your code because the columns will be
out of order.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: multiple params in pg_prepare

Nope, still doesn't work...for the moment I'll just preset the variables
and use double quotes for the query string. The actual insert statement
should be like this:

INSERT INTO gtest VALUES (city, GeomFromText('POINT(lon lat)', 4326))

where lon and lat are decimal numbers, and I wanted to replace city,
lon, lat with different values each time I insert...
It seems like any $var after a single quote, even with \', gets ignored?
I also tried using double quotes and escaping $, but doesn't work either..
Thanks though.

Doris

Chris wrote:
> dlamoris@ucla.edu wrote:
>> Hi,
>>
>> I'm trying to set up a prepared sql statement with 3 parameters and
>> then execute with array of 3 elements (this is to a postgresql
>> database with postgis):
>>
>> $result = pg_prepare($dbconn, "my_query", 'INSERT INTO gtest VALUES ($1,
>> GeomFromText(\'POINT($2 $3)\', 4326))');
>>
>> $result = pg_execute($dbconn, "my_query", array("phptest", "23", "24"));
>>
>> But when I ran it, it gave me this error:
>> Warning: pg_execute(): Query failed: ERROR: bind message supplies 3
>> parameters, but prepared statement "my_query" requires 1 in
>> /home/dlamoris/phptest.php on line 15
>
> Probably because of the single quotes around the point() function.
>
> Try the heredoc type syntax suggested on http://php.net/pg_prepare:
>
> $my_query = << > INSERT INTO gtest VALUES($1, GeomFromText('POINT('$2, $3')', 4326));
> MYQRY;
>
>
> Also I'd suggest naming the columns in the first part:
>
> insert into gtest(col1, col2, col3) values (....);
>
> so it's always in the same order and you're not relying on any
> behaviour. If you add a column in the middle of your table later on,
> this is going to break a lot of your code because the columns will be
> out of order.
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: multiple params in pg_prepare

How about combining your POINT() into one parameter:

$result = pg_prepare($dbconn, "my_query",
'INSERT INTO gtest VALUES ($1,GeomFromText($2, 4326))');

$result = pg_execute($dbconn, "my_query",
array("phptest", "POINT(23 24)"));


Doris Lam wrote:
> Nope, still doesn't work...for the moment I'll just preset the variables
> and use double quotes for the query string. The actual insert statement
> should be like this:
>
> INSERT INTO gtest VALUES (city, GeomFromText('POINT(lon lat)', 4326))
>
> where lon and lat are decimal numbers, and I wanted to replace city,
> lon, lat with different values each time I insert...
> It seems like any $var after a single quote, even with \', gets ignored?
> I also tried using double quotes and escaping $, but doesn't work either..
> Thanks though.
>
> Doris
>
> Chris wrote:
>> dlamoris@ucla.edu wrote:
>>> Hi,
>>>
>>> I'm trying to set up a prepared sql statement with 3 parameters and
>>> then execute with array of 3 elements (this is to a postgresql
>>> database with postgis):
>>>
>>> $result = pg_prepare($dbconn, "my_query", 'INSERT INTO gtest VALUES ($1,
>>> GeomFromText(\'POINT($2 $3)\', 4326))');
>>>
>>> $result = pg_execute($dbconn, "my_query", array("phptest", "23", "24"));
>>>
>>> But when I ran it, it gave me this error:
>>> Warning: pg_execute(): Query failed: ERROR: bind message supplies 3
>>> parameters, but prepared statement "my_query" requires 1 in
>>> /home/dlamoris/phptest.php on line 15
>>
>> Probably because of the single quotes around the point() function.
>>
>> Try the heredoc type syntax suggested on http://php.net/pg_prepare:
>>
>> $my_query = << >> INSERT INTO gtest VALUES($1, GeomFromText('POINT('$2, $3')', 4326));
>> MYQRY;
>>
>>
>> Also I'd suggest naming the columns in the first part:
>>
>> insert into gtest(col1, col2, col3) values (....);
>>
>> so it's always in the same order and you're not relying on any
>> behaviour. If you add a column in the middle of your table later on,
>> this is going to break a lot of your code because the columns will be
>> out of order.
>>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php