PostgreSQL 數據庫的簡單操作PHP類

//使用

define('DB_NAME', 'aaaa');// 數據庫名
define('DB_USER', 'root');// 數據庫用戶
define('DB_PASSWORD', '123456');//數據庫密碼
define('DB_HOST', 'localhost');// 服務器名或服務器ip,一般為localhost
define('DB_CHARSET', 'utf8');//PostgreSQL編碼設置.如果您的程序出現亂碼現象，需要設置此項來修復. 請不要隨意更改此項，否則將可能導致系統出現亂碼現象

define('DBPREFIX', 'icms');// 表名前綴, 同一數據庫安裝多個請修改此處
define('DB_PREFIX_TAG', '#iCMS@__');// SQL表名前綴替換


iDB::query(sql);
iDB::getValue(sql);
iDB::getRow(sql);
iDB::getArray(sql);

<?php
/**


iPHP - i PHP Framework
Copyright (c) 2012 iiiphp.com. All rights reserved.
*
@author coolmoo <iiiphp@qq.com>
@site http://www.iiiphp.com
@licence http://www.iiiphp.com/license
@version 1.0.1
@package iDB
@$Id: iPgsql.class.php 44 2012-03-30 04:02:10Z coolmoo $
*/
define('DB_PORT', 5432);
define('OBJECT', 'OBJECT', true);
define('ARRAY_A', 'ARRAY_A', false);
define('ARRAY_N', 'ARRAY_N', false);

defined('SAVEQUERIES') OR define('SAVEQUERIES', true);
class iDB{
    public static $show_errors = false;
    public static $num_queries = 0;
    public static $last_query;
    public static $col_info;
    public static $queries;
    public static $func_call;
    public static $last_result;
    public static $num_rows;
    public static $insert_id;

private static $collate;
private static $time_start;
private static $last_error ;
private static $dbh;
private static $result;

function __construct() {
    if (!self::$dbh)
        self::connect();
}
function connect() {
    extension_loaded('pgsql') OR die('您的 PHP 安裝看起來缺少 PostgreSQL 數據庫部分，這對 iPHP 來說是必須的。');

    defined('DB_COLLATE') && self::$collate = DB_COLLATE;

    self::$dbh = pg_connect("host=".DB_HOST." port=".DB_PORT." dbname=".DB_NAME." user=".DB_USER." password=".DB_PASSWORD);

    defined('DB_CHARSET') && self::query("set client_encoding to '".DB_CHARSET."'");

    self::$dbh OR self::bail("<h1>數據庫鏈接失敗</h1><p>請檢查 <em><strong>config.php</strong></em> 的配置是否正確!</p><ul><li>請確認主機支持PostgreSQL?</li><li>請確認用戶名和密碼正確?</li><li>請確認主機名正確?(一般為localhost)</li></ul><p>如果你不確定這些情況,請詢問你的主機提供商.如果你還需要幫助你可以隨時瀏覽 <a >iPHP 支持論壇</a>.</p>");
    //@mysql_select_db(DB_NAME, self::$dbh) OR self::bail("<h1>鏈接到<em><strong>".DB_NAME."</strong></em>數據庫失敗</h1><p>我們能連接到數據庫服務器（即數據庫用戶名和密碼正確） ，但是不能鏈接到<em><strong>$db</strong></em>數據庫.</p><ul><li>你確定<em><strong>$db</strong></em>存在?</li></ul><p>如果你不確定這些情況,請詢問你的主機提供商.如果你還需要幫助你可以隨時瀏覽 <a >iPHP 支持論壇</a>.</p>");

}
// ==================================================================
//  Print SQL/DB error.

function print_error($str = '') {
    if (!$str)
        $str = pg_result_error(self::$dbh);

    $EZSQL_ERROR[]  = array ('query' => self::$last_query, 'error_str' => $str);

    $str    = htmlspecialchars($str, ENT_QUOTES);
    $query  = htmlspecialchars(self::$last_query, ENT_QUOTES);
    // Is error output turned on or not..
    if ( self::$show_errors ) {
        // If there is an error then take note of it
        die("<div id='error'>
        <p class='iPHPDBerror'><strong>iPHP database error:</strong> [$str]<br />
        <code>$query</code></p>
        </div>");
    } else {
        return false;
    }
}
// ==================================================================
//  Kill cached query results

function flush() {
    self::$last_result  = array();
    self::$col_info     = null;
    self::$last_query   = null;
}

// ==================================================================
//  Basic Query - see docs for more detail

function query($query,$QT=NULL) {
    if (!self::$dbh) {
        self::connect();
    }
    // filter the query, if filters are available
    // NOTE: some queries are made before the plugins have been loaded, and thus cannot be filtered with this method
    $query=str_replace(DB_PREFIX_TAG,DB_PREFIX, $query);

    // initialise return
    $return_val = 0;
    self::flush();

    // Log how the function was called
    self::$func_call = __CLASS__."::query(\"$query\")";

    // Keep track of the last query for debug..
    self::$last_query = $query;

    // Perform the query via std pgsql_query function..
    if (SAVEQUERIES) self::timer_start();

    self::$result = pg_query(self::$dbh,$query);


    self::$num_queries++;

    if (SAVEQUERIES) self::$queries[] = array( $query, self::timer_stop());

    // If there is an error then take note of it..
    if ( self::$last_error = pg_result_error(self::$result) ) {
        self::print_error();
        return false;
    }
    $QH = strtoupper(substr($query,0,strpos($query, ' ')));
    if (in_array($QH,array("INSERT","DELETE","UPDATE","REPLACE"))) {
        $rows_affected = pg_affected_rows (self::$result);
        // Take note of the insert_id
        if (in_array($QH,array("INSERT","REPLACE"))) {
            self::$insert_id = pg_last_oid(self::$result);
        }
        // Return number of rows affected
        $return_val = $rows_affected;
    } else {
        if($QT=="field") {
            $i = 0;
            while ($i < pg_num_fields(self::$result)) {
                self::$col_info[$i] = pg_field_name(self::$result);
                $i++;
            }
        }else {
            $num_rows = 0;
            while ( $row = pg_fetch_object(self::$result) ) {
                self::$last_result[$num_rows] = $row;
                $num_rows++;
            }
            // Log number of rows the query returned
            self::$num_rows = $num_rows;

            // Return number of rows selected
            $return_val = $num_rows;
        }
        pg_free_result(self::$result);
    }

    return $return_val;
}
/**
 * Insert an array of data into a table
 * @param string $table WARNING: not sanitized!
 * @param array $data should not already be SQL-escaped
 * @return mixed results of self::query()
 */
function insert($table, $data) {

//      $data = add_magic_quotes($data);
        $fields = array_keys($data);
        return self::query("INSERT INTO ".DB_PREFIX_TAG."{$table} (" . implode(',',$fields) . ") VALUES ('".implode("','",$data)."')");
    }

/**
 * Update a row in the table with an array of data
 * @param string $table WARNING: not sanitized!
 * @param array $data should not already be SQL-escaped
 * @param array $where a named array of WHERE column => value relationships.  Multiple member pairs will be joined with ANDs.  WARNING: the column names are not currently sanitized!
 * @return mixed results of self::query()
 */
function update($table, $data, $where) {

//      $data = add_magic_quotes($data);
        $bits = $wheres = array();
        foreach ( array_keys($data) as $k )
            $bits[] = "$k = '$data[$k]'";

    if ( is_array( $where ) )
        foreach ( $where as $c => $v )
            $wheres[] = "$c = '" . addslashes( $v ) . "'";
    else
        return false;
    return self::query("UPDATE ".DB_PREFIX_TAG."{$table} SET " . implode( ', ', $bits ) . ' WHERE ' . implode( ' AND ', $wheres ) . ' LIMIT 1' );
}
/**
 * Get one variable from the database
 * @param string $query (can be null as well, for caching, see codex)
 * @param int $x = 0 row num to return
 * @param int $y = 0 col num to return
 * @return mixed results
 */
function getValue($query=null, $x = 0, $y = 0) {
    self::$func_call = __CLASS__."::getValue(\"$query\",$x,$y)";
    if ( $query )
        self::query($query);

    // Extract var out of cached results based x,y vals
    if ( !empty( self::$last_result[$y] ) ) {
        $values = array_values(get_object_vars(self::$last_result[$y]));
    }
    // If there is a value return it else return null
    return (isset($values[$x]) && $values[$x]!=='') ? $values[$x] : null;
}

/**
 * Get one row from the database
 * @param string $query
 * @param string $output ARRAY_A | ARRAY_N | OBJECT
 * @param int $y row num to return
 * @return mixed results
 */
function getRow($query = null, $output = OBJECT, $y = 0) {
    self::$func_call = __CLASS__."::getRow(\"$query\",$output,$y)";
    if ( $query )
        self::query($query);

    if ( !isset(self::$last_result[$y]) )
        return null;

    if ( $output == OBJECT ) {
        return self::$last_result[$y] ? self::$last_result[$y] : null;
    } elseif ( $output == ARRAY_A ) {
        return self::$last_result[$y] ? get_object_vars(self::$last_result[$y]) : null;
    } elseif ( $output == ARRAY_N ) {
        return self::$last_result[$y] ? array_values(get_object_vars(self::$last_result[$y])) : null;
    } else {
        self::print_error(__CLASS__."::getRow(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
    }
}

/**
 * Return an entire result set from the database
 * @param string $query (can also be null to pull from the cache)
 * @param string $output ARRAY_A | ARRAY_N | OBJECT
 * @return mixed results
 */
function getArray($query = null, $output = ARRAY_A) {
    self::$func_call = __CLASS__."::getArray(\"$query\", $output)";

    if ( $query )
        self::query($query);

    // Send back array of objects. Each row is an object
    if ( $output == OBJECT ) {
        return self::$last_result;
    } elseif ( $output == ARRAY_A || $output == ARRAY_N ) {
        if ( self::$last_result ) {
            $i = 0;
            foreach( (array) self::$last_result as $row ) {
                if ( $output == ARRAY_N ) {
                    // ...integer-keyed row arrays
                    $new_array[$i] = array_values( get_object_vars( $row ) );
                } else {
                    // ...column name-keyed row arrays
                    $new_array[$i] = get_object_vars( $row );
                }
                ++$i;
            }
            return $new_array;
        } else {
            return null;
        }
    }
}

/**
 * Gets one column from the database
 * @param string $query (can be null as well, for caching, see codex)
 * @param int $x col num to return
 * @return array results
 */
function getCol($query = null , $x = 0) {
    if ( $query )
        self::query($query);

    $new_array = array();
    // Extract the column values
    for ( $i=0; $i < count(self::$last_result); $i++ ) {
        $new_array[$i] = self::getValue(null, $x, $i);
    }
    return $new_array;
}

/**
 * Grabs column metadata from the last query
 * @param string $info_type one of name, table, def, max_length, not_null, primary_key, multiple_key, unique_key, numeric, blob, type, unsigned, zerofill
 * @param int $col_offset 0: col name. 1: which table the col's in. 2: col's max length. 3: if the col is numeric. 4: col's type
 * @return mixed results
 */
function get_col_info($query = null ,$info_type = 'name', $col_offset = -1) {
    if ( $query )
        self::query($query,"field");

    if ( self::$col_info ) {
        if ( $col_offset == -1 ) {
            $i = 0;
            foreach(self::$col_info as $col ) {
                $new_array[$i] = $col->{$info_type};
                $i++;
            }
            return $new_array;
        } else {
            return self::$col_info[$col_offset]->{$info_type};
        }
    }
}
function version() {
    // Make sure the server has PostgreSQL 4.0
    $v = pg_version(self::$dbh);
    return $v['client'];
}

/**
 * Starts the timer, for debugging purposes
 */
function timer_start() {
    $mtime = microtime();
    $mtime = explode(' ', $mtime);
    self::$time_start = $mtime[1] + $mtime[0];
    return true;
}

/**
 * Stops the debugging timer
 * @return int total time spent on the query, in milliseconds
 */
function timer_stop() {
    $mtime = microtime();
    $mtime = explode(' ', $mtime);
    $time_end = $mtime[1] + $mtime[0];
    $time_total = $time_end - self::$time_start;
    return $time_total;
}

/**
 * Wraps fatal errors in a nice header and footer and dies.
 * @param string $message
 */
function bail($message){ // Just wraps errors in a nice header and footer
    if ( !self::$show_errors ) {
        return false;
    }
    header('Content-Type: text/html; charset=utf8');
    echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><title>iPHP PostgreSQL Error</title><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /></head><body><h1 id="logo"><img alt="iPHP" src="http://www.iiiphp.com/doc/iPHP.logo.gif" /></h1><p>'.$message.'</p></body></html>';
    exit();
}

}</pre>