★ wanayoo — archive 1999 http://linuxhelper.org/examples/php-post.php3Nouvelle recherche | Portail wanayoo
     
























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.

Home
Home
Home
Home
Home
Home
Home
Home
Home
Home
Home

Tutorial Index
Tutorial Index
Tutorial Index
Tutorial Index
Tutorial Index
Tutorial Index
Tutorial Index
Tutorial Index