Like MySQL, the PostgreSQL database is quite popular among PHP developers.
Understandably, there are numerous well-documented PHP functions you can use in
conjunction with PostgreSQL; see the PHP Manual for a complete list. However,
you only need a few of these functions in order to make a simple connection and
select some data:
pg_connect - opens a connection to PostgreSQL; requires a hostname, database name, username and password.
pg_exec - executes the SQL statement.
pg_numrows - returns the number of rows in a result
pg_fetch_array - puts a SQL statement result row in an array.
pg_freeresult - frees the resources in use by the current connection.
pg_close - closes the current connection.
Just for argument's sake, let's pretend that a PostgreSQL database is already installed on your system, for which you have a valid username and password. Let's also assume that you've created a table on that database, called COFFEE_INVENTORY. The COFFEE_INVENTORY table has three columns: COFFEE_NAME, ROAST_TYPE and QUANTITY.
The rows in the COFFEE_INVENTORY table could be populated with data such as:
French Roast,dark,18
Kenya,medium,6
Ethiopian Harrar,medium,35
Sumatra,dark,8
Columbian,light,12
Now, let's do some PHP. Before you begin, you must know the name of the server on which the database resides, and have a valid username and password. Then, start your PHP code by creating a connection variable:
Test that a connection was established, and if it wasn't, print an error message and exit the program:
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}
Up to this point, you've told PHP to connect to a server and select a database. If you've made it this far, you can issue a SQL statement and hopefully see some results! Using the COFFEE_INVENTORY table, suppose you want to view your inventory, including the name of the coffee and the roast type, with the highest number of bags listed first. Create a variable that holds your SQL statement:
$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
FROM COFFEE_INVENTORY
ORDER BY QUANTITY DESC";
Next, create a variable to hold the result of the query, carried out by the pg_exec function. The pg_exec function takes two arguments: the connection and SQL statement variables you've just created.
$sql_result = pg_exec($connection,$sql);
Get the number of rows in the result set, using the pg_numrows function:
$num = pg_numrows($sql_result);
To format the results currently held in $sql_result, first separate the results by row, using the pg_fetch_array function.
$i = 0;
while ($i < $num) {
$row = pg_fetch_array($sql_result, $i);
// more code here...
$i++;
}
The while loop will create an array called $row for each record in the result set. To get the individual elements of the record (COFFEE_NAME, ROAST_TYPE, QUANTITY), create specific variables:
You'll probably want to print the results in a simple HTML table. Step back and place this statement before the while loop begins, to open the table tag and create the row headings:
Finally, you'll want to free up the resources used to perform the query, and close the database connection. Failing to do so could cause memory leaks and other nasty resource-hogging things to occur.
