A simple web application with php and postgresql

This tutorial will walk you through the creation of a simple web application in php, that accesses a postgresql database. The tutorial focuses on simple ways to implement the application; keep in mind that simplest is not always best, but in this tutorial we focus on simple.

To get the most out of the tutorial, don't just read it. Execute the programs, use the 'view source' option on your browser to see the html that the php pages are generating, and change the programs to make sure you understand them.

The application

So, what is the application ? We will create a database of users, where each user can have many emails (hey, we said the app would be simple :). Accordingly, we have two tables, with the following structure (the file emails_db.sql also inserts a few tuples on each table):

And our application will let us display all users, display the emails for each user, and add users and emails.

Basic page structure

It is usually a good idea to create a template for your web pages; this way, you can just 'save as' and you ensure consistency and save some work. I tend to create a file called empty.html and an empty.php, with the templates.

Here is the empty.html file. Notice that it references a css stylesheet. This lets us

Now, for the php template, we probably want to include the code for connecting to the database, since in most pages we will be connecting to the db (hey, this is a database application); we could just put the call to pg_connect, but if we ever need to change the password (or the database or user, for that matter) then we would need to change ALL of the php pages, so we better put the code to connect in one file and include it in all the others. We can use the include function (actually, include_once or require once would probably be safer). So we create a file called connect.php that contains the following code:

And then our php template (called empty.html) would look like this:

Basic php code

1. Get information from the request (using $_REQUEST)
2. Do something with the database (use pg_query)
3. Produce some output, depending on what we did

Listing users

We can start by creating the page to list all users. The beginning is the same as in our template; we include connect.php and put all the html boilerplate. In the php code, we start by defining a function; the function is called display_users and takes as argument the handle returned by pg_query. As with most languages, this is just the definition of the function, nothing is done until the function is called.

The actual execution starts in line 22. First, we define a string variable, containing the query we will execute. Notice there is nothing magic about it yet; it is just a string variable. It's value is the string representation of the SQL command we want to execute, but as far as php is concerned, this is just a string.

Then, we call pg_query. We pass it the string stored in $query, so that is the query sent to the database (remember we had already established the connection, in the file we include at the top), and we store the returned value in the variable $result.

Notice pg_query returns a handle (basically an integer identifying a particular resource); this is an opaque reference to the result set of the query; we pass this value to the other pg functions (pg_num_rows and pg_fetch_object etc)

Now we pass the value returned by pg_query into the display_users function. This function displays the elements in the result set as a table. (Now would be a good time to execute the listUsers0.php program, and make sure you understand the code and the html that is being generated)

Making it prettier with css

Now, our html pages reference a css stylesheet (mystyle.css). A stylesheet lets us change the way the browser will display our html; we can change font and background colors, add borders etc. The stylesheet we have is:

The first line says that anything inside an h1 tag should be centered (we normally use h1 for the title at the top).The third line says that tables should have a solid border around them, 2 pixels wide and in black color.

The next two lines (4 and 5) are slightly more complicated; line 4 says that anything inside a tag of class odd should have a bluish background color; line 5 says anything of class even should have a white background; We can use this trick to make our tables alternate colors; we simply add a class="odd" or class="even" to the <tr> tags.

The file listUsers.php does just that. Notice that the only thing we changed is the display_users function (that's how functions make our lives easier; we know where to go make changes !).

Displaying a particular user and its emails

The next step is to display a user with all its emails. This entails two queries (one to get the info from the MyUser table and another from the UserEmails table). Now this page needs to know who to display the info about, so we need to pass it the username of the desired user.

The simplest way to pass info to a php page is to use an html form; we just need to make sure to point to the right php page in the action parameter, and create fields with the right name. So, a very simple form could look like this:

And then the php for it could be:

In this page we first (line 10) define a function to display emails, similar to the one in listUsers; the actual execution starts in line 25; we get a value from the request (notice we do $_REQUEST['username'] , here username is the name of the field on the form), and store it in the variable $username. In line 26, we use that value to create a SQL query that will get, from the MyUser table, the name corresponding to that username. Line 28 actually sends that query to the database. Line 29 fetches the resulting row into an objetc (notice exactly one row should be returned) and line 30 prints it. Line 31 creates another SQL query, this time to get all the emails from the UserEmails table that correspond to the username. Line 32 executes that query, and line 33 calls the displayEmails function, that creates the html table etc

Security and validation

Now this page works, but only if it gets the right data; it is not checking whether the user actually exists etc; in fact, we are not even checking whether we are getting any data from the form !

Also, since the page uses data from the form directly into the query, a malicious user may enter invalid data that closes the ', adds a semicolon, and then another SQL command that changes your database, or reveals more info than it should (as a challenge, you may try to actually create such a string and type it into the form). In Security parlance, this is called a SQL injection attack. An easy way to avoid most of such attacks is to remove the ' character from the string. php has a function called addslashes that does just that (actually, it replaces the ' character with \', which guarantees that it enters your db as data instead of being a metacharacter). An even better approach, which we're doing in all our examples, is to use prepared statements (pg_query_params) to do all the queries involving user input.

So a better implementation would look like:

Here in line 25 we use the isset function to check whether the variable username is coming through the request; we then use addslashes in line 26, to sanitize the data and avoid the SQL injection attack; finally, in line 30, we check the number of rows returned by our first query; if there is no user with that username, then 0 rows are returned.

Sending data inside a link

Now, our listUsers.php page displays all users; it would be nice if we could add a link there to display the emails for each particular user. If you tried the form above you probably saw that the variable name and value gets added to the url, so it now looks like: htt://okaram...../displayUser.php?username=okaram , the variable username, with value okaram is being sent to displayUser.php; we can use php to create a link of that form, and display the emails for the appropriate user.

So, here is listUsers2.php, that does just that.

Adding users

Another obvious piece of functionality would be the ability to add data to the database, or modify existing data. The basic structure is the same. Create an html form that allows to send all the necessary data, and a php page that uses that data to create an SQL statement (in this case an INSERT, DELETE or UPDATE instead of a SELECT statement) and uses pg_query to have the DBMS execute that statement.

As an example, we create a form, addUser.html that has the data for the MyUser table:

Notice that we use an input of type checkbox at the end of the form, to display a textbox. We also use an input of type password; this is just a normal textbox, except that it displays * instead of the characters you type.

The php page is relatively simple; the only variable that gets special treatment is isadmin, which needs to be transformed. If the checkbox is not checked, the variable is not sent at all, wheras if it is checked, the string 'on' is sent as its value; so we use isset to check whether it has been sent or not, and we transform that to "true" or "false", since that is what needs to go in the SQL statement.

Notice that the main difference between this page and displayUser.php is in the kind of SQL statement. Line 19 creates the INSERT statement, and line 21 sends it to the DBMS.

Now, this doesn't do anything to validate the data. A more robust implementation would look like this:

Notice that the validateIncomingData function checks that all fields are coming in the request, and that they all have a value. It also checks that the user doesn't already exist in the database.