#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    12

    Parametrized Query From Php To Postgresql


    hello!
    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
    structure

    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!
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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).
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo