PHP Classes
elePHPant
Icontem

PHPG: PHP PostgreSQL database class

Recommend this page to a friend!
  Info   View files View files (3)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2013-03-19 (3 years ago) RSS 2.0 feedNot yet rated by the usersTotal: 192 All time: 7,886 This week: 1,016Up
Version License PHP version Categories
phpg 1.0Public Domain5.0PHP 5, Databases
Description Author

This is a PHP PostgreSQL database access class.

It can establish connections to a PostgreSQL database server and create cursors to execute queries and retrieve the query results.

The class can automatic detection and convert PostgreSQL data types to PHP data types including integers, floats, booleans, NULLs, arrays, Hstores, geometrical types, dates and times, etc..

It can also commit or rollback started transactions.

Picture of Joshua D. Burns
Name: Joshua D. Burns <contact>
Classes: 1 package by
Country: United States United States

Details
PHPG
====

A PostgreSQL database interface class written in PHP specifically designed to confront and resolve a majority of long-standing issues with PHP's native PostgreSQL driver.

Much of the underlying functionality utilizes PHP's native PostgreSQL driver to maintain performance and reliability.

<b>Features</b>
* Automatic detection and transformation of most PostgreSQL data-types to native PHP data structures. Includes Integers, Floats, Booleans, NULLs, Arrays, Hstores, Geometrical Types, and more!
* Transaction-style database cursors, with commit and rollback functionality.
* Superglobal database connections: Retrieve existing database connections from any scope.

<b>More on Automatic Detection & Transformation of PostgreSQL Data-Types</b>
* PostgreSQL Arrays (any data-type) to PHP Arrays.
* PostgreSQL Hstores to PHP Associative Arrays.
* PostgreSQL Geometric Data-Types (box, point, polygon, lseg, etc) to native PHP Associative Arrays.
* PostgreSQL Dates / Timestamps to native PHP DateTime Objects (including automatic detection of Time Zones).
* ... And much more!

<b>Requirements</b>
* PHP: 5.0 or later
* PostgreSQL: 8.0 or later
* PostgreSQL Contrib Modules (Optional) : hstore, PostGIS (PostgreSQL 8.x, built into 9.x)

About The Author
================
<b>Written and maintained by:</b>
* Joshua D. Burns
* <jdburnz@gmail.com>, <josh@messageinaction.com>
* http://www.messageinaction.com

<b>Online Presence:</b>
* Programming BLOG: http://www.youlikeprogramming.com
* LinkedIn: http://www.linkedin.com/in/joshuadburns
* Stack Overflow: http://stackoverflow.com/users/253254/joshua-burns

<b>Background</b>
* Co-Founder and Director of Technology at Message In Action (http://www.messageinaction.com).
* Specializes in large-scale, high performance eCommerce and inventory management systems.
* Entrepreneur, Strategist, Political Enthusiast and Project Manager.

<b>Qualifications</b>
* 5+ years experience in Project Management
* 15+ years of programming experience including PHP, Python and Javascript.
* Intimate working knowledge of PostgreSQL, Microsoft SQL Server, MySQL and MongoDB database back-ends.

Quick Start Guide / Tutorial
============================

<b>Instantiate a new PostgreSQL connection</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class

// Pass a string of connection parameters as described in here: http://php.net/manual/en/function.pg-connect.php
$params = "host=localhost port=5432 dbname=my_db user=postgres password=my_pass options='--client_encoding=UTF8'";
$phpg = new PHPG('My DB', $params);

// Pass an associative array of connection parameters:
$params = array(
  'host' => 'localhost',
  'port' => '5432',
  'dbname' => 'my_db',
  'user' => 'postgres',
  'password' => 'my_pass',
  'options' => "'--client_encoding=UTF8'"
);
$phpg = new PHPG('My DB', $params);
```

<b>Retrieve an existing PostgreSQL conection (from any scope)</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class

// Initial instantiation of connection:
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params);

/**
/* From Another Scope, where $phpg database connection is not accessible...
**/

// Retrieve existing connection via it's connection alias:
$phpg = new PHPG('My DB');
```

<b>Create a new Cursor</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection

// Create a cursor from which we can execute queries:
$cursor = $phpg->cursor();
```

<b>Perform a query and iterate over the result set</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform the query
$cursor->execute("SELECT first_name, last_name FROM users ORDER BY last_name, first_name");

// Iterate over the result set using `while` syntax:
while($user = $cursor->iter()) {
  // do something
}

// Iterate over the result set using `foreach` syntax (not yet implemented):
foreach($cursor as $offset => $user) {
  // do something
}
```

<b>Perform a query, and retrieve the number of rows returned (used for SELECT and RETURNING statements)</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform the query:
$cursor->execute("SELECT first_name, last_name FROM users ORDER BY last_name, first_name");

// Retrieve the number of rows returned by the query:
$num_results = $cursor->rows_returned();
```

<b>Perform a query, and retrieve the number of rows affected (used for INSERT, UPDATE and DELETE statements)</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform the query:
$cursor->execute("DELETE FROM users WHERE last_name = 'Doe'");

// Retrieve the number of rows affected by the query:
$num_results = $cursor->rows_affected();
```

<b>Perform a query, and retrieve a single row from the result set</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform the query
$cursor->execute("SELECT first_name, last_name FROM users ORDER BY last_name, first_name");

// Grab a row (returns row 0, and advances the cursor from row 0 to row 1)
$user_1 = $cursor->fetchone();

// Grab another row (returns row 1, and advances the cursor from row 1 to row 2)
$user_2 = $cursor->fetchone();
```

<b>Perform a query, and retrieve all of the rows in a single array</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform the query
$cursor->execute("SELECT first_name, last_name FROM users ORDER BY last_name, first_name");

// Grab the entire result set (returns an array of associative arrays).
// NOTICE! If you're returning a lot of data this can very easily exhaust your working memory.
$users = $cursor->fetchall();
```

<b>Commit one or more changes</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$first_cursor = $phpg->cursor(); // Create a cursor
$second_cursor = $phpg->cursor(); // Create another cursor

// Insert a couple records
$first_cursor->execute("INSERT INTO users (first_name, last_name) VALUES ('John', 'Smith')");
$second_cursor->execute("INSERT INTO users (first_name, last_name) VALUES ('Janet', 'Johnson')");

// Update a record
$first_cursor->execute("UPDATE users SET first_name = 'Jane' WHERE last_name = 'Doe'");

// Commit all actions, across all the database connection's cursors, up to this point.
$phpg->commit();
```

<i>Note: commit() will commit *all* INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, etc actions made across all of the database connection's cursors since the last rollback() or commit() was performed.</i>

<b>Rollback one or more changes</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$first_cursor = $phpg->cursor(); // Create a cursor
$second_cursor = $phpg->cursor(); // Create another cursor

// Insert a couple records
$first_cursor->execute("INSERT INTO users (first_name, last_name) VALUES ('John', 'Smith')");
$second_cursor->execute("INSERT INTO users (first_name, last_name) VALUES ('Janet', 'Johnson')");

// Update a record
$first_cursor->execute("UPDATE users SET first_name = 'Jane' WHERE last_name = 'Doe'");

// Roll back all actions, across all the database connection's cursors, up to this point.
$phpg->rollback();
```

<i>Note: rollback() will rollback *all* INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, etc actions made across all of the database connection's cursors since the last rollback() or commit() was performed</i>

<b>Reset the cursor's pointer to the beginning of the result set</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform a query
$cursor->execute("SELECT * FROM users");

$user1 = $cursor->fetchone(); // Grab the first row
$user2 = $cursor->fetchone(); // Grab the second row

// Reset the cursor, setting it's internal pointer back to row zero
$cursor->reset();

$user3 = $cursor->fetchone(); // Grab first row again
$user4 = $cursor->fetchone(); // Grab second row again
```

<b>Set the cursor's pointer to a specific offset</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform a query
$cursor->execute("SELECT * FROM users");

$user1 = $cursor->fetchone(); // Grab the first row
$user2 = $cursor->fetchone(); // Grab the second row
$user3 = $cursor->fetchone(); // Grab the third row

// Set the cursor's pointer back to row 2 (offsets start at zero, so 0 = first row, 1 = second row etc)
$cursor->seek(1);

$user4 = $cursor->fetchone(); // Grab second row again
$user5 = $cursor->fetchone(); // Grab third row again
```

<b>Free a result set once it's no longer needed</b>
```php
<?php
require('phpg.php'); // Contains PHPG Class
$params = "host=localhost dbname=my_db user=postgres password=my_pass";
$phpg = new PHPG('My DB', $params); // Instantiate a PostgreSQL connection
$cursor = $phpg->cursor(); // Create a cursor

// Perform a query
$cursor->execute("SELECT * FROM users");

// Iterate over the results
while($user = $cursor->iter()) {
  // do something
}

// Free the result set, because we have no further need for it.
$cursor->free();
```
  Files folder image Files  
File Role Description
Plain text file phpg.php Class PHPG Class Library
Accessible without login Plain text file README.txt Doc. Getting started
Accessible without login Plain text file LICENSE.txt Lic. License

 Version Control Unique User Downloads Download Rankings  
 0%
Total:192
This week:0
All time:7,886
This week:1,016Up