PHP | PostgreSQL Essential Reference

I l @ ve RuBoard

PHP is a scripting language used for building dynamic web pages. It contains a number of advanced features that rival commercial options such as ASP and ColdFusion.

It contains several built-in database interfaces, including functions specific for communicating with both MySQL and PostgreSQL. The following is a list of the functions specific to PostgreSQL:

```
 pg_close(  connection_id  ) 
```
Description: Closes a PostgreSQL connection. Returns false if not a valid connection; otherwise , returns true .
```
 pg_cmdtuples(  result_id  ) 
```
Description: Returns the number of instances affected by an INSERT , UPDATE , or DELETE query. If no tuple was affected, the function will return 0.

 pg_connect([  host  ], [  port  ], [  options  ], [  tty  ], dbname)

Description: Opens a connection to a PostgreSQL database. Returns a connection index on success or false if the connection could not be made.

Example:
```
 <?php  $dbconn = pg_Connect ("dbname=newriders");  $dbconn2 = pg_Connect ("host=localhost port=5432 dbname=newriders");  ?> 
```
```
 pg_dbname(  connection_id  ) 
```
Description: Returns the name of the database connected to the specified connection index. Otherwise, it returns false if the connection is not a valid connection index.

 pg_end_copy([  resource connection  ])

Description: Synchronizes a front-end application with the back end after doing a copy operation. It must be issued; otherwise, the back end might get out of sync with the front end.
```
 pg_errormessage(  connection_id  ) 
```
Description: Returns a string containing any error messages from previous database operations; otherwise, returns false .
```
 pg_exec(  connection_id, query  ) 
```
Description: Returns a result index following the execution of the SQL commands contained in the query . Otherwise, it returns a false value. From a successful execution, the return value of this function is an index to be used to access the results from other PostgreSQL functions.

 pg_fetch_array(  result_id  ,  row  , [  result_type  ])

Description: Returns an array that corresponds to the fetched row; otherwise, returns false if there are no more rows.

pg_fetch_array() is an extended version of pg_fetch_row() . In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.

For instance:

 <?php  $conn = pg_pconnect ("dbname=newriders");  $rst = pg_exec ($conn, "SELECT * FROM authors");  if (!$rst) {     echo "An error occured.\n";      exit;  }  $rst_array = pg_fetch_array ($rst, 0);  echo $rst_array[0] . " First Row - First Field\n";  $rst_array = pg_fetch_array ($rst, 1);  echo $rst_array["author"] . " Second Row - Author Field\n";  ?>

```
 pg_fetch_object(  result_id  ,  row  , [  result_type  ]) 
```
Description: Returns the properties that correspond to the fetched row; otherwise, returns false if there are no more rows.

pg_fetch_object() is similar to pg_fetch_array() with one difference ”an object is returned instead of an array. As a result, you can only access the data by the field names, not by their ordinal numbers .
```
 pg_fetch_row(  result_id  ,  row  ) 
```
Description: Returns the specified row as an array. Each result column is stored in an array offset, starting at offset 0.
```
 pg_fieldisnull(  result_id  ,  row  ,  field  ) 
```
Description: Returns 0 if the field in the given row is not NULL . Returns 1 if the field in the given row is NULL . Field can be specified as number or fieldname.
```
 pg_fieldname(  result_id  ,  field  _  number  ) 
```
Description: Returns the field name of the corresponding field-index number specified. Field numbering starts from 0.
```
 pg_fieldnum(  result_id  , field_name) 
```
Description: Returns the field number for the column name specified.
pg_fieldprtlen( result_id , row field_name)

Description: Returns the number of characters of a specific field in the given row.
```
 pg_fieldsize(  result_id  ,  field_number  ) 
```
Description: Returns the number of bytes that the internal storage size of the given field number occupies.A field size of “1 indicates a variable-length field.
```
 pg_fieldtype(  result_id  ,  field_number  ) 
```
Description: Returns a string containing the data type of the field represented by the field number supplied.
```
 pg_freeresult(  result_id  ) 
```
Description: When called, all result memory will automatically be freed. Generally, this is only needed when you are certain you are running low on memory because PHP will automatically free memory once a connection is closed.
```
 pg_getlastoid(  result_id  ) 
```
Description: Returns the last OID assigned to an inserted tuple. The result identifier is used from the last command sent via pg_exec() .
```
 pg_host(  connection_id  ) 
```
Description: Returns the hostname of the connected PostgreSQL server.
```
 pg_loclose(  file_id  ) 
```
Description: Closes a large object. file_id is a file descriptor for the large object from pg_loopen() .
```
 pg_locreate(  connection_id  ) 
```
Description: Creates a large object and returns its OID.
```
 pg_loexport(  oid  ,  file_path  [,  int connection_id  ]) 
```
Description: Specifies the object ID of the large object to export, and the filename argument specifies the pathname of the file.
```
 pg_loimport(  file_path  , [  connection_id  ]) 
```
Description: Specifies the pathname of the file to be imported as a large object. All handling of large objects in PostgreSQL must happen inside a transaction.

 pg_loopen(  connection_id  ,  obj  _  oid  ,  string mode  )

Description: Opens a large object and returns file descriptor. The file descriptor encapsulates information about the connection. Do not close the connection before closing the large object file descriptor. obj_oid specifies a valid large object OID. The mode can be "r","w", or "rw".
```
 pg_loread(  file_id  ,  length  ) 
```
Description: Reads the specified length of bytes from a large object and returns it as a string. The file_id specifies a valid large object file descriptor.
```
 pg_loreadall(  file_id  ) 
```
Description: Reads a large object and passes it straight to the browser.
```
 pg_lounlink(  connection_id  ,  large_obj_id  ) 
```
Description: Deletes a large object with the OID specified in large_obj_id .
```
 pg_lowrite(  file_id  ,  buffer  ) 
```
Description: Writes to a large object from the specified buffer. Returns the number of bytes actually written or false in the case of an error. file_id refers to the file descriptor for the large object from pg_loopen() .
```
 pg_numfields(  result_id  ) 
```
Description: Returns the number of fields in a result. The result_id is a valid result identifier returned by pg_exec() .
```
 pg_numrows(  result_id  ) 
```
Description: Returns the number of rows in a result. The result_id is a valid result identifier returned by pg_exec() .
```
 pg_options(  connection_id  ) 
```
Description: Returns a string of the specified options valid on the provided connection identifier.

 pg_pconnect([  host  ], [  port  ],[  tty  ], [  options  ]  dbname  , [  user  ], [  password  ])

Description: Opens a persistent connection, needed by other PHP functions, to a PostgreSQL database.

```
 pg_port(  connection_id  ) 
```
Description: Returns the port number of the PostgreSQL server.

 pg_put_line(  connection_id  ,  data  )

Description: Sends a NULL - terminated string to the PostgreSQL server. This is useful, for example, for very high-speed inserting of data into a table, initiated by starting a PostgreSQL copy operation.

For instance:

 <?php  $conn = pg_pconnect ("dbname=foo");  pg_exec($conn, "create table bar (a int4, b char(16), d float8)");  pg_exec($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);  ?>

```
 pg_result(  result_id  ,  row_number  ,  fieldname  ) 
```
Description: Returns values from a result identifier produced by pg_exec() . The row_number and fieldname specify what elements of the array are returned. Instead of naming the field, you can use the field index as an unquoted number.
```
 pg_set_client_encoding(  connection_id  ,  encoding  ) 
```
Description: Sets the client encoding type. The encoding can be SQL_ASCII , EUC_JP , EUC_CN , EUC_KR , EUC_TW , UNICODE , MULE_INTERNAL , LATIN1 LATIN9 , KOI8 , WIN , ALT , SJIS , BIG5 , or WIN1250 . Returns 0 if success or “1 if error.
```
 pg_client_  encoding  (  connection_id  ) 
```
Description: Returns the client encoding as a string. Will be one of the values that can be set with the pg_set_client_encoding function.
```
 pg_trace(  filename  , [  mode  , [  connection_id  ]]) 
```
Description: Enables tracing of the PostgreSQL front-end/back-end communication to a debugging file. Useful aid in debugging communication problems.
```
 pg_tty(  connection_id  ) 
```
Description: Returns the tty name that server-side debugging output is being sent.
```
 pg_untrace(  connection_id  ) 
```
Description: Stops tracing started by pg_trace .

I l @ ve RuBoard