PHP / Postgresql Examples and Functioning Code
This tutorial will assume that you have both
PHP3 and Postgresql 6.5
installed and functioning. Both PHP3 and Postgresql have very detailed
instructions in the source code directory. Read the REAME and INSTALL
files for each, and follow them EXACTLY as written for a smooth install.
IF you try to jump ahead or skip steps then you will not have a proper
install and will have to start over from scratch.
We will start with a few basics and build a Postgresql database for these
examples. Then we will show you how to implement PHP3 code into your web
files to access the newly created database. This site is for beginners
of PHP3 usage, therefore you will not see very much as far as
complicated code. The advanced PHP3 users section is still in process of
being designed, please check back frequently for its arrival. For now you
may check out rd.linux-help.org for a
sample of more complicated code.
For best results please follow the entire tutorial step by step so you get
the best possible understanding of the process from database creation to
PHP3 usage.
Lets begin the creation of our new database.
For your convience all issued
commands will be in bold.
Make sure postmaster is
running with the -i switch. You may do ps ax |grep post on the
commandline as root to see if it is running. If its running proceed to the
next step. If it is not running then login as the postgres user and start
it up, NEVER start postmaster as any other user than postgres. You can
start it up with a command like this /usr/local/pgsql/bin/postmaster -i
& If you do not know the full path to the executable you can run this
command as root to find it which postmaster
Now as the postgres user type CREATEDB phonelist then hit enter.
This will create the database called phonelist.
Now i will give you the schema of the database. Schema means the
tables, indexes, and so forth that gives your database its guts. First as
your postgresql user type psql and that will put you into the
database with a commandline interface. (NOTE: Some of you may know
postgres pretty well and be used to using pgaccess, feel free to create
your database that way if you are more comfortable with it.) Now type
\connect phonelist
Once you are connected to the phonelist database you will issue this
command to create a couple of sequences. Sequences are unique numbers that
identify each record and are inserted sequentually with each new entry:
CREATE SEQUENCE names_id_seq START 1 INCREMENT 1;
CREATE SEQUENCE id_seq START 1 INCREMENT 1;
Now lets give the database a table with some fields.:
CREATE TABLE "address" (
"last" character varying(30),
"first" character varying(30),
"phone" character varying(30),
"address" character varying(120),
"birthday" character varying(20),
"email" character varying(30),
"fax" character varying(30),
"beeper" character varying(30),
"cellphone" character varying(30),
"notes" character varying(130),
"dateinserted" datetime DEFAULT now ( ) NOT NULL,
"city" character varying(30),
"state" character varying(30),
"zip" character varying(30),
"spouse" character varying(30),
"workphone" character varying(30),
"id" int4);
Now let create the index
CREATE INDEX people ON address (last,id);
NOTE: It is assumed that you have some meaning of what the above commands
just did. If you do not, the working examples and code will still work if
followed step by step, but i recommend that you review the
PHP3 and
Postgresql manuals
for best comprehension.
You should now have a fully functionally
database called phonelist.
Lets now add some data so we can play with the new database a bit and get
comfy with it. We will add some names and phone numbers as well as the
rest of the fields to the database, by using the INSERT command.
You may cut and paste the next steps into the database to save time.
(NOTE: there are a couple of ways to add data to a database i am taking
all shortcuts so if you don't understand how database's work please read
the postgresql manual
for better understanding)
INSERT INTO address VALUES('Smith', 'Tom', '(555) 555-9564', '1212 Jones
Dr', '5-8-63', 'tsmith@isp.com', '(555) 555-9993', '241-9867', '908-5894',
'Linuxhelper example', 'NOW', 'Sometown', 'USA', '64598', 'Jane',
'(555) 954-8956', NEXTVAL('id_seq'));
INSERT INTO address VALUES('Jones', 'James', '(465) 444-8974',
'5461 Smith Lane','6-4-66','jjones@psi.net','(465)
444-9762','652-5643','809-7612','Linuxhelper example', 'NOW', 'Sometown',
'USA', '94856','Barb','(465) 659-8946',NEXTVAL('id_seq'));
Now using the schema above go ahead a make a couple of your own
INSERTS to make the database more fun to play with, and more
personal for you. Just make sure that when using the schema and creating
your inserts that you insert the data in the order of the schema and
NEXTVAL('id_seq')); will ALWAYS be the last thing inserted. Schema order
is as
follows:last,first,phone,address,birthday,email,fax,beeper,cellphone,notes,
dateinserted,city,state,zip,spouse,workphone.
You may use 'NOW' for the dateinserted to get the current time and date as
i did above.
Now lets try a couple of select statements to
see how it pulls information
from the database.
First lets do a very simple basic select statement that will count how
many entries you have in your database.
SELECT count(*) from address;
You should get something like this with the total count number depending
on how many extra entries you added from above. If you didn't add any
extra then the count should be 2.
phonelist=> select count(*) from
address; count
-----
26
(1 row)
Now lets pull one specific file from the database
SELECT * from address where first = 'James';
You should get this exact output. HINT: make sure to do \x
before you do your select statement in order for
your output to have this same format.
phonelist=> select * from address where first = 'James';
Field | Value
-- RECORD 0 --
last | Jones
first | James
phone | (465) 444-8974
address | 5461 Smith Lane
birthday | 6-4-66
email | jjones@psi.net
fax | (465)
444-9762
beeper | 652-5643
cellphone | 809-7612
notes | Linuxhelper example
dateinserted| Thu Oct 07 08:29:43 1999 EST
city | Sometown
state | USA
zip | 94856
spouse | Barb
workphone | (465) 659-8946
id | 33
(1 row)
Ok now your database is functional and ready to
be used with PHP3 to
access it via your web server. To learn more about SELECT
statements and other types of queries see the Postgresql manual.
When we leave this page and begin our journey into PHP3 all the code
will be color coded for your convience and to catch your eye. I will be
doing this by using the extention .phps for these examples instead of the
standard extention .php3. In order for the sample code to actually work
you will need to use the standard extention .php3 when you name your
pages. Without further ado lets move onto the PHP3 intergration part of
this tutorial, just click here to be on your way.
|