Beginning PHP and PostgreSQL
Chris on 2001 May 12
Chris on 2001 May 12
PHP is very similiar to ASP. It's a server-side scripting language, which means the web-server does all the work, not the browser. This means it's pretty fast, and you can do pretty complex things fairly easily.
Now, let's start with the basics of PHP. Create a file called test.php with the following
<html>
<body>
<?php
$myvar = "Hello world";
echo $myvar;
?>
</body>
</html>
To access it, go to http://yourserver/test.php
This will output "Hello world" to the screen. If you display the source, it will only show the HTML tags, and "Hello world" in the body of the document. The server did all the work, processed all the code it had to, and returned only the HTML to the screen. The tags that do the work are called the "delimiters".
<?php
shows the start of the PHP code, and
?>
tells you that's the end of the PHP code. When you develop more complex scripts, these can be used anywhere through-out the code. The delimiters can be changed to something else, so if you're used to ASP coding, it's fairly easy to change them.
After each line of code you need the semicolon. These seperate the set of instructions from one another, and it is called a seperator. You could write a whole script with only semicolons seperating the instructions, but this would be messy. Just remember, each command has to end with a semicolon.
The word myvar begins with a dollar sign. This tells PHP it's a variable, and to assign the value "Hello world" to the variable. Other variables can contain numbers, or arrays, but all variables start with the dollar sign.
PHP has a lot of functions built into it that we can take advantage of.
Let's do something more interesting with our script.
Create a file called "info.php" with the following text:
<html>
<body>
<?php
phpinfo();
?>
</body>
</html>
When you view this script through your web browser, (http://yourserver/info.php) it will show a lot of information about it, including the PHP version, the version of Apache, and a lot of other variables PHP uses. Down the bottom in the section called Additional Modules, look for PostgreSQL, or just find it on the page. If it isn't there, review the installation steps from the start.
Now we'll look at how to connect up to the PostgreSQL database. First of all, we'll need to put some data into the database to be able to get it back out again. This will only be a very simple example, see the PostgreSQL documentation for more information, or contact me, if you need some help.
We're going to create a simple "friends" database with some dummy names to put in it.
First, we have to obviously create the database. This is done by typing this at the prompt:
postgres@designmagick:/usr/local/pgsql > createdb friends
It will either give back some output saying:
postgres@designmagick:/usr/local/pgsql > createdb friends
CREATE DATABASE
Or just another prompt. If you get something like this:
Connection to database 'template1' failed.
FATAL 1: SetUserId: user 'username' is not in 'pg_shadow'
createdb: database creation failed on friends.
it means that you aren't doing this as the PostgreSQL user that was setup when you installed it. Change to the PostgreSQL user and try again. The advantage of PostgreSQL doing this, is that it won't let you compromise your system by running it as root.
If we now do this:
postgres@designmagick:/usr/local/pgsql > psql -l
datname |datdba |encoding |datpath
---------------+----------+--------------+------------
friends | 26 | 0|friends
(1 rows)
This is the output for PostgreSQL V 6.5.3. For V7.0.2, we get this output:
postgres@designmagick:/usr/local/pgsql > psql -l
List of databases
Database | Owner
--------------+----------
friends | postgres
CREATE TABLE friends (id serial, firstname char(10), surname char(10));
INSERT INTO friends values (nextval('friends_id_seq'), 'Test', 'User');
INSERT INTO friends values (nextval('friends_id_seq'), 'Your', 'Name');
If the lines wrap, make sure the insert commands are on new lines. It won't work if the insert commands aren't on new lines. Now, we run the command to insert the values into the database.
postgres@designmagick:/usr/local/pgsql > psql -d friends -f input
You should get some output looking like this:
postgres@designmagick:/usr/local/pgsql > psql -d friends -f input
psql:input:1: NOTICE: CREATE TABLE will create implicit sequence 'friends_id_seq' for SERIAL column 'friends.id'
psql:input:1: NOTICE: CREATE TABLE/UNIQUE will create implicit index 'friends_id_key' for table 'friends'
CREATE
INSERT 279808 1
INSERT 279809 1
What this means is "connect to the database friends (-d friends) and do what the file input tells you to do (-f input)". So this tells the friends database to create the table "friends" inside the database with the field names id, firstname, and surname. The nextval command gets the next id (or value) in the id list. We'll cover this a bit later in our datatypes section. For now, it puts values "1, Test, User" for the first line, and "2, Your, Name" for the second line. Another thing handy to know is that only strings have to be in quotes, numbers are ok without them.
Now we'll have a look at how to get the data back out of the database. Copy this into a text file, and give it a .php or .php3 extension.
<html>
<body>
<?php
$db = pg_connect("dbname=friends user=htdocs");
$query = "SELECT * FROM friends";
$result = pg_exec($db, $query);
if (!$result) {printf ("ERROR"); exit;}
$row = pg_fetch_row ($result,0);
printf ("ID : %s<br>
", $row[0]);
printf ("First Name: %s<br>
", $row[1]);
printf ("Surname : %s<br>
", $row[2]);
pg_close($db);
?>
</body>
</html>
It will most likely come up with this:
Warning: PostgresSQL query failed: ERROR: friends: Permission denied. in test.php on line 6
What's the problem? We haven't given the webserver access to the database! First of all, we need to find out what user the web server is running as. At the prompt:
designmagick:~ # ps aux | grep http
or
designmagick:~ # ps -aux | grep http
This will produce something like this:
root 209 0.0 5.2 3292 1612 ? S May 10 0:00 /bin/httpd
htdocs 211 0.0 5.1 3408 1588 ? S May 10 0:00 /bin/httpd
htdocs 212 0.0 6.5 3456 1996 ? S May 10 0:00 /bin/httpd
The first column is the only one we're interested in. We're looking for the user that is NOT root - if we used "root", then we would introduce a big security problem. "Root" will start the first one, but the rest run as their own user. So the web server runs as "htdocs". Now we need to give the "htdocs" access to the database. Obviously at this point, change the username to the one that your web server runs as. We have to create the user first, then give it access to the database.
postgres@designmagick:~$ createuser htdocs
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
Now we give them access. Edit a file called "access" with this in it:
GRANT all ON friends TO htdocs;
Now, we grant access to the user:
postgres@designmagick:/usr/local/pgsql > psql -d friends -f access
It should return something like this:
GRANT all ON friends TO htdocs;
CHANGE
EOF
If we try our PHP script again, it should come up with this :
ID : 1
First Name: Test
Surname : User
as our webpage. We now have PHP linking into with our postgresql database. Before we go any further, I will explain some of what we've done.
<?php
$db = pg_connect("dbname=friends user=htdocs");
$query = "SELECT * FROM friends";
$result = pg_exec($db, $query);
?>
These lines connect to the database on our system.
<?php
$db = pg_connect("dbname=friends user=htdocs");
?>
the pg_connect command opens a connection to the database. Check out www.php.net/manual/en/function.pg-connect.php.
<?php
$query = "SELECT * FROM friends";
?>
This is the query we're running against the database. We're only doing a simple one at the moment, in a later tutorial we'll deal with more complex queries, with things like updating, deleting data from the database.
<?php
$result = pg_exec($db, $query);
?>
This actually performs, or "executes" the query against the database.
<?php
if (!$result) {printf ("ERROR"); exit;}
?>
The query performs a result. If there is NO result - (!$result) - print an error message and exit the script so it doesn't do anything else. If a result is found, (ie it does the query ok), it will continue onto the next step. This will be covered in a bit more detail later on in our error section.
<?php
$row = pg_fetch_row ($result,0);
?>
This means that the "row" of information that we get is the data from each of the tables from the database is put into the $row variable.
<?php
printf ("ID : %s<br>
", $row[0]);
printf ("First Name: %s<br>
", $row[1]);
printf ("Surname : %s<br>
", $row[2]);
?>
So, we print out the results.
The %s means its a string variable type. In the place of %s, print the value $row[0] - The [0] means the first element of the array - which in our case is the ID. This comes from C programming, so if you know that, it will help here a little bit. The second line prints the first name, the third line comes from the surname. If we had 5 tables in our database, the array values would go up to 4, since the first element is always element 0, and so on.
So, if we wanted the second row from the table, we could do something like :
<?php
$row = pg_fetch_row ($result,1);
?>
If you tried this now, your output would display like this :
ID : 2
First Name: Your
Surname : Name
<?php
pg_close($db);
?>
</body>
</html>
Here we close the connection to the database. We also end the PHP script (see above), and close the HTML file off properly.