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();
```
|
Download
