Executing SQL Statements: query

You execute a SQL query with the pg_query function:

result_resource pg_query ( resource connection, string query )

pg_query() returns a query result resource (just refered to as resource below) if query could be executed or FALSE on failure or if connection is not valid (details about the error can be retrieved using the pg_last_error() function).
The query result resource can be used to access the results from other PostgreSQL functions such as pg_fetch_row().

The connection must be a valid connection that was returned by pg_connect().
If connection is not set, the last connection made by pg_connect() is used
(omitting the connection is often a cause of hard to find bug).

Example:

<? $con = "dbname=workshop user=felix"; $dbh = pg_connect( $con ); if( $dbh ) { echo "connected\n"; $res = pg_query( $dbh, "SELECT * FROM stud ;"); print_r ( pg_fetch_row( $res ) ); echo ( "close: ".pg_close( $dbh )."\n" ); pg_free_result ( $res ); } else { echo "connection error \n"; } //Output: // Array ( [0] => 1 // [1] => fred // [2] => 2 // [3] => bio // [4] => m ) ?>
bool pg_free_result ( resource result):
Frees the result memory and returns TRUE on success or FALSE on failure.
pg_free_result() only needs to be called if you are worried about using too much memory while your script is running. All result memory will automatically be freed when the script is finished.

array pg_fetch_row ( resource result, int row):
Fetches one row of data from the result result resource. The specified row (starting at offset 0) or the current one is returned as an indexed array (field index as key). It returns FALSE if there are no more rows. Calling pg_fetch_row() will increment internal row counter by 1.

array pg_fetch_array ( resource result [, int row [, int result_type]]):
Returns an array that corresponds to the fetched row (tuples/records) or FALSE, if there are no more rows.

pg_fetch_array() is an extended version of pg_fetch_row(). As pg_fetch_row, it stores the data with numeric indices (field index as key) and additionally with associative indices (field names as key).

result_type is an optional parameter that controls the return value. It is a constant and can take the values:

PGSQL_NUM   : Return as pg_affected_rows, field index as key.

PGSQL_ASSOC : Only field names as key.

PGSQL_BOTH (default) : Both field name/index as keys.

pg_fetch_array() is NOT significantly slower than pg_fetch_row().

Example:

<?php <? $con = "dbname=workshop user=felix"; $dbh = pg_connect( $con ); if( $dbh ) { echo "connected\n"; $res = pg_query( $dbh, "SELECT * FROM stud ;"); while ( $row = pg_fetch_array( $res ) ) { print_r ( $row ); print " \n"; } echo ( "close: ".pg_close( $dbh )."\n" ); pg_free_result ( $res ); } else { echo "connection error \n"; } //Output (only first record): // Array ( [0] => 1 [id] => 1 // [1] => fred [name] => fred // [2] => 2 [semester] => 2 // [3] => bio [diploma] => bio // [4] => m [gender] => m ) // Array ( [0] => 3 [id] => 3 // [1] => tom [name] => tom // ..... ?>

array pg_fetch_all ( resource result [, int row]):
Returns an array that contains all remaining rows (tuples/records) in the result resource or FALSE, if there are no more rows. Example:

<?php $arr = pg_fetch_all ($result, 0, PGSQL_NUM); var_dump($arr); ?>

object pg_fetch_object ( resource result [, int row [, int result_type]]):
pg_fetch_object() is similar to pg_fetch_array(), with one difference the an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field names, and not by their offsets (numbers are illegal property names).

mixed pg_fetch_result ( resource result, int row, mixed field):
Returns single values from a result resource returned by pg_query(),
The row and field specify what cell in the table of results to return:
row is the result row number (integer, starting from 0) and
field is the field index (integer, starting from 0) or the field name (string).

Alternative Query Functions

bool pg_put_line ( [resource connection, string data]):
Sends a NULL-terminated string to the PostgreSQL backend server.
This is useful for example for inserting of data into a table with PostgreSQL copy-operation. That final NULL-character is added automatically.
It returns TRUE on success or FALSE on failure.

bool pg_end_copy ( [resource connection]):
Syncs the PostgreSQL front-end (usually a web server process) with the PostgreSQL server after doing a copy operation performed by pg_put_line(). pg_end_copy() must be issued, otherwise the PostgreSQL server may get out of sync with the front-end and will report an error. Returns TRUE on success or FALSE on failure.

Example (copy from stdin to insert data into a table):

<?php $conn = pg_pconnect ("dbname=foo"); pg_query($conn, "CREATE TABLE bar (a int4, b char(16), d float8)"); pg_query($conn, "COPY bar FROM stdin"); pg_put_line($conn, "3\thello world\t4.5\n"); pg_put_line($conn, "4\tgoodbye world\t7.11\n"); pg_put_line($conn, "\\.\n"); pg_end_copy($conn); ?>

array pg_convert ( resource connection, string table_name, array assoc_array [, int options]):
Checks and converts assoc_array suitable for SQL statement.

array pg_select ( resource connection, string table_name, array assoc_array [, int options]):
Selects records specified by assoc_array.

bool pg_insert ( resource connection, string table_name, array assoc_array [, int options]):
Inserts assoc_array which has field=>value into table.

mixed pg_delete ( resource connection, string table_name, array assoc_array [, int options]):
Deletes record condition specified by assoc_array which has field=>value.
Example (pg_delete):

<?php $db = pg_connect ('dbname=foo'); // This is safe, since $_POST is converted automatically $res = pg_delete($db, 'post_log', $_POST); if ($res) { echo "POST data is deleted: $res\n"; } else { echo "User must have sent wrong inputs\n"; } ?>