First Steps - PHP

When you talk about PHP and databases, people tend to assumeyou're talking about MySQL. But hang on to your horses - difficultthough it may be to believe, PHP does include support for a number ofother databases. One of them is PostgreSQL, the *other* open-sourcedatabase - and this article tells you everything you need to know aboutusing it with PHP.

TABLE OF CONTENTS:

By: Vikram Vaswani, (c) Melonfire

Poor Best

Rating:

/ 52

May 01, 2002

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

<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

blog comments powered by Disqus

PHP ARTICLES

- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...
- PHP: Building Concrete Validators
- Sanitizing Input with PHP
- Executing Shell Commands with PHP
- Handling File Data with PHP
- File Security and Resources with PHP
- ArrayObject PHP Class Examples
- ArrayObject PHP Class: An Introduction
- Getting File System Data with PHP
- PHP Tools for Working with the File and Oper...
- Working with the File and Operating System w...
- PHP Proxy Patterns: Completing a Blog

Find More PHP Articles

DevShed

PHP Tutorials

First Steps - PHP

See Also:

Dev Shed Tutorial Topics: