PHP and PostgreSQL Connectivity

Processing model:

1. Establish connection, receive handle.
2. Send SQL query, receive results ``array.''
3. Process results array.
4. Free results array.
5. Repeat as needed.
6. Close connection.

1. Establishing a connection:

   $handle = pg_connect("dbname=databaseName user=userName password=pwd");

   Check handle status! Why handles? (Script could have multiple DB connections open.)
    

2. Sending a query:

   $result = pg_exec($handle, "query string");

   Check result status!
    

3. Determining the size of a result: pg_numrows($result), pg_numfields($result).
    
4. Accessing the result:

   $item = pg_result($result, $row, $field);
   $item = pg_result($result, $row, "fieldName");

   $row and $field are 0-based numeric indices. fieldName is an associative array-style index.
    
5. Freeing a result, closing a connection:

   pg_freeresult($result);
   pg_close($handle);
   

Example Code:

login.html                login.txt
authenticate.php      authenticate.txt
query.php               query.txt         
 

Things to note for each file:

1. login.html:
   1. Form tag: method and action.
   2. Input tags: types and names.
       
2. authenticate.php:
   1. Debugging notes.
   2. Retrieval of username, password. Associative array.
   3. Database connection and error checking.
   4. Sending a query and error checking.
   5. Accessing query results. Associative array.

      Why the check on pg_numrows()?
       

   6. Establishing the session and saving session variables.
   7. Passing SID back to the server as a GET parameter.

      SID will be empty when we resume the session.
       

   8. Freeing the result and closing the database.

    

3. query.php:
   1. Retrieving session variables.
   2. Iterating through the result.