PHP pg_query函数代码示例

function show_css_page($dbconn, $diary_login)
{
    $sql = "SELECT u.uid, s.page_css FROM " . TABLE_SETTINGS . " AS s, " . TABLE_USERS . " AS u WHERE u.login='" . $diary_login . "' AND s.uid=u.uid LIMIT 1";
    $result = pg_query($dbconn, $sql) or die(pg_last_error($dbconn));
    $data = pg_fetch_object($result, NULL);
    echo $data->page_css;
}

 function sql_query($sqltype, $query, $con)
 {
     if ($sqltype == 'mysql') {
         if (class_exists('mysqli')) {
             return $con->query($query);
         } elseif (function_exists('mysql_query')) {
             return mysql_query($query);
         }
     } elseif ($sqltype == 'mssql') {
         if (function_exists('sqlsrv_query')) {
             return sqlsrv_query($con, $query);
         } elseif (function_exists('mssql_query')) {
             return mssql_query($query);
         }
     } elseif ($sqltype == 'pgsql') {
         return pg_query($query);
     } elseif ($sqltype == 'oracle') {
         return oci_execute(oci_parse($con, $query));
     } elseif ($sqltype == 'sqlite3') {
         return $con->query($query);
     } elseif ($sqltype == 'sqlite') {
         return sqlite_query($con, $query);
     } elseif ($sqltype == 'odbc') {
         return odbc_exec($con, $query);
     } elseif ($sqltype == 'pdo') {
         return $con->query($query);
     }
 }

 /**
  * Взять информацию по найденным результатам
  *
  * @return array массив с пользователями
  */
 function getRecords($order_by = NULL)
 {
     if ($this->matches) {
         $sql = "SELECT * FROM search_users_simple WHERE id IN (" . implode(', ', $this->matches) . ')';
         if ($order_by) {
             $sql .= " ORDER BY {$order_by}";
         } else {
             if ($this->_sortby && (($desc = $this->_sort == SPH_SORT_ATTR_DESC) || $this->_sort == SPH_SORT_ATTR_ASC)) {
                 $sql .= " ORDER BY {$this->_sortby}" . ($desc ? ' DESC' : '');
             }
         }
         if ($res = pg_query(DBConnect(), $sql)) {
             if (!$order_by && ($this->_sort == SPH_SORT_RELEVANCE || $this->_sort == SPH_SORT_EXTENDED)) {
                 $links = array();
                 $rows = array();
                 while ($row = pg_fetch_assoc($res)) {
                     $links[$row['id']] = $row;
                 }
                 for ($i = 0; $i < count($this->matches); $i++) {
                     $rows[] = $links[$this->matches[$i]];
                 }
             } else {
                 $rows = pg_fetch_all($res);
             }
             return $rows;
         }
     }
     return array();
 }

 public function query($query)
 {
     if ($this->connection === null) {
         $this->connect();
     }
     try {
         $result = pg_query($this->connection, $query);
     } catch (Exception $e) {
         if (preg_match("/^pg_query\\(\\): Query failed: ERROR:  null value in column (\\S+) violates not-null constraint/", $e->getMessage(), $matches)) {
             throw new Atrox_Core_Exception_NullValueException($matches[1] . " can not be null");
         } else {
             if (preg_match("/^pg_query\\(\\): Query failed: ERROR:  duplicate key violates unique constraint/", $e->getMessage())) {
                 throw new Atrox_Core_Data_Exception_DuplicateKeyException($e->getMessage());
             } else {
                 if (preg_match("/^pg_query\\(\\): Query failed: ERROR:  relation \"(\\S+)\" does not exist/", $e->getMessage(), $matches)) {
                     throw new Atrox_Core_Exception_NoSuchRelationException($e->getMessage());
                 } else {
                     if (preg_match("/^pg_query\\(\\): Query failed: ERROR:  value (\\S+) is out of range for type (\\S+)/", $e->getMessage(), $matches)) {
                         throw new Atrox_Core_Exception_OutOfRangeException($matches[1] . " is not within range of datatype '{$matches[2]}'");
                     } else {
                         if (preg_match("/^pg_query\\(\\): Query failed: ERROR:  value too long for type (.+)/", $e->getMessage(), $matches)) {
                             throw new Atrox_Core_Exception_TooLongException("Value is too long for datatype '{$matches[1]}'");
                         } else {
                             throw $e;
                         }
                     }
                 }
             }
         }
     }
     return $result;
 }

 public function query($sql)
 {
     $resource = pg_query($this->link, $sql);
     if ($resource) {
         if (is_resource($resource)) {
             $i = 0;
             $data = array();
             while ($result = pg_fetch_assoc($resource)) {
                 $data[$i] = $result;
                 $i++;
             }
             pg_free_result($resource);
             $query = new \stdClass();
             $query->row = isset($data[0]) ? $data[0] : array();
             $query->rows = $data;
             $query->num_rows = $i;
             unset($data);
             return $query;
         } else {
             return true;
         }
     } else {
         trigger_error('Error: ' . pg_result_error($this->link) . '<br />' . $sql);
         exit;
     }
 }

 public function query($query)
 {
     if ($this->ok()) {
         $this->resource = pg_query($this->connection, $query);
     }
     return $this->resource !== false;
 }

 function Header()
 {
     //$dados = @pg_exec("select nomeinst,ender,munic,uf,telef,email,url,logo from db_config where codigo = ".@$GLOBALS["DB_instit"]);
     //$url = @pg_result($dados,0,"url");
     //global $nomeinst;
     //$nomeinst = pg_result($dados,0,"nomeinst");
     //global $ender;
     //$ender = pg_result($dados,0,"ender");
     $sql = "select nomeinst,bairro,cgc,ender,upper(munic) as munic,uf,telef,email,url,logo, db12_extenso\n\t\tfrom db_config \n\t\tinner join db_uf on db12_uf = uf\n\t\twhere codigo = " . db_getsession("DB_instit");
     $result = pg_query($sql);
     global $nomeinst;
     global $ender;
     global $munic;
     global $cgc;
     global $bairro;
     global $uf;
     //echo $sql;
     db_fieldsmemory($result, 0);
     /// seta a margem esquerda que veio do relatorio
     $S = $this->lMargin;
     $this->SetLeftMargin(10);
     $Letra = 'Times';
     $this->Image("imagens/files/logo_boleto.png", 95, 8, 24);
     //$this->Image('imagens/files/'.$logo,2,3,30);
     $this->Ln(35);
     $this->SetFont($Letra, '', 10);
     $this->MultiCell(0, 4, $db12_extenso, 0, "C", 0);
     $this->SetFont($Letra, 'B', 13);
     $this->MultiCell(0, 6, $nomeinst, 0, "C", 0);
     $this->SetFont($Letra, 'B', 12);
     $this->MultiCell(0, 4, @$GLOBALS["head1"], 0, "C", 0);
     $this->Ln(10);
     $this->SetLeftMargin($S);
 }

 public static function GetChannels()
 {
     global $recent;
     $list = array();
     if (isset($_GET['recent'])) {
         if ($_GET['recent'] == "yes") {
             $recent = true;
         } else {
             $recent = false;
         }
         setcookie("recent", $_GET["recent"]);
     }
     if ($recent === NULL && isset($_COOKIE['recent']) && $_COOKIE['recent'] == "yes") {
         $recent = true;
     }
     if ($recent) {
         $query = pg_query("SELECT channel FROM active ORDER by channel");
     } else {
         $query = pg_query("SELECT channel FROM logs_meta WHERE name = 'enabled' AND value = 'True' ORDER by channel");
     }
     while ($item = pg_fetch_assoc($query)) {
         $list[] = $item["channel"];
     }
     return $list;
 }

function consultarUsuarioLoginSenha($name, $password, $conn)
{
    $query = "SELECT DISTINCT\n\t\t\t\t\t*\n\t\t\t\t  FROM \n\t\t\t\t\tusuarios u\n\t\t\t\t  WHERE\n\t\t\t\t\tu.nome = '" . $name . "' AND\n\t\t\t\t\tu.senha = '" . $password . "'";
    $queryResut = pg_query($conn, $query);
    $queryResut = pg_fetch_array($queryResut);
    return $queryResut;
}

function control()
{
    global $result, $input, $userID;
    $conn = pg_connect("host=postgredb.ctnfr2pmdvmf.us-west-2.rds.amazonaws.com port=5432 dbname=postgreDB user=postgreuser password=6089qwerty");
    if (!$conn) {
        echo "denied, an error occurred about connection.\n";
        exit;
    }
    $query = "SELECT USERNAME,USERID FROM PALUSER WHERE USERNAME LIKE '{$input}%' AND USERID != '{$userID}'";
    $result = pg_query($conn, $query);
    if (!$result) {
        echo "denied, an error occurred about query.\n";
        return 0;
    }
    $names = "";
    $ids = "";
    $first = true;
    while ($row = pg_fetch_row($result)) {
        if ($first) {
            $names = $row[0];
            $ids = $row[1];
            $first = false;
        } else {
            $names = $names . " " . $row[0];
            $ids = $ids . " " . $row[1];
        }
    }
    if ($ids == "") {
        echo "denied, empty result";
    }
    return $names . " " . $ids;
}

 function query_start($query)
 {
     // For reg expressions
     $query = trim($query);
     // Query was an insert, delete, update, replace
     if (preg_match("/^(insert|delete|update|replace)\\s+/i", $query)) {
         return false;
     }
     // Flush cached values..
     $this->flush();
     // Log how the function was called
     $this->func_call = "\$db->query_start(\"{$query}\")";
     // Keep track of the last query for debug..
     $this->last_query = $query;
     // Perform the query via std pg_query function..
     if (!($this->result = @pg_query($this->dbh, $query))) {
         $this->print_error();
         return false;
     }
     $this->num_queries++;
     // =======================================================
     // Take note of column info
     $i = 0;
     while ($i < @pg_num_fields($this->result)) {
         $this->col_info[$i]->name = pg_field_name($this->result, $i);
         $this->col_info[$i]->type = pg_field_type($this->result, $i);
         $this->col_info[$i]->size = pg_field_size($this->result, $i);
         $i++;
     }
     $this->last_result = array();
     $this->num_rows = 0;
     // If debug ALL queries
     $this->trace || $this->debug_all ? $this->debug() : null;
     return true;
 }

function get_node_map($node, $node_old)
{
    global $table_cable;
    global $table_pq;
    global $table_node;
    global $title;
    global $table_cable_type;
    $sql = "SELECT n1.id AS node_id_1, n2.id AS node_id_2, n1.address AS n1, n2.address AS n2, n1.id AS n1_id, n2.id AS n2_id, c_t.name AS cable_name, c_t.fib AS cable_fib\r\n        FROM " . $table_pq . " AS p1, " . $table_pq . " AS p2, " . $table_cable . " AS c1, " . $table_node . " AS n1, " . $table_node . " AS n2, " . $table_cable_type . " AS c_t\r\n        WHERE " . (isset($_GET['id']) ? "p1.node = " . $node . " AND p2.node != " . $node_old . " AND" : "") . " p2.id = CASE WHEN c1.pq_1 = p1.id THEN c1.pq_2 ELSE CASE WHEN c1.pq_2 = p1.id THEN c1.pq_1 ELSE NULL END END\r\n        AND p1.node = n1.id\r\n        AND p2.node = n2.id\r\n    \tAND c1.cable_type = c_t.id";
    //echo $sql.'<br>';
    //die;
    $result = pg_query($sql);
    if (pg_num_rows($result)) {
        while ($row = pg_fetch_assoc($result)) {
            if ($node_old == 0) {
                $title = $row['n1'];
            }
            if (isset($_GET['id'])) {
                if (add_arr($row['node_id_1'], $row['node_id_2'], $row['n1_id'], $row['n2_id'], $row['n1'], $row['n2'], $row['cable_name'], $row['cable_fib']) && $row['n2_id']) {
                    get_node_map($row['n2_id'], $row['n1_id']);
                }
            } else {
                add_arr($row['node_id_1'], $row['node_id_2'], $row['n1_id'], $row['n2_id'], $row['n1'], $row['n2'], $row['cable_name'], $row['cable_fib']);
            }
        }
    }
    return $content;
}

 function get_attributes()
 {
     $sql_str = "SELECT " . "name, " . "style_desc, " . "symbol_name, " . "symbol_size, " . "angle, " . "width, " . "color_r, " . "color_g, " . "color_b, " . "outlinecolor_r, " . "outlinecolor_b, " . "outlinecolor_g, " . "bgcolor_r, " . "bgcolor_g, " . "bgcolor_b " . "FROM " . "tng_mapserver_style " . "WHERE " . "id = " . $this->id;
     $this->dbconn->connect();
     $result = pg_query($this->dbconn->conn, $sql_str);
     if (!$result) {
         echo "An error occurred while executing the query - " . $sql_str . " - " . pg_last_error($this->dbconn->conn);
         //$this->dbconn->disconnect();
         return false;
     }
     // successfuly ran the query
     // store attributes
     $this->name = pg_fetch_result($result, 0, 'name');
     $this->style_desc = pg_fetch_result($result, 0, 'style_desc');
     $this->symbol_name = pg_fetch_result($result, 0, 'symbol_name');
     $this->symbol_size = pg_fetch_result($result, 0, 'symbol_size');
     $this->angle = pg_fetch_result($result, 0, 'angle');
     $this->width = pg_fetch_result($result, 0, 'width');
     $this->color_r = pg_fetch_result($result, 0, 'color_r');
     $this->color_g = pg_fetch_result($result, 0, 'color_g');
     $this->color_b = pg_fetch_result($result, 0, 'color_b');
     $this->outlinecolor_r = pg_fetch_result($result, 0, 'outlinecolor_r');
     $this->outlinecolor_g = pg_fetch_result($result, 0, 'outlinecolor_g');
     $this->outlinecolor_b = pg_fetch_result($result, 0, 'outlinecolor_b');
     $this->bgcolor_r = pg_fetch_result($result, 0, 'bgcolor_r');
     $this->bgcolor_g = pg_fetch_result($result, 0, 'bgcolor_g');
     $this->bgcolor_b = pg_fetch_result($result, 0, 'bgcolor_b');
     $this->dbconn->disconnect();
     return true;
 }

function transform($x, $y, $oldEPSG, $newEPSG)
{
    if (is_null($x) || !is_numeric($x) || is_null($y) || !is_numeric($y) || is_null($oldEPSG) || !is_numeric($oldEPSG) || is_null($newEPSG) || !is_numeric($newEPSG)) {
        return null;
    }
    if (SYS_DBTYPE == 'pgsql') {
        $con = db_connect(DBSERVER, OWNER, PW);
        $sqlMinx = "SELECT X(transform(GeometryFromText('POINT(" . pg_escape_string($x) . " " . pg_escape_string($y) . ")'," . pg_escape_string($oldEPSG) . ")," . pg_escape_string($newEPSG) . ")) as minx";
        $resMinx = db_query($sqlMinx);
        $minx = floatval(db_result($resMinx, 0, "minx"));
        $sqlMiny = "SELECT Y(transform(GeometryFromText('POINT(" . pg_escape_string($x) . " " . pg_escape_string($y) . ")'," . pg_escape_string($oldEPSG) . ")," . pg_escape_string($newEPSG) . ")) as miny";
        $resMiny = db_query($sqlMiny);
        $miny = floatval(db_result($resMiny, 0, "miny"));
    } else {
        $con_string = "host=" . GEOS_DBSERVER . " port=" . GEOS_PORT . " dbname=" . GEOS_DB . "user=" . GEOS_OWNER . "password=" . GEOS_PW;
        $con = pg_connect($con_string) or die("Error while connecting database");
        /*
         * @security_patch sqli done
         */
        $sqlMinx = "SELECT X(transform(GeometryFromText('POINT(" . pg_escape_string($x) . " " . pg_escape_string($y) . ")'," . pg_escape_string($oldEPSG) . ")," . pg_escape_string($newEPSG) . ")) as minx";
        $resMinx = pg_query($con, $sqlMinx);
        $minx = floatval(pg_fetch_result($resMinx, 0, "minx"));
        $sqlMiny = "SELECT Y(transform(GeometryFromText('POINT(" . pg_escape_string($x) . " " . pg_escape_string($y) . ")'," . pg_escape_string($oldEPSG) . ")," . pg_escape_string($newEPSG) . ")) as miny";
        $resMiny = pg_query($con, $sqlMiny);
        $miny = floatval(pg_fetch_result($resMiny, 0, "miny"));
    }
    return array("x" => $minx, "y" => $miny);
}

 public function postprocess_event($event)
 {
     $savepoint = $this->get_savepoint($event);
     switch ($event->tag) {
         case PGQ_EVENT_OK:
             $sql_release = sprintf("RELEASE SAVEPOINT %s", $savepoint);
             $this->log->debug($sql_release);
             $result = pg_query($this->pg_dst_con, $sql_release);
             if ($result === False) {
                 $this->log->notice("Could not release savepoint %s", $savepoint);
                 return PGQ_ABORT_BATCH;
             }
             break;
         case PGQ_EVENT_FAILED:
             $sql_rollback = sprintf("ROLLBACK TO SAVEPOINT %s", $savepoint);
             $this->log->debug($sql_rollback);
             $result = pg_query($this->pg_dst_con, $sql_rollback);
             if ($result === False) {
                 $this->log->notice("Could not rollback to savepoint %s", $savepoint);
                 return PGQ_ABORT_BATCH;
             }
             break;
         case PGQ_EVENT_RETRY:
             $sql_rollback = sprintf("ROLLBACK TO SAVEPOINT %s", $savepoint);
             $this->log->debug($sql_rollback);
             $result = pg_query($this->pg_dst_con, $sql_rollback);
             if ($result === False) {
                 $this->log->notice("Could not tollback to savepoint %s", $savepoint);
                 return PGQ_ABORT_BATCH;
             }
             break;
     }
     return True;
 }