# Postgres PHP

Postgres was originally developed at the University of California, Berkeley. It introduced many of the advanced object-relational concepts becoming popular in commercial databases. PostgreSQL is the most current incarnation of Postgres and is considered to be version 6. It implements almost all of the SQL specification. Best of all, it's free. As with other sections in this chapter, the descriptions of the functions can't stand alone. You will have to study PostgreSQL to fully understand how they work. More information may be found at the officialPostgreSQL. Zeev Suraski wrote the original Postgres extension. Jouni Ahto added support for large objects.

boolean pg_close(integer connection)
Use pg_close to close a connection to a PostgreSQL database. See pg_exec for an example of use.

integer pg_cmdtuples(integer result)
The pg_cmdtuples function returns the number of instances affected by the last query. This includes DELETE, INSERT, and UPDATE statements, but not SELECT statements.

<?
//connect to database
$Connection = pg_connect("", "", "", "", "leon");$Query = "INSERT INTO item ";
$Query .= "VALUES ('hammer', 15.00) "; //execute query$Result = pg_exec($Connection,$Query);
//tell user how many rows were inserted
print(pg_cmdtuples($Result) . " rows inserted.<BR>n"); //close connection pg_close($Connection);
?>

integer pg_connect(string connection)
integer pg_connect(string host, string port, string database)
integer pg_connect(string host, string port, string options, string database)
integer pg_connect(string host, string port, string options, string tty, string database)
The pg_connect function returns a connection identifier to a PostgreSQL database. The prototype displayed above is actually only one of several configurations for the arguments. If you provide only one argument, then it is assumed to be a connection string. This should be in the style expected by PostgreSQL. If you provide three arguments, pg_connect expects host, port, and database, in that order. If you provide four arguments, pg_connect expects host, port, options, and database. Finally, you may provide all five arguments in the order described in the last prototype. If blanks are used for any argument, a sensible default will be used. Compare this function to pg_pconnect. See pg_exec for an example of use.

string pg_dbname(integer connection)
Use pg_dbname to get the name of the current database. See pg_exec for an example of use.

string pg_errormessage(integer connection)
The pg_errormessage function returns the error message for the last database action. See pg_exec for an example of use.

integer pg_exec(integer connection, string query)
The pg_exec function executes a query on the given connection. A result identifier is returned.

<?
//connect to database
if(!($Connection = pg_connect("", "", "", "", "leon"))) { print("Could not establish connection.<BR>n"); exit; } //print information about connection print("Connection established<BR>n"); print("Host: " . pg_host($Connection) . "<BR>n");
print("Port: " . pg_port($Connection) . "<BR>n"); print("Database: " . pg_dbname($connection) . "<BR>n");
print("Options: " . pg_options($connection) . "<BR>n"); print("<BR>n"); //create query$Query = "SELECT * ";
$Query .= "FROM item"; //execute query if(!($Result = pg_exec($Connection,$Query)))
{
print("Could not execute query: ");
print(pg_errormessage($Connection)); print("<BR>n"); exit; } // print each row in a table print("<TABLE>n"); // print header row print("<TR>n"); for($Field=0; $Field pg_numfields($Result); $Field++) { print("<TD>"); print(pg_fieldname($Result, $Field) . " "); print(pg_fieldtype($Result, $Field)); print("(" . pg_fieldsize($Result, $Field) . ")"); print("</TD>n"); } print("/TR>n"); //loop through rows for($Row=0; $Row pg_numrows($Result); $Row++) { print("<TR>n"); for($Field=0; $Field pg_numfields($Result); $Field++) { print("<TD>"); if(pg_fieldisnull($Result, $Row,$Field))
{
$price = "NULL"; } else { print(pg_result($Result, $Row,$Field));
}
print("</TD>n");
}
print("</TR>n");
}
print("/TABLE>n");
// free the result and close the connection
pg_freeresult($Result); pg_close($Connection);
?>

array pg_fetch_array(integer result, integer row)
The pg_fetch_array function returns an array containing every field value for the given row. The values are indexed by number, starting with zero, and by column name. Each call to pg_fetch_array returns the next row, or FALSE when no rows remain. Comparethis function to pg_fetch_object and pg_fetch_row.

<?
//connect to database
if(!($Connection = pg_connect("", "", "", "", "leon"))) { print("Could not establish connection.<BR>n"); exit; } //create query$Query = "SELECT * ";
$Query .= "FROM item"; //execute query if(!($Result = pg_exec($Connection,$Query)))
{
print("Could not execute query: ");
print(pg_errormessage($Connection)); print("<BR>n"); exit; } //loop over each row while($Row = pg_fetch_array($Result,$Row))
{
print($Row["Name"] . "<BR>n"); } // free the result and close the connection pg_freeresult($Result);
pg_close($Connection); ?> object pg_fetch_object(integer result, integer row) The pg_fetch_object function returns an object with a property for every field. Each property is named after the field name. Each call to pg_fetch_object returns the next row, or FALSE when no rows remain. Compare this function to pg_fetch_array and pg_fetch_row. <? //connect to database if(!($Connection = pg_connect("", "", "", "", "leon")))
{
print("Could not establish connection.<BR>n");
exit;
}
//create query
$Query = "SELECT * ";$Query .= "FROM item";
//execute query
if(!($Result = pg_exec($Connection, $Query))) { print("Could not execute query: "); print(pg_errormessage($Connection));
print("<BR>n");
exit;
}
//loop over each row
while($Row = pg_fetch_object($Result, $Row)) { print("$Row->Name<BR>n");
}
// free the result and close the connection
pg_freeresult($Result); pg_close($Connection);
?>

array pg_fetch_row(integer result, integer row)
The pg_fetch_row function returns the values of all the fields in a row. The fields may are indexed by their field number, starting with zero. Each call to pg_fetch_object returns the next row, or FALSE when no rows remain. Compare this function to
pg_fetch_array, and pg_fetch_object.

<?
//connect to database
if(!($Connection = pg_connect("", "", "", "", "leon"))) { print("Could not establish connection.<BR>n"); exit; } //create query$Query = "SELECT * ";
$Query .= "FROM item"; //execute query if(!($Result = pg_exec($Connection,$Query)))
{
print("Could not execute query: ");
print(pg_errormessage($Connection)); print("<BR>n"); exit; } //loop over each row while($Row = pg_fetch_row($Result,$Row))
{
print("$Row[0]<BR>n"); } // free the result and close the connection pg_freeresult($Result);
pg_close($Connection); ?> boolean pg_fieldisnull(integer result, integer row, string field) The pg_fieldisnull function returns TRUE if the specified field is NULL. Fields are counted from 0. See pg_exec for an example of use. string pg_fieldname(integer result, integer field) The pg_fieldname function returns the name of the field in the result set specified by the field number, which starts counting at zero. See pg_exec for an example of use. integer pg_fieldnum(integer result, string field) The pg_fieldnum function returns the number of the field given its name. Numbering begins with 0. If an error occurs, negative one (-1) is returned. <? print(pg_fieldnum($Result, "name"));
?>

integer pg_fieldprtlen(integer result, integer row, string field)
The pg_fieldprtlen function returns the printed length of a particular field value. You may specify the field either by number, starting at zero, or by name.

<?
print(pg_fieldprtlen($Result,$Row, 2));
?>

integer pg_fieldsize(integer result, string field)
The pg_fieldsize function returns the size of the field, which may be specified by name or number. Fields are numbered from zero. See pg_exec for an example of use.

string pg_fieldtype(integer result, string field)
The pg_fieldtype function returns the type of the specified field. The field argument may be a number or a name. Fields are numbered starting with zero. See pg_exec for an example of use.

boolean pg_freeresult(integer result)
The pg_freeresult function frees any memory associated with the result set. Ordinarily it is not necessary to call this function, as all memory will be cleared when the script ends. See pg_exec for an example of use.

integer pg_getlastoid()
The pg_getlastoid function returns the object ID (OID) of the last object inserted into a table if the last call to pg_exec was an INSERT statement. Negative one (-1) is returned if there is an error.

<?
//connect to database
$Connection = pg_connect("", "", "", "", "leon");$Query = "INSERT INTO item (name, price) ";
$Query .= "VALUES ('hammer', 15.00)";$Result = pg_exec($Connection,$Query);
print("ID of inserted item: " . pg_getlastoid() . "<BR>n");
pg_close($Connection); ?> string pg_host(integer connection) The pg_host function returns the name of the host for the connection. See pg_exec for an example of use. pg_loclose(integer file) The pg_loclose function closes a large object. The file argument is a file identifier returned by pg_loopen. See pg_loopen for an example of use. integer pg_locreate(integer connection) The pg_locreate function creates a large object and returns the OID. The object is created with both read and write access. <?$Object = pg_locreate($Connection); ?> integer pg_loopen(integer connection, integer object, string mode) The pg_loopen function opens a large object. The object argument is a valid large object ID and the mode may be one of r, w, rw. A file identifier is returned. <?$Object = pg_locreate($Connection);$File = pg_loopen($Connection,$Object, "r");
pg_loclose($File); ?> string pg_loread(integer file, integer length) The pg_loread function returns the large object as a string. The length argument specifies a maximum length to return. <?$Object = pg_locreate($Connection);$File = pg_loopen($Connection,$Object, "r");
$Contents = pg_loread($File, 4096);
?>

The pg_loreadall function reads an entire large object and sends it directly to the browser.

<?
$File = pg_loopen($Connection, $Object, "r"); pg_loreadall($File);
?>

Use pg_lounlink to delete a large object.

<?
$Object = pg_locreate($Connection);
$File = pg_loopen($Connection, $Object, "r"); pg_lounlink($File, $Object); ?> pg_lowrite(integer file, string buffer) The pg_lowrite function writes the named buffer to the large object. <?$Object = pg_locreate($Connection);$File = pg_loopen($Connection,$Object, "w");
pg_lowrite($File, "some text"); ?> integer pg_numfields(integer result) The pg_numfields function returns the number of fields in the result set. See pg_exec for an example of use. integer pg_numrows(integer result) Use pg_numrows to get the number of rows in the result set. See pg_exec for an example of use. string pg_options(integer connection) The pg_options function returns the options used when the connection was opened. See pg_exec for an example of use. integer pg_pconnect(string host, string port, string options, string tty, string database) The pg_pconnect function operates identically to pg_connect, except that a persistent connection is created. This connection will last as long as the server process, so it may be recycled. This saves the overhead time of opening a connection. <?$Connection = pg_pconnect("", "", "", "", "leon");
?>

integer pg_port(integer connection)
The pg_port function returns the port number used in the pg_connect function. See pg_exec for an example of use.

string pg_result(integer result, integer row, string field)
Use pg_result to get the value of a specific field in a result set. Rows and fields are numbered from zero, but fields may also be specified by name. See pg_exec for an example of use.

string pg_tty(integer connection)
The pg_tty function returns the tty name used for debugging and supplied with the pg_connect fuction. See pg_exec for an example of use.

« Previous Topics
Inter Base
Msql
Mysql

0