PostgreSQL 9.13 + php入门篇(一）

一直想为 pg 做点贡献，今天终于有空了， 于是写一个PostgreSQL 9.13 入门的教程 ...

部署上可以移步这里 ...

php 5.4.10 + nginx1.0.12 + PostgreSQL 9.1.3 源码编译自动化部署第二版

http://www.52php.cn/article/p-aotagsej-hb.html

-----------------------------------------------------------------------------------------

| System | CentOS 5.7

-----------------------------------------------------------------------------------------

| DB | PostgreSQL 9.13

-----------------------------------------------------------------------------------------

lnpp脚本里面已经做了些初始化的工作，例如：

su postgres -c "$PG_ROOT/bin/initdb -D $PG_ROOT/data && exit"  

我们先输入一些数据以供后面查询(详见后面补充）

-- Database: bpsimple


-- DROP DATABASE bpsimple;


CREATE DATABASE bpsimple
 WITH OWNER = postgres
   ENCODING = 'UTF8'
   TABLESPACE = pg_default
   LC_COLLATE = 'en_US.UTF-8'
   LC_CTYPE = 'en_US.UTF-8'
   CONNECTION LIMIT = -1;
-- Table: item
-- DROP TABLE item;

CREATE TABLE item
(
  item_id serial NOT NULL,description character varying(64) NOT NULL,cost_price numeric(7,2),sell_price numeric(7,CONSTRAINT item_pk PRIMARY KEY (item_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE item
  OWNER TO neil;


以上我直接从pgadmin 3 上的sql pane copy 下来的，是我模拟器上的现有数据，所以以上语句没有经过测试 !

http://www.postgresql.org/docs/9.1/interactive/index.html 有问题的话,可以手册一下!
 

 
接下来我们还要对postgresql 进行一些配置已经进行外部的访问 ...
先进行访问授权 ...
#vim $PG_ROOT/data/pg_hda.conf
host  bpsimple neil       all                   trust
 
#vim postgresql.conf
listen_addresses = '*'
 
port = 5432
 
设置完监听端口后我们重启一下postgresql ...
su $PGUSER -c "$PGCTL stop -D '$PGDATA' -m fast"
 
su $PGUSER -c "$PGDAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
 
具体环境变量视不同机子而定，好吧，主题开始，首先编写一个pg类 ...
#vim ./pgphp/dbconn.php

 

<?php

class dbconn {

    private $linkid;      // PostgreSQL link identifier
    private $host;        // PostgreSQL server host
    private $db;          // PostgreSQL database
    private $user;        // PostgreSQL user
    private $passwd;      // PostgreSQL password
    private $result; // Query result
    private $querycount; //Total queries excuted

    /* Class constructor. Initializes the $host,$user,$passwd
      and $db fields. */

    function __construct($host,$db,$passwd) {
        $this->host = $host;
        $this->user = $user;
        $this->passwd = $passwd;
        $this->db = $db;
    }

    /* Connects to the PostgreSQL Database */

    function connect() {
        try {
            $this->linkid = @pg_connect("host=$this->host dbname=$this->db
            user=$this->user password=$this->passwd");
            if (!$this->linkid)
                throw new Exception("Could not connect to PostgreSQL server.");
        } catch (Exception $e) {
            die($e->getMessage());
        }
    }

    /* Execute database query. */

    function query($query) {
        try {
            $this->result = @pg_query($this->linkid,$query);
            if (!$this->result)
                throw new Exception("The database query failed.");
        } catch (Exception $e) {
            echo $e->getMessage();
        }
        $this->querycount++;
        return $this->result;
    }

    /* Determine total rows affected by query. */

    function affectedRows() {
        $count = @pg_affected_rows($this->linkid);
        return $count;
    }

    /* Determine total rows returned by query */

    function numRows() {
        $count = @pg_num_rows($this->result);
        return $count;
    }

    /* Return query result row as an object. */

    function fetchObject() {
        $row = @pg_fetch_object($this->result);
        return $row;
    }

    /* Return query result row as an indexed array. */

    function fetchRow() {
        $row = @pg_fetch_row($this->result);
        return $row;
    }

    /* Return query result row as an associated array. */

    function fetchArray() {
        $row = @pg_fetch_array($this->result);
        return $row;
    }

    /* Return total number of queries executed during
      lifetime of this object. Not required,but
      interesting nonetheless. */

    function numQueries() {
        return $this->querycount;
    }

}
?>


然后开始进行调用吧 ...

#vim dbtest.php



<html>
    <title> pgtest</title>

    <?php

    require_once 'dbconn.php';

    $db = new dbconn("localhost","bpsimple","postgres","");
    $db->connect();

    $db->query('SELECT * FROM item');

    echo 'number of row:' . $db->numRows();

    ?>

</html>


接下来就可以访问 http://hostname/pgphp/dbtest.php




它会输入 item 的行数 ...



QQ:213572677 && linux c ph sql





Reference :
Beginning.PHP.and.PostgreSQL.8.From.Novice.to.Professional.Feb.2006


Beginning.Databases.With.PostgreSQL-From.Novice.To.Professional.2nd.Edition

 
 

  
  PostgreSQL 9.1.3 docs
 
 
http://www.postgresql.org/docs/9.1/interactive/index.html





2012/4/10补充的sql初始化：

#su postgres

pg$  /tmp/lnpp/pgsql/bin/createuser neil
$ /tmp/lnpp/pgsql/bin/createdb bpsimple
$ /tmp/lnpp/pgsql/bin/psql -U neil -d bpsimple


create table item
(
    item_id                         serial,description                     varchar(64) not null,cost_price                      numeric(7,sell_price                      numeric(7,CONSTRAINT                      item_pk PRIMARY KEY(item_id)
);

INSERT INTO item(description,cost_price,sell_price) 
VALUES('Wood Puzzle',15.23,21.95);
INSERT INTO item(description,sell_price) 
VALUES('Rubik Cube',7.45,11.49);
INSERT INTO item(description,sell_price) 
VALUES('Linux CD',1.99,2.49);
INSERT INTO item(description,sell_price) 
VALUES('Tissues',2.11,3.99);
INSERT INTO item(description,sell_price) 
VALUES('Picture Frame',7.54,9.95);
INSERT INTO item(description,sell_price) 
VALUES('Fan Small',9.23,15.75);
INSERT INTO item(description,sell_price) 
VALUES('Fan Large',13.36,19.95);
INSERT INTO item(description,sell_price) 
VALUES('Toothbrush',0.75,1.45);
INSERT INTO item(description,sell_price) 
VALUES('Roman Coin',2.34,2.45);
INSERT INTO item(description,sell_price) 
VALUES('Carrier Bag',0.01,0.0);
INSERT INTO item(description,sell_price) 
VALUES('Speakers',19.73,25.32);


2012/4/11 补充

有关有无密码登录,主要是在pg_hda.conf里面进行修改

host  bpsimple neil       all                   trust (无密码登录）

host  bpsimple neil       all                   md5 ( 需要密码）local  bpsimple neil       all                   trust (无密码登录）

设置用户密码:
#su postgres
$/tmp/lnpp/pgsql/bin/psql -d dbname -U postgres -c "alter role postgres password ‘yourpassword’;"






    
        
 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!