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?
Next: Digging Deeper >>
More PHP Articles
More By Vikram Vaswani, (c) Melonfire
