Skip to content

Latest commit

 

History

History
303 lines (238 loc) · 10.8 KB

README.md

File metadata and controls

303 lines (238 loc) · 10.8 KB
Raw

PHPG

A PostgreSQL database interface 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.

Features

  • Automatic detection and conversion of most PostgreSQL data-types to native PHP data structures, such as: Integer, Float, Boolean, NULL, Array, Hstore, Geometrical Data-Types, Date/Time, and more.
  • Transaction-style database cursors, including commit and rollback.
  • Access to existing database connections from any scope in PHP.

More on Automatic Detection & Conversion of PostgreSQL Data-Types

  • PostgreSQL Arrays (any data-type) to/from PHP Arrays.
  • PostgreSQL Hstores to/from PHP Associative Arrays.
  • PostgreSQL Geometric Data-Types (box, point, polygon, lseg, etc) to/from native PHP Associative Arrays.
  • PostgreSQL Dates / Timestamps to/from native PHP DateTime Objects (including automatic detection of Time Zones).
  • ... And much more!

Requirements

  • 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

Written and maintained by:

Online Presence:

Background

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

Qualifications

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

License

Please refer to the LICENSE file for licensing and copyright information.

Quick Start Guide / Tutorial

Instantiate a new PostgreSQL connection

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

Retrieve an existing PostgreSQL conection (from any scope)

<?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');

Create a new Cursor

<?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();

Perform a query and iterate over the result set

<?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
}

Perform a query, and retrieve the number of rows returned (used for SELECT and RETURNING statements)

<?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();

Perform a query, and retrieve the number of rows affected (used for INSERT, UPDATE and DELETE statements)

<?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();

Perform a query, and retrieve a single row from the result set

<?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();

Perform a query, and retrieve all of the rows in a single array

<?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();

Commit one or more changes

<?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();

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.

Rollback one or more changes

<?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();

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

Reset the cursor's pointer to the beginning of the result set

<?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

Set the cursor's pointer to a specific offset

<?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

Free a result set once it's no longer needed

<?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();

Donations

PHPG is free software, but donations help the developer spend more time maintaining this projects and others like it.