PHP and PostgreSQL - First Steps (
Page 3 of 9 )
Now, how about doing the same thing with PHP - fire a SELECT query at the
database, and display the results in an HTML page?
<html>
<head><basefont face="Arial"></head>
<body>
<?
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "postgres";
$pass = "postgres";
$db = "test";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECT * FROM addressbook";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// get the number of rows in the resultset
$rows = pg_num_rows($result);
echo "There are currently $rows records in the database.";
// close database connection
pg_close($connection);
?>
</body>
</html>
And here's what's the output looks like:
There are currently 3 records in the database.
As you can see, using PHP to get data from a PostgreSQL database involves
several steps, each of which is actually a pre-defined PHP function. Let's
dissect each step:
1. The first thing to do is specify some important
information needed to establish a connection to the database server. This
information includes the server name, the username and password required to gain
access to it, and the name of the database to query. These values are all set up
in regular PHP variables.
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "postgres";
$pass = "postgres";
$db = "test";
2. In order to begin communication with the PostgreSQL database server,
you first need to open a connection to the server. All communication between PHP
and the database server takes place through this connection.
In order to
initialize this connection, PHP offers the pg_connect() function.
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
The function requires a connection string containing one or more
parameters - these could include the host name, port, database name, user name
and user password. Here are some examples of valid connection strings:
$connection = pg_connect ("host=myhost dbname=mydb");
$connection = pg_connect ("host=myhost dbname=mydb user=postgres
password-postgres");
$connection = pg_connect ("host=myhost port=5432 dbname=mydb
user=postgres password-postgres");
This function then returns a "link identifier", which is stored in the
variable $connection; this identifier is used throughout the script when
communicating with the database.
3. Now that you have a connection to the
database, it's time to send it a query via the pg_query() function. This
function needs two parameters: the link identifier for the connection and the
query string.
// generate and execute a query
$query = "SELECT * FROM addressbook";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
The result set returned by the function above is stored in the variable
$result.
4. This result set may contain, depending on your query, one or
more rows or columns of data. You then need to retrieve specific sections or
subsets of the result set with different PHP functions - the one used here is
the pg_num_rows() function, which counts the number of rows and returns the
value needed.
// get the number of rows in the resultset
$rows = pg_num_rows($result);
There are several other alternatives you can use at this point, which will
be explained a little further down.
5. Finally, each database connection
occupies some amount of memory - and if your system is likely to experience
heavy load, it's a good idea to use the pg_close() function to close the
connection and free up the used memory.
// close database connection
pg_close($connection);
Simple, ain't it?
