PHP: pg_query_params - Manual

downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | conferences | my php.net

search for in the

pg_query pg_put_line

Last updated: Sat, 27 Jan 2007

view this page in

# pg_query_params

(PHP 5 >= 5.1.0RC1)

pg_query_params -- Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text.

## Описание

resource pg_query_params ( resource connection, string query, array params )
resource pg_query_params ( string query, array params )

Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text.

pg_query_params() is like pg_query(), but offers additional functionality: parameter values can be specified separately from the command string proper. pg_query_params() is supported only against PostgreSQL 7.4 or higher connections; it will fail when using earlier versions.

If parameters are used, they are referred to in the `query` string as $1, $2, etc. `params` specifies the actual values of the parameters. A `NULL` value in this array means the corresponding parameter is SQL `NULL`.

The primary advantage of pg_query_params() over pg_query() is that parameter values may be separated from the `query` string, thus avoiding the need for tedious and error-prone quoting and escaping. Unlike pg_query(), pg_query_params() allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.)

## Список параметров

`connection`

  PostgreSQL database connection resource. When `connection` is not present, the default connection is used. The default connection is the last connection made by pg_connect() or pg_pconnect().

`query`

  The parameterised SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not allowed.) If any parameters are used, they are referred to as $1, $2, etc.

`params`

  An array of parameter values to substitute for the $1, $2, etc. placeholders in the original prepared query string. The number of elements in the array must match the number of placeholders.

## Возвращаемые значения

A query result resource on success, or `FALSE` on failure.

## Примеры

Пример 1. Using pg_query_params()

`<?php
// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary");
// Find all shops named Joe's Widgets. Note that it is not necessary to
// escape "Joe's Widgets"
$result = pg_query_params($dbconn, 'SELECT * FROM shops WHERE name = $1', array("Joe's Widgets"));
// Compare against just using pg_query
$str = pg_escape_string("Joe's Widgets");
$result = pg_query($dbconn, "SELECT * FROM shops WHERE name = '{$str}'");
?>`

## Смотрите также

pg_query()

add a note User Contributed Notes
pg_query_params
dt309 at f2s dot com
22-Dec-2006 12:11
`If you need to provide multiple possible values for a field in a select query, then the following will help.
<?php
// Assume that $values[] is an array containing the values you are interested in.
$values = array(1, 4, 5, 8);
// To select a variable number of arguments using pg_query() you can use:
$valuelist = implode(', ', $values);
$query = "SELECT * FROM table1 WHERE col1 IN ($valuelist)";
$result = pg_query($query)
or die(pg_last_error());
// You may therefore assume that the following will work.
$query = 'SELECT * FROM table1 WHERE col1 IN ($1)';
$result = pg_query_params($query, array($valuelist))
or die(pg_last_error());
// Produces error message: 'ERROR: invalid input syntax for integer'
// It only works when a SINGLE value specified.
// Instead you must use the following approach:
$valuelist = '{' . implode(', ', $values . '}'
$query = 'SELECT * FROM table1 WHERE col1 = ANY ($1)';
$result = pg_query_params($query, array($valuelist));
?>
The error produced in this example is generated by PostGreSQL.
The last method works by creating a SQL array containing the desired values. 'IN (...)' and ' = ANY (...)' are equivalent, but ANY is for working with arrays, and IN is for working with simple lists.`
mledford
04-Oct-2006 08:18
`If you are trying to replicate the function pg_query_params, you might also want to support NULL values. While is_int returns true for a NULL value, the formatting for the SQL.
function pg_query_params( $db, $query, $parameters ) {
// Escape parameters as required & build parameters for callback function
global $pg_query_params__parameters;
foreach( $parameters as $k=>$v ) {
if ( is_null($v) ) {
$parameters[$k] = 'NULL';
} else {
$parameters[$k] = ( is_int( $v ) ? $v : "'".pg_escape_string( $v )."'" );
}
}
$pg_query_params__parameters = $parameters;
// Call using pg_query
return pg_query( $db, preg_replace_callback( '/\$([0-9]+)/', 'pg_query_params__callback', $query));
}`
cc+php at c2se dot com
02-Sep-2006 05:17
`This is a useful function for preventing SQL injection attacks, so, for those of us who are not yet able to upgrade to PHP5.1, here is a replacement function which works similarly on older versions of PHP...
<?php # Parameterised query implementation for Postgresql and older versions of PHP
if( !function_exists( 'pg_query_params' ) ) {
function pg_query_params__callback( $at ) {
global $pg_query_params__parameters;
return $pg_query_params__parameters[ $at[1]-1 ];
}
function pg_query_params( $db, $query, $parameters ) {
// Escape parameters as required & build parameters for callback function
global $pg_query_params__parameters;
foreach( $parameters as $k=>$v )
$parameters[$k] = ( is_int( $v ) ? $v : "'".pg_escape_string( $v )."'" );
$pg_query_params__parameters = $parameters;
// Call using pg_query
return pg_query( $db, preg_replace_callback( '/\$([0-9]+)/', 'pg_query_params__callback', $query ) );
}
}
// Example: pg_query_params( $db_resource, "SELECT * FROM table WHERE col1=$1 AND col2=$2", array( 42, "It's ok" ) );
?>`
add a note
pg_query pg_put_line

Last updated: Sat, 27 Jan 2007
show source | credits | stats | sitemap | contact | advertising | mirror sites
Copyright © 2001-2007 The PHP Group
All rights reserved.
This mirror generously provided by: Yahoo! Inc.
Last updated: Sat Mar 10 10:36:09 2007 PST