ONLamp.com
oreilly.comSafari Books Online.Conferences.

advertisement


[ Mammoth ]

Making the PostgreSQL and PHP Connection

by Joshua D. Drake, coauthor of Practical PostgreSQL
01/24/2002

PHP is the most widely used Apache module available and provides a strong platform for Web application development. However, most people who use PHP with open source databases use PHP with MySQL. As an invitation to using PostgreSQL, I have written the following article on using PHP and PostgreSQL. There are already a lot of PHP articles out there on the Web, so I'm not going to cover the basic concepts here. Instead, I'll jump right to the chase and show the fundamentals of using PHP with PostgreSQL.

Making the Connection

There are two direct ways to make a connection to PostgreSQL using PHP. They are pg_connect() and pg_pconnect(). The syntax of the two connections is very similar. However, functionally, they are a bit different. The pg_connect() function will create a new connection for each instance of the function. On the other hand, pg_pconnect() will reuse an existing connection to PostgreSQL if one is available.

The following is a simple example of opening a database connection to PostgreSQL:

<?
  $connection = pg_connect("dbname=mydb user=myuser host=localhost");
?>

To do the same with pg_pconnect, the syntax is nearly identical:

<?
  $connection = pg_pconnect("dbname=mydb user=myuser host=localhost");
?>

The previous examples open a connection to the database "mydb" on the local host, as the "myuser" user. If the user= parameter is omitted, the user that will be authenticated is the system user that your Web server is running as (for example, "nobody").

The next step would be to actually send a command or query to the PostgreSQL database. This is done in conjunction with the pg_connect() function by using the pg_exec() function.

<?
  $connection = pg_connect("dbname=mydb user=myuser");
  $myresult = pg_exec($connection, "SELECT * FROM id,username,fname,lname where id > 100");
?<

The previous example will connect and execute a query, but not much else. You only have to pass the $connection variable if you have more than one connection to choose from (for example, if you have opened two connections with the pg_connect() function). Therefore, the following code would function identically, if you intend on having only one connection:

<?
  pg_connect("dbname=mydb user=myuser");
  $myresult = pg_exec("SELECT * FROM id,username,fname,lname where id > 100");
?>

If you wanted to make this code a bit more robust, you could add some exception handling:

<?
  $connection = pg_connect("dbname=mydb user=myuser");
  if (!$connection) {
    print("Connection Failed.");
    exit;
  }
  $myresult = pg_exec($connection, "SELECT * FROM id,username,fname,lname where id > 100");
?>

Now we have a connection to PostgreSQL that will alert you if the connection fails. Also, we are executing a simple query. We are not, however, processing the results of the query we sent. Thus, we will want to add some logic on the result set as shown in the following bit of code:

<?
  // make our connection
  $connection = pg_connect("dbname=mydb user=myuser");
  
  // let me know if the connection fails
  if (!$connection) {
    print("Connection Failed.");
    exit;
  }
  
  // declare my query and execute
  $myresult = pg_exec($connection, "SELECT * FROM id,username,fname,lname where id > 100");
  
  // process results
  for ($lt = 0; $lt < pg_numrows($myresult); $lt++) {
    $id = pg_result($myresult, $lt, 0);
    $username = pg_result($myresult, $lt, 1);
    $fname = pg_result($myresult, $lt, 2);
    $lname = pg_result($myresult, $lt, 3);
    
    // print results
    print("User Id: $id<br />\n");
    print("Username: $username<br />\n");
    print("First Name: $fname<br />\n");
    print("Last Name: $lname<br />\n");
  }
?>

We have introduced some new items with the added code. The first is the for loop. If you do not utilize a loop, the script will only display the first row of results. Obviously, if you have a query that produces 10 results, you want to see all 10, not just 1. Thus, the for loop is used.

Related Reading

Practical PostgreSQLPractical PostgreSQL
By John C. Worsley, Joshua D. Drake
Table of Contents
Index
Sample Chapter
Full Description

We also introduced two new functions: pg_numrows() and pg_result(). The pg_numrows() function is used within the for loop parameters, and the pg_result() function is used multiple times within the loop. The pg_numrows() function does something very simple, but very useful. It returns the number of rows for a given result set retrieved from PostgreSQL. The pg_result() function will return the object values from a result. In other words, it is used to retrieve the column data returned with the rows. Its first argument is the returned result set ($myresult), its second argument is the row offset, and its third argument is the column offset.

Now you have connected to a PostgreSQL database, executed a query, processed the results, and displayed them. However, that is only part of the equation. PHP is a web language, and with web languages come HTML forms. Therefore, we want to show you, the reader, the person that allows me to pay my bills, how to insert values using a form and PHP. Let's take the following example form.

<title>My web form</title>
<body>
  <form method="POST" action="http://myhost/testform.php">
    User Id: <input type="text" name="id"> <br />
    User Name <input type=text name="username"> <br />
    First Name <input type=text name="fname"> <br />
    Last Name <input type=text name="lname"> <br />
    <hr />
    <input type="submit" name="submit" value="Insert">
  </form>
</body>

The previous example is a basic HTML form that will post to the script testform.php. In our case, the script testform.php is going to be the same file as the HTML form. This can be achieved by doing the following:

<?
  // If they have not clicked submit... show them the form.
  if($submit != "Insert") {
  // no reason to create excess print() statements.. just close the script.
?>
<title>My web form</title>
<body>
  <form method="POST" action="http://myhost/testform.php">
     User Id: <input type="text" name="id"> <br />
     User Name <input type=text name="username"> <br />
     First Name <input type=text name="fname"> <br />
     Last Name <input type=text name="lname"> <br />
    <hr />
    <input type="submit" name="submit" value="Insert">
  </form>
</body>
<?
  } else {
    // since submit was hit, process the form
    
    // make our connection
    $connection = pg_connect("dbname=mydb user=myuser");
    
    // let me know if the connection fails
    if (!$connection) {
      print("Connection Failed.");
      exit;
    }
    
    // Insert the values from the form
    pg_exec($connection, "INSERT INTO users VALUES ('$id','$username','$fname','$lname')");
    
    // select the values based on an id where clause and then display the data
    $myresult = pg_exec($connection, "SELECT * FROM users WHERE id='$id'");
    
    // process results
    for ($lt = 0; $lt < pg_numrows($myresult); $lt++) {
      $id = pg_result($myresult, $lt, 0);
      $username = pg_result($myresult, $lt, 1);
      $fname = pg_result($myresult, $lt, 2);
      $lname = pg_result($myresult, $lt, 3);
      
      // print results
      print("You inserted: <br />");
      print("<hr /><br />");
      print("User Id: $id<br />\n");
      print("Username: $username<br />\n");
      print("First Name: $fname<br />\n");
      print("Last Name: $lname<br />\n");
    }
  }
?>

The previous example does quite a bit in just a little bit of code. First, it checks to see if the person visiting the page has hit the submit button. If the person hasn't hit submit, it will display our simple HTML form.

Related Reading

Programming PHPProgramming PHP
By Rasmus Lerdorf, Kevin Tatroe
Full Description

If the person visiting the site did hit the submit button, the script will attempt to insert the values from the form into the PostgreSQL database table. The script will then select from the table based on the ID from the submission form, and display the results.

Alternatively, you could have just redisplayed the passed arguments. However, by using a query, you are instead able to verify that the data that was inserted is actually the data you submitted via the form (and that the data was inserted at all). You can also see, in this context, any other existing users with the same ID as the one you just inserted.

Joshua D. Drake is cofounder of Command Prompt, Inc., a PostgreSQL and Linux custom development company.


O'Reilly & Associates recently released (December 2001) Practical PostgreSQL.

Return to the ONLamp.com.





Sponsored by: