Postgre SQL, Notas de estudo de Análise de Sistemas de Engenharia

Baixe Postgre SQL e outras Notas de estudo em PDF para Análise de Sistemas de Engenharia, somente na Docsity!

By Vikram Vaswani

This article copyright Melonfire 2000−2002. All rights reserved.

Table of Contents

A Matter Of Choice............................................................................................................................................

Getting Started....................................................................................................................................................

First Steps............................................................................................................................................................

Digging Deeper....................................................................................................................................................

Different Strokes...............................................................................................................................................

Rolling Around..................................................................................................................................................

Catching Mistakes.............................................................................................................................................

A Well−Formed Idea........................................................................................................................................

Surfing The Web...............................................................................................................................................

PHP and PostgreSQL

i

Getting Started

Before we get started, you need to make sure that you have everything you need to successfully use PHP with PostgreSQL. Here's your cheat sheet:

1. The latest version of PostgreSQL, available from http://www.postgresql.org (this article uses version 7.1)
2. A Web server which supports PHP, available from http://www.php.net/ (this article uses Apache 1.3. with PHP 4.2.0)

Note that your PHP build must support PostgreSQL in order for the examples in this article to work correctly. You can include PostgreSQL support in your PHP build by adding the "−−with−pgsql" configuration parameter when compiling the package. Note also that PHP 4.2.0 and better contains fairly extensive changes to the function names used in the language's PostgreSQL module; this article uses the new function names and assumes that you're running PHP 4.2.x. Drop by http://www.php.net/manual/en/ref.pgsql.php for more information on the changes, and the corresponding function names for older versions.

I'm not going to get into the details of configuring and installing either PostgreSQL or PHP here − the documentation included with both those packages has more than enough information to get you started, and the accompanying Web sites contain lots of troubleshooting information should you encounter problems. In case you don't already have these packages installed on your development system, drop by the Web sites listed above, get yourself set up and come back once you're done.

Assuming that you have a properly configured and installed setup, the first step is to start both the database server and the Web server.

[postgres@medusa] $ /usr/local/pgsql/bin/postmaster −i −D /usr/local/pgsql/data & DEBUG: database system was shut down at 2002−04−12 19:18: IST DEBUG: CheckPoint record at (0, 1694744) DEBUG: Redo record at (0, 1694744); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 643; NextOid: 18778 DEBUG: database system is in production state [postgres@medusa] $ su − root [root@medusa] $ /usr/local/apache/bin/apachectl start Starting httpd [OK] [root@medusa] $

Note that the PostgreSQL server must be started as the special "postgres" user created during the installation process, and the startup invocation must include the additional "−i" parameter to allow TCP/IP connections to the server.

The next step is to create an example database table that can be used for the code listings in this article. Here's

Getting Started 2

what the SQL dump file looks like:

CREATE TABLE addressbook (id serial, name varchar(255), address text, tel varchar(50), email varchar(255));

INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Bugs Bunny', 'The Rabbit Hole, Looney Toons, USA', '123 4567', '[email protected]');

INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Robin Hood', 'Sherwood Forest', 'None', '[email protected]');

INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Sherlock Holmes', '221B Baker Street, London 16550, England', ' 1822', '[email protected]');

You can import this data into PostgreSQL by dropping to a shell and using the following command:

[postgres@medusa] $ /usr/local/pgsql/bin/createdb test [postgres@medusa] $ /usr/local/pgsql/bin/psql −d test −f /home/postgres/addressbook.sql

Now check whether or not the data has been successfully imported with a SELECT query (the SELECT SQL statement is used to retrieve information from a database) via the interactive PostgreSQL monitor program "psql".

[postgres@medusa] $ /usr/local/pgsql/bin/psql −d test Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms \h for help with SQL commands ? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit

test=# SELECT COUNT(*) FROM addressbook; count

PHP and PostgreSQL

Getting Started 3

First Steps

Now, how about doing the same thing with PHP − fire a SELECT query at the database, and display the results in an HTML page?

// 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);

And here's what's the output looks like:

First Steps 5

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";

1. 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.

PHP and PostgreSQL

First Steps 6

Digging Deeper

Now, that was a very basic example. How about something a little more useful?

This next example will query the database, return the list of addresses, and display them all as a neatly−formatted list.

Address Book

{ // iterate through resultset for ($i=0; $i<$rows; $i++) { $row = pg_fetch_row($result, $i); ?>

Digging Deeper 8

No data available.

Here's what the output looks like:

As in the previous example, the script first sets up a connection to the database. The query is formulated and the result set is returned to the browser. In this case, since I'm dealing with multiple rows of data, I've used the pg_fetch_row() function in combination with a "for" loop to iterate through the result set and print the data within each row.

The pg_fetch_row() function returns the columns within each row as array elements, making it possible to easily access the values within a record. By combining it with a "for" loop, I can easily process the entire result set, thereby displaying all returned records as list items.

Finally, in case you're wondering, the pg_last_error() function returns the last error generated by the server − combined with die(), this provides an effective, if primitive, debugging mechanism.

PHP and PostgreSQL

Digging Deeper 9

?>

No data available.

Most of the magic here lies in the call to pg_fetch_array(),

$row = pg_fetch_array($result, $i, PGSQL_ASSOC);

which returns every row as a hash with keys corresponding to the column names.

PHP also allows you to access individual fields within a row as object properties rather than array elements, via its pg_fetch_object() function. Take a look:

Address Book

// database access parameters // alter this as per your configuration $host = "localhost"; $user = "postgres"; $pass = "postgres"; $db = "test";

// open a connection to the database server

PHP and PostgreSQL

Different Strokes 11

$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 name, address FROM addressbook ORDER BY name"; $result = pg_query($connection, $query) or die("Error in query: $query. ". pg_last_error($connection));

// get the number of rows in the resultset // this is PG−specific $rows = pg_num_rows($result);

// if records present if ($rows > 0) { // iterate through resultset for ($i=0; $i<$rows; $i++) { $row = pg_fetch_object($result, $i); ?> name; ?> address; ?>

No data available.

PHP and PostgreSQL

Different Strokes 12

## Rolling Around

One of the nice things about PostgreSQL − and one of the reasons why many developers prefer it over MySQL − is its support for transactions (in case you didn't know, this refers to the ability to group a series of SQL statements together so that they are executed either together, or not at all). You can find more information about transactions online, at http://www.postgresql.org/idocs/index.php?tutorial−transactions.html − and if you already know what they are, here's an example which demonstrates how they may be used in a PHP context with PostgreSQL.

// 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"); }

// begin a transaction block $query = "BEGIN WORK"; $result = pg_query($connection, $query) or die("Error in query: $query. ". pg_last_error($connection));

// generate some queries $query = "INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Spiderman', 'The Web, Somewhere In Your Neighborhood', 'None', '[email protected]')"; $result = pg_query($connection, $query) or die("Error in query: $query. ". pg_last_error($connection));

$query = "INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Bruce Wayne', 'Gotham City', '64928 34585', '[email protected]')"; $result = pg_query($connection, $query) or die("Error in query: $query.

Rolling Around 14

". pg_last_error($connection));

// now roll them back $query = "ROLLBACK"; // if you want to commit them, comment out the line above // and uncomment the one below // $query = "COMMIT"; $result = pg_query($connection, $query) or die("Error in query: $query. ". pg_last_error($connection));

// now check to see how many records are there in the table // and print this $query = "SELECT * FROM addressbook"; $result = pg_query($connection, $query) or die("Error in query: $query. ". pg_last_error($connection)); $rows = pg_num_rows($result); echo "There are currently $rows records in the database";

// close database connection pg_close($connection);

Technically, there's nothing new here − this script uses the same functions you've seen in preceding examples. The difference lies in the use of multiple SQL statements to begin and end a transaction block, and in the use of COMMIT and ROLLBACK statements to commit and erase records from the database.

PHP and PostgreSQL

Rolling Around 15

And here's the output:

Warning: pg_query() query failed: ERROR: parser: parse error at or near "selecta" in /usr/local/apache/htdocs/e.php on line 23 Error in query: SELECTA * FROM addressbook. ERROR: parser: parse error at or near "selecta"

The pg_last_error() function displays the last error returned by PostgreSQL. Turn it on, and you'll find that it can significantly reduce the time you spend fixing bugs.

PHP and PostgreSQL

Catching Mistakes 17

A Well−Formed Idea

Finally, how about one more example to wrap things up? This next script contains a form which can be used to enter new addresses into the table, together with a form processor that actually creates and executes the INSERT statement.

Address Book

Name:

Address:

Tel:

Email:

} else { // form submitted // prepare to insert data

// database access parameters // alter this as per your configuration $host = "localhost"; $user = "postgres"; $pass = "postgres"; $db = "test";

// open a connection to the database server

A Well−Formed Idea 18