PostgreSQL 9.13 + PhP (1)

I always wanted to contribute to pg, and finally I am free today, so I wrote a tutorial for PostgreSQL 9.13 introduction ...

You can move here for deployment ...

PHP 5.4.10 + nginx1.0.12 + PostgreSQL 9.1.3 source code compilation and automatic deployment of the second version

http://blog.csdn.net/qzier_go/article/details/7316510

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

| System | CentOS 5.7

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

| DB | PostgreSQL 9.13

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

Some initialization work has been done in the lnpp script, for example:


su postgres -c "$ PG_ROOT / bin / initdb -D $ PG_ROOT / data && exit"
We first enter some data for later query (see the supplement later)


-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,2),
  CONSTRAINT item_pk PRIMARY KEY (item_id)
)
WITH (
  OIDS = FALSE
);
ALTER TABLE item
  OWNER TO neil;


The above I directly copied from the sql pane on pgadmin 3 is the existing data on my simulator, so the above statement has not been tested!


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

  If there is a problem, you can handbook it!





Next we have to configure some postgresql and have external access ...


Authorize access first ...


#vim $ PG_ROOT / data / pg_hda.conf


host bpsimple neil all trust





#vim postgresql.conf


listen_addresses = '*'


port = 5432





After setting the listening port, we restart postgresql ...


 su $ PGUSER -c "$ PGCTL stop -D '$ PGDATA' -m fast"


 su $ PGUSER -c "$ PGDAEMON -D '$ PGDATA' &" >> $ PGLOG 2> & 1





The specific environment variables depend on different machines, okay, the topic begins, first write a pg class ...


#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, $ user, $ 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;
    }

}
?>


Then start calling ...


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


Then you can visit http: //hostname/pgphp/dbtest.php



It will enter the number of rows of 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 supplementary SQL initialization:



#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,2),
    sell_price numeric (7,2),
    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, cost_price, sell_price)
VALUES ('Rubik Cube', 7.45, 11.49);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Linux CD', 1.99, 2.49);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Tissues', 2.11, 3.99);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Picture Frame', 7.54, 9.95);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Fan Small', 9.23, 15.75);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Fan Large', 13.36, 19.95);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Toothbrush', 0.75, 1.45);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Roman Coin', 2.34, 2.45);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Carrier Bag', 0.01, 0.0);
INSERT INTO item (description, cost_price, sell_price)
VALUES ('Speakers', 19.73, 25.32);


2012/4/11 Supplement

The login with or without password is mainly modified in pg_hda.conf

host bpsimple neil all trust (login without password)

host bpsimple neil all md5 (password required) local bpsimple neil all trust (login without password)


Set user password:

#su postgres

$ / tmp / lnpp / pgsql / bin / psql -d dbname -U postgres -c "alter role postgres password‘ yourpassword ’;"