use PDO;
use PDOException;

/**
* Used for interacting with the database. Usage:
* <pre>
* $db = Database::get();
* $db->call( ... );
* </pre>
*/
class Database extends Obj {
private static $instance;
private $dataStore;

/**
* Sets the connection that this class uses for database transactions.
*/
public function __construct() {
global $dbhost;
global $dbname;
global $dbuser;
global $dbpass;

try {
$this->setDataStore(
new PDO( "pgsql:dbname=$dbname;host=$dbhost", $dbuser, $dbpass ) );
}
catch( PDOException $ex ) {
$this->log( $ex->getMessage() );
}
}

/**
* Returns the singleton database instance.
*/
public function get() {
if( self::$instance === null ) {
self::$instance = new Database();
}

return self::$instance;
}

/**
* Call a database function and return the results. If there are
* multiple columns to return, then the value for $params must contain
* a comma; otherwise, without a comma, the value for $params is used
* as the return column name. For example:
*
*- SELECT $params FROM $proc( ?, ? ); -- with comma
*- SELECT $proc( ?, ? ) AS $params; -- without comma
*- SELECT $proc( ?, ? ); -- empty
*
* @param $proc Name of the function or stored procedure to call.
* @param $params Name of parameters to use as return columns.
*/
public function call( $proc, $params = "" ) {
$args = array();
$count = 0;
$placeholders = "";

// Key is zero-based (e.g., $proc = 0, $params = 1).
foreach( func_get_args() as $key => $parameter ) {
// Skip the $proc and $params arguments to this method.
if( $key < 2 ) continue;

$count++;
$placeholders = empty( $placeholders ) ? "?" : "$placeholders,?";
array_push( $args, $parameter );
}

$sql = "";

if( empty( $params ) ) {
// If there are no parameters, then just make a call.
$sql = "SELECT recipe.$proc( $placeholders )";
}
else if( strpos( $params, "," ) !== false ) {
// If there is a comma, select the column names.
$sql = "SELECT $params FROM recipe.$proc( $placeholders )";
}
else {
// Otherwise, select the result into the given column name.
$sql = "SELECT recipe.$proc( $placeholders ) AS $params";
}

$statement = $this->getDataStore()->prepare( $sql );

//$this->log( "SQL: $sql" );

for( $i = 1; $i <= $count; $i++ ) {
//$this->log( "Bind " . $i . " to " . $args[$i - 1] );
$statement->bindParam( $i, $args[$i - 1] );
}

$statement->execute();
$result = $statement->fetchAll();
$this->decodeArray( $result );

return $result;
}

/**
* Converts an array of numbers into an array suitable for usage with
* PostgreSQL.
*
* @param $array An array of integers.
*/
public function arrayToString( $array ) {
return "{" . implode( ",", $array ) . "}";
}

/**
* Recursive method to decode a UTF8-encoded array.
*
* @param $array - The array to decode.
* @param $key - Name of the function to call.
*/
private function decodeArray( &$array ) {
if( is_array( $array ) ) {
array_map( array( $this, "decodeArray" ), $array );
}
else {
$array = utf8_decode( $array );
}
}

private function getDataStore() {
return $this->dataStore;
}

private function setDataStore( $dataStore ) {
$this->dataStore = $dataStore;
}
}

Example usage:

$db = Database::get();
$result = $db->call( "is_existing_cookie", "existing", $cookie_value );

return isset( $result[0] ) ? $result[0]["existing"] > 0 : false;

Another example:

private function authenticate() {
$db = Database::get();
$db->call( "authentication_upsert", "",
$this->getCookieToken(),
$this->getBrowserPlatform(),
$this->getBrowserName(),
$this->getBrowserVersion(),
$this->getIp()
);
}

Switching to PDO is better. Critiques welcome on Code Review SE.

http://codereview.stackexchange.com/questions/26507/generic-...