September 18th, 2002, 06:34 AM
Parametrized Query From Php To Postgresql
i'm working wiz postgresql and php and i'd like to know what is the best
way to send a parameterized (?) query to the postgres server and in order
to process results in php.
the ones i found are the following :
1- dynamically construct the query in my php script and send it to the server, i don't
feel like doing so, i'd like to put every sql statement in the database for a better
2- make a function in pl/pgsql taking required parameters and after making a few tests
according to the params makes dynamically the sql query and stores the results in a
temporary table... i'm sure it is not the smartest way...
3- i know this can also be done wiz cursors but i don't really know how it works and how to use
them from a php script...
thanks for reading, i'm waiting for your suggestions!
October 2nd, 2002, 01:03 AM
We discuss PHP and cursors in these two threads:
postgres and cursors
Sybase+Php in production
PHP/PostgreSQL at the moment does not support parameterized queries such as with PHP/Oracle, where you "bind" a PHP variable to a parameter of a pre-compiled query. I hope that we will eventually get this ability, because it can have performance benefits in certain situations.
So a "parameterized query" really just means the query is pre-compiled in the database and only takes certain parameters, which are said to be "bound" to the query. It is not necessarily the same thing as a query that manipulates cursors to store temporary result sets.
But, if you use the cursor method I describe in the threads above, combined with a PL/pgSQL function, you can get some performance improvements. You might want to look at http://www.postgresql.org/idocs/inde...l-cursors.html , and pay attention to the section about bound cursor variables. The point here is that the query plan is cached (maintained in a compiled state), which gives you the same performance advantages of a parameterized query.
The real point of all of this, to sum it up, is "push as much intelligence as you can into the database". If you need to do something that involves several queries, but only requires a couple incoming variables, then creating a stored procedure to handle all these queries will save a lot on I/O, since PHP doesn't have to talk back and forth to the database as much. (this is especially important if you are connecting to a database via network on another server).