Web Development Intensive

Working off of the example code from the Basic SQL example files, here is a slightly more sophisticated set of pages that when used together, create a sort of Content Management System for our animals application.

How it works

Here is a user flow diagram outlining how this application fits together.  Below the diagram is an overview of each step.

User flow diagram for Basic SQL CMS

read.php

In this version of the files, the pages that handle each of the CRUD commands are all linked together.  A user can view the animals stored in the database on the main page, read.php. Read.php simply queries the database for a list of all the rows stored in the animals table, and outputs each of those rows into the XHTML for the page.

The user can click links to “update” or “delete” any individual animal.  When they click either of those links, the id of the selected animal is passed in the query string of the link to the corresponding page, either update.php or delete.php.

update.php

Update.php displays a form that the user can use to change the name of the animal that was clicked.  The first time this page is loaded, this form is prepopulated with the existing name of the animal that was clicked, which has been retrieved from the database.

The id of the animal and its new name are passed along with another request for update.php once the user clicks the submit button.  Update.php this time updates the selected row in the animals table and redirects the user back to read.php, where the user can see the updated list of names.

delete.php

Delete.php simply runs a query that deletes the selected row from the database, and then redirects the user to read.php, where they can see the updated list of animals.

create.php

A user can click a link to “add a new animal” on read.php.  This takes them to create.php, where they can enter a new animal name into a form.  When they submit the form, the animal name is passed along with another request for create.php.

Create.php this time creates a new row in the database table for the new animal, and then redirects the user to read.php, where they can see the updated list of animals.

If you have finished the in-class assignment of creating a message board, you are ready for the advanced assignment.

The advanced assignment is to add the ability for users to upload images along with their message posts.

add_post.php

So when users add new posts, they enter in their name, the title of the post, the body of the post, as well as the image that goes along with the post.

Here’s the updated wireframe for the add_post.php page:

updated add_post.php wireframe

You will want to check out the upload file example on the server here.

You use an <input type=”file” …> tag to allow users to select a file to upload.

Make sure your form has the “enctype” attribute set to “multipart/form-data”.  This indicates to the server that it should expect to be receiving binary data for the file.

process_post.php

When the server receives the data that the user submits along with the HTTP POST request for process_post.php, it has to store the data in the database, as you have already done in the first part of this assignment.

You will want to create a new “image_path” field in your messages table in the database where you will store the path where you uploaded the image.  You can get the path where the image is uploaded by going through the file upload example code linked above.

So when you create the new row in the database table, you will be storing the author, post title, image path, and post body in the database table.

index.php

And when a user views the list of all the messages, if there is an image that has been uploaded along with a particular message, that image shows up next to the message.  If there is no image associated with a post, the layout should adjust accordingly.

Here is the updated index.php wireframe:

updated index.php wireframe

Very advanced assignment

If this was too easy for you, here is a very advanced assignment.  Require the users to register with your site before they can post a message.

Users should be able to view the home page whether they are registered or not.

But only registered users should be able to post a new message.  If a user has not registered, they should be redirected to the index.php page if they try to view the add_post.php or process_post.php pages.

Your assignment this class is to create a message board.  The message board allows users to view all of the messages that have been posted to the board so far.  It also allows users to post new messages.

Here is the user flow of the site:

User flow of message board site

index.php – the message list page

When the user first comes to the site, they see the main page, index.php.  This page shows them a list of all of the messages on the board in reverse chronological order.  The page reads all of the rows of data from the database table and displays them.

This page also has a link to “add a new post”.  When the user clicks that link, she is brought to add_post.php.

index.php wireframe

You will want to use code similar to what is available in the read.php example available on the server here.

Also, you will eventually want to format the dates that you retrieve from the created field of the database table, you will want to read this post about beautifying MySQL timestamps.

add_post.php – the post page

add_post.php consists of a form the user can fill out in order to post a new message.  This form has three fields: the user’s name, the post title, and the post body.  When the user clicks submit, this page makes an HTTP GET request for process_post.php, and passes along the data that the user entered into the form as part of the query string of the URL of the request.

add_post.php wireframe

This page can be a simple XHTML page.  It’s ok to name it add_post.php even if it just has XHTML code inside of it.

process_post.php – the process post page

The process_post.php script receives the data that was passed in the GET request to the server by using the built-in PHP $_GET or $_REQUEST variables, and enters that information as a new row in the database table.

You will want to use code that is similar to the PHP used in the create.php example available on the server here.

This script will then redirect the user back to the main page, index.php.  Check out this example of how to redirect a user from one page to another.  You will be using the built-in header() function in PHP in order to pass a special “Location” HTTP header from the server to the client that instructs it to go make a request for a different page.

For example, this code redirects a user to the nytimes.com website:

header("Location: nytimes.com"); //redirect to another page

Create

INSERT INTO abloomberg_animals (name, color) VALUE (‘zubr’, ‘brown’)

Read

SELECT * FROM abloomberg_animals WHERE 1
SELECT name, color FROM abloomberg_animals WHERE id=15

Update

UPDATE abloomberg_animals SET name=’zubr’ WHERE id=15
UPDATE abloomberg_animals SET name=’zubr’, color=’brown’ WHERE id=15

Delete

DELETE from abloomberg_animals WHERE id=15

Tables of data

Relational databases treat data in the same grid-like way as an Excel spreadsheet.  There are rows and columns of data.  But databases have extra features that make them easier and more intuitive to work with.

Databases, tables, rows, and columns

Each relational database can hold multiple tables.  Tables are the spreadsheet-like grids of rows and columns of data.   So if you are familiar with Microsoft Excel documents. you can think of the database as the Excel document, and the tables as the individual sheets within that document.

Databases are faster

With plain text files, As the amount of data stored grows, so too does the amount of time it takes for the server to open the text file and go line-by-line through it.  After only several hundred lines of data, there will be a noticeable lag when reading data from a text file.  The same problems exist for writing, updating, and deleting data – the more data you have, the more time-consuming those operations get, and other complications crop up.

Databases are optimized to quickly do the most common tasks with data: Creating new data in storage, Reading data from storage, Updating data, and Deleting data.  These tasks, called CRUD for short, are exactly what databases like MySQL are designed to do quickly and efficiently.  A database server can loop through thousands of rows of data in a tiny fraction of a second.

The database uses indexes to pre-sort data in a variety of ways to make it faster to sort through when you eventually request data from it.

Databases respond to queries

Databases also allow you to pinpoint individual pieces of data relatively quickly.  At a high level, you can make a request to the database to return particular rows and columns of data you want to access (whether it be for creating, reading, updating or deleting), and the database will automatically find that data and return it without you having to loop through the other rows and columns manually.

Of course it gets complicated, like everything else, but the idea is straightforward.  You make a request for data, and the server responds.  A request like this for a particular set of data is called a “query”, and SQL is the language we will use to make queries.

A typical SQL query to request the contents of all the rows out of a table called “users” would look something like this:

SELECT * FROM users

We’ll get into the details of SQL syntax later.

Relationships between tables

Data in one table is often related to data in other tables.  This can be most easily explained through an example.

Let’s say, hypothetically, that we’re building a site where users can upload photos to a blog.  The first step would be to have a ‘users’ table that stores just the basic information about each user.  It might looks something like this:

users table

Notice that there is an “id” field.  So each row, meaning each entry in this table, has a unique identifier.  Each table must have a unique identifier, known in database parlance as the primary key.

So if I were to ask a question, such as “what is the username of user #3?”, it would be possible, in code, to write a SQL query that answers this question.  The code would look for the “username” field of the row in the “users” table with id=3, and that query would return the answer.  That query might look something like this:

SELECT username FROM users WHERE id=3

In order to keep track of which images have been uploaded by which users, we might create another table called “images”.  This table would have the details of each image which has been uploaded, including which user uploaded it.  The “images” table might be set up as follows:

images table

Again, we see that there is an “id” field, which is the primary key for the “images” table, just like we had an “id” field in the “users” table.  Each table will have an “id” field that serves as the primary key for that table.

But notice that the “images” table also has a field called “user_id”, which would be used indicate the primary key of the user who uploaded the image in each row.  So when we, in our code, create a new row in the “images” table, we would tell the database what data to put in each of the fields; and in the “user_id” field, we would tell it to put in the unique identifier of the particular user who uploaded that image in that field.

Then, if we were, in code, to read the data out of the “images” table, we be able to see that user #1 uploaded photo #2, and user #3 uploaded photo #3, for example.

This “user_id” field in this case is called a foreign key.  The term, foreign key, just means that we are using the primary key of one table (in this case, the “users” table) as a piece of data in another table (in this case, the “images” table). That is how we conceptually link the data between two tables.

It’s important to note that the relationship between the “id” field of the “users” table and the “user_id” field of the “images” table is not something that is automatically managed and handled by MySQL.  Some database servers do have a special feature where the database automatically “knows” about this relationship.  But the MySQL server we are using is ignorant of this relationship, so we have to make sure our code is smart enough to know what data is related to what other data – it’s not built into the database.

Understanding how to use the $_GET, $_POST, and $_REQUEST variables is critical to creating a site in PHP.  So here is an in-depth analysis of how data is passed from one page to another using forms.

In our earlier post, we discussed the concept of persistent data.  Each web page is like a distinct application that does not share any information with any other web page.  But sometimes, you want one script to communicate with another script.  Sometimes two different pages need to share data.

In this post, we’re going to take a look at three things:

1. an XHTML form in which a user will enter some data
2. the HTTP request, which is the technical mechanism the browser uses to send the data the user entered in the form to the server
3. the way a PHP script can access the data the user entered in the form and do something with it

The example scenario

As an example, let’s say we have created a website where users can sign up to receive a free guide to Sri Lankan real estate in the mail.  So there is a form that the user has to fill out where they enter their shipping address.  When the user clicks the “submit” button on that form, that data that they entered is taken from the page on which they entered it, and is sent to another script using either a GET or POST request to the server.

These two types of HTTP requests, GET and POST, are the primary ways that a browser can make requests pages from a server.  (For an overview of all possible types of requests and responses between web clients and servers, you can look up the HTTP reference page on Wikipedia).

index.php, the XHTML file

Let’s imagine that index.php is the XHTML code for the form where the user enters his shipping information.  This form just has a bunch of text fields that a user fills in with his shipping address.  Then the user clicks “Send it now!” to submit the form:

<form action="process_signup.php" method="POST">
  <label for="full_name">Name:</label>
  <input type="text" id="full_name" name="full_name" />
  <br />
  <label for="street">Street:</label>
  <input type="text" id="street" name="street" />
  <br />
  <label for="city">City:</label>
  <input type="text" id="city" name="city" />
  <br />
  <label for="state">State:</label>
  <input type="text" id="state" name="state" />
  <br />
  <label for="zip">Zip:</label>
  <input type="text" id="zip" name="zip" />
  <br />
  <input type="submit" value="Send it now!" />
</form>

When viewed in the browser, and surrounded by the usual <html>, <head>, and <body> tags, this page, without any style sheets, will look something like this:

A simple form

The HTTP POST request

When the form is “submitted”, what this means in technical terms is that the browser actually makes an HTTP POST request to the server for the file, process_payment.php.  We can tell this because the <form> tag has two attributes: “action” and “method”, which indicate what the browser should do when the user clicks the submit button.

<form action="process_payment.php" method="POST">

The “action” attribute indicates which page the browser should request when the user clicks submit. The “method” attribute indicates which HTTP type of request the browser should make.  In this case it’s a POST request.

The POST request asks the server for the process_signup.php file, but along with that request it also sends the data the user entered in the form to the server.  That data is sent in a way that is invisible to the user, hidden in the HTTP headers that the browser uses to make the formal request the server for the process_payment.php file.

If we were to somehow intercept those invisible headers that the browser sends as its formal request to the server, they would look something like this:

POST /process_signup.php HTTP/1.1
Host: www.mysite.com
User-Agent: Mozilla/4.0
Content-Length: 27
Content-Type: application/x-www-form-urlencoded

full_name=Amos+Bloomberg&street=145+4th+Avenue&city=New+York&state=NY&zip=10003

The top part are the generic HTTP POST headers indicating what file the browser wants, and what kind of client the browser is.  The bottom line contains the urlencoded data that the user entered in the form.  This is how POST method submits data to the server.  But you’ll never see any of that since it is never shown to the user.

Notice that the data in the bottom line holds the data that the user entered as a series of key=value pairs, with a very specific syntax:

key1=value1&key2=value2&key3=value3 ... and so on...

In urlencoding, all key/value pairs are separated by ampersands, “&”. The key to each piece of data from a form is always the “name” attribute of the XHTML form field in which the user entered the data.  The value is always the text that the user entered.

So “full_name” is what we put in the “name” attribute of the first <input> tag in the form…

  <input type="text" id="full_name" name="full_name" />

..so that’s the term that is used as the key to that piece of data in the HTTP POST request that is sent to the server.

full_name=Amos+Bloomberg&street=145+4th+Avenue&city=New+York&state=NY&zip=10003

“street” is what we used as the “name” attribute of the second <input> tag in the XHTML, so that’s what is used as the key to the second piece of data in the HTTP POST request.  And so on for all the other form fields.

process_signup.php, the PHP script

Once that form has been submitted and the HTTP POST request has been made to the server, the server sees that the file that the browser requested is a PHP script, so it runs that script on the server before sending anything back to the client.

PHP, since it is built specifically for the web, is smart enough to know that the PHP code in the scripts on the server might be interested in the details of the data that was sent along with the HTTP request, meaning the data that the user entered in the form.  So PHP provides a few special variables that contain that data in an easy-to-use format.

The $_POST variable is built-in associative array that automatically is set to hold all of the data that was sent along with in any HTTP POST request to the server.  The index of each element in the associative array is the same word that was used as the key in the key=value pair that represented that data in the HTTP request.  This is also, if you recall, always the same word as the “name” attribute of the form field in which the user entered the data.

So in our example, the $_POST array will have 5 elements.  If we were to dump out the raw data stored in the $_POST array in our PHP script using the built-in print_r() function, which we always use to debug arrays, it would look something like this:

Array(
  full_name => Amos Bloomberg,
  street => 145 4th Avenue,
  city => New York,
  state => NY,
  zip => 10003
)

We can access each element in the array individually in PHP by using the $_POST array with the correct term as the index, using the syntax $_POST['<index name>'].  For example:

$fullName = $_POST['full_name'];
$street = $_POST['street'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];

The important thing is that the index that we use each time we access the $_POST array is the same word that we used in the “name” attribute of the form field <input> tag in the XHTML page where the user entered their address.

Assuming all the names we use as indexes for the $_POST array in the PHP match the “name” attributes of all the form fields the user filled in on the XHTML page, we will have successfully passed data from one page to another.  Data the user entered from the form has been passed to the PHP script.

Assuming the XHTML “names” and the PHP indexes match, we will now have the data that the user entered in the form stored in our variables, $fullName, $street, $city, $state, and $zip.  And we can do whatever we want with that information, for example storing it in a database, or sending it in an email to someone.

The HTTP GET request

As an alternative, we could have used the HTTP GET method instead of the POST method when submitting the form.  To do this, we would have changed the form tag in the XHTML to be:

<form action="process_payment.php" method="GET">

The practical difference between the two is that with GET, the data that the user enters in the form is attached to the URL of the file that the client is requesting from the server.  And users will see the data they entered in the form show up in the address bar of the browser, when it requests the file process_signup.php, so data passed along with a GET request is not invisible or hidden from the user.

Browser address bar with HTTP GET request data

If we were to intercept the headers in an HTTP GET request to the server, it would look something like this:

GET /process_signup.php?full_name=Amos+Bloomberg&street=145+4th+Avenue&city=New+York&state=NY&zip=10003
Host: www.mysite.com
User-Agent: Mozilla/4.0

Notice how the data the user entered in the form is tacked on to the end of the file name that the browser is requesting from the server.  The user will see all this data in the browser address bar as well.

Just as the data sent along with the POST request to the server in our original example was automatically stored in a $_POST variable in the PHP script on the server, process_signup.php, the data sent along with the GET request is automatically stored in a $_GET variable.  And you can access that data in almost the exact same way you did with the $_POST variable in your PHP code:

$fullName = $_GET['full_name'];
$street = $_GET['street'];
$city = $_GET['city'];
$state = $_GET['state'];
$zip = $_GET['zip'];

So, as I hope is obvious, the $_GET variable only holds data that was passed to a PHP script via the GET method.  And the $_POST variable holds only that data that was passed to the server via a POST request.  Do not try to mix and mingle the two.

The $_REQUEST variable

Whereas the $_GET variable only holds data passed to the server with an HTTP GET request, and the $_POST variable only holds data passed to the server in an HTTP POST request, the $_REQUEST variable holds any data passed to the server along with either type of request.  It also holds data passed via cookies.

The $_REQUEST variable is a sort-of catch-all for any data passed to the server, regardless of how it got there.  So regardless of whether the form used the method POST or the method GET, we could use this code in the PHP script to access the data:

$fullName = $_REQUEST['full_name'];
$street = $_REQUEST['street'];
$city = $_REQUEST['city'];
$state = $_REQUEST['state'];
$zip = $_REQUEST['zip'];

As you can see, the built-in $_REQUEST variable is an associative array with indexes that are the same words we used in the “name” attribute of each field in the XHTML form, just as $_POST and $_GET were.

Some sites allow users to upload files to the server.  Most often, this is the case for sites that allow users to upload images.  But the same technique is used regardless of the type of file.  Uploading files of any type, be they images, audio files, video files, text files, or any other type, is a singular process.

This example shows how to allow users to upload files to the server.  As with any site, it all starts with the XHTML.  Click here to see this example code in action.

An XHTML page with a form

When uploading files to the server, it is absolutely necessary to give the <form> tag an extra attribute: enctype=”multipart/form-data”.  This tells the server that it should expect to receive not only text in the form data, but also possibly a file as well.  Omitting this extra attribute is a common mistake.

Here is an example XHTML page with a file upload form on it.  We’ll call it “index.html“.  The especially relevant bits for this example are the <form> tag, and the <input type=”file”> tag:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <title>File Upload Example</title>
 <link rel="stylesheet" type="text/css" href="styles/main.css" />
 </head>
 <body>
 <div id="container">

 <h1>File Upload Example</h1>
 <p>Please upload an image:</p>

 <form action="process_file.php" method="POST" enctype="multipart/form-data">
 <input type="file" name="uploadfile">
 <br /><br />
 <input type="submit" value="Upload!">
 </form>

 </div><!-- end container -->
 </body>
</html>

When the user clicks the submit button, the data entered into the form is sent along with an HTTP POST request to the server for the script, “process_file.php”.  This we surmise from the “method” and “action” attributes of the <form> tag.

A PHP script to process the uploaded file

The following code is from “process_file.php“, which is the PHP script that receives the data passed along with the HTTP POST request, and decides what to do with it.

<?php

require_once("Uploader.class.php");

//create a new instance of the Uploader class
//the constructor function for the Uploader class takes three parameters: the "name" attribute of the <input type="file" name="..."> field, the subdirectory where you want to store the files, and the maximum file size allowed
$uploader = new Uploader("uploadfile", "files", "10M");

//try to do the upload
if ($uploader->upload()) {
 //it worked... so get the new location of the file from the $uploader->getPermanentPath() function
 $path = $uploader->getPermanentPath();
 //redirect to success page, and pass the new path along
 header("Location: success.php?path=" . urlencode($path));    
}
else {
 //it didnt work!  so get the new error message from the $uploader->getError() function
 $error = $uploader->getError();
 //redirect to failure page, and pass the error message along
 header("Location: failure.php?error=" . urlencode($error));    
}

?>

Let’s analyze this file.  The first thing we do is require the inclusion of another file, named “Uploader.class.php”.  This file is an object-oriented bit of PHP code that I have written.  This Uploader class file contains the code that does the dirty work of file uploading.

The nice thing about object oriented code is that it is built to be taken for granted (click here if you’re interested in a brief intro to object-oriented concepts.)  This is one of the core philosophies of object-oriented coding: different processes should run independently of one another, and the internal workings of each process should not matter to the functioning of the other processes.  This is known as “encapsulation”.  So in this case, I will not explain how this class works.  The code is well commented if you want to look for yourselves.  I will just describe how it should be used.

Once we have included the Uploader class file, we create an Uploader object.  To do so requires passing three pieces of data to the Uploader “constructor function” (read here for a brief intro to how to do object-oriented programming in PHP)

$uploader = new Uploader("uploadfile", "files", "10M");

• The first parameter is  the value we gave the “name” attribute of the <input type=”file” name=”…” /> tag in the XHTML form, in this case it is “uploadfile”.
• The second parameter is the subdirectory on the server where we want to store any uploaded files… you’ll see on the server that we have set up a “files” subdirectory in this project folder.  (Note: This folder must allow web browsers to write data to it.  You can use a file-transfer program such as WinSCP or Cyberduck to make sure the permissions for this folder allow “Others” to “Write” to it. Usually this can be done by right-clicking on the folder in WinSCP, or Control-clicking on the folder in CyberDuck, and selecting the “Properties” or “Info” menu in WinSCP and CyberDuck respectively)
  

  Control-click menu in Cyberduck

  

  Info menu in CyberDuck - make sure "Others" can "Write"

• The last parameter is an option maximum file size that you want to allow users to upload, in this case 10M.

Now that the object is set up, we try to do the upload, and we test to see if it worked or not.

if ($uploader->upload()) {
   //... it worked ...
}
else {
   //... it failed ...
}

If the upload was successful, we redirect the browser to a “success.php” confirmation page. We also pass along the path of the newly uploaded file on the server to the success.php script.

If the upoad failed, we redirect the browser to a “failure.php” page that explains that an error occurred, and we pass along an error message as part of the request for that failure.php script.

The success confirmation page

The success.php confirmation page simply shows a user-friendly message indicating that the upload was successful.  The PHP script on this page retrieves the data that was passed along with the HTTP GET request for the page.  This data is the path of the newly uploaded image file on the server.  The script on this page uses that path as the “src” attribute of an <img> on the page, which makes the uploaded image appear on the page.

<?php echo "<?" ?>xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <title>Success! File Uploaded</title>
 <link rel="stylesheet" type="text/css" href="styles/main.css" />
 </head>

 <body>
 <div id="container">

 <h1>Success! File Uploaded</h1>
 <p>Thanks.  Your image has been successfully uploaded to <?php echo $_REQUEST['path'] ?></p>

 <a href="<?php echo $_REQUEST['path']; ?>">
   <img src="<?php echo $_REQUEST['path'] ?>" />
 </a>

 </div><!-- end container -->
 </body>
</html>

The failure error page

The failure.php error page simply shows a user-friendly error message indicating that the upload was unsuccessful.  The PHP script on this page retrieves the data that was passed along with the HTTP GET request for the page.  This data is the error message that was output by the Uploader object.

<?php echo "<?" ?>xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <title>Failure! File Not Uploaded</title>
 <link rel="stylesheet" type="text/css" href="styles/main.css" />
 </head>

 <body>
 <div id="container">

 <h1>Failure! File Not Uploaded</h1>
 <p>Your image failed to upload</p>
 <p><?php echo $_REQUEST['error'] ?></p>

 <p>
   <a href="index.html">try again</a>
 </p>

 </div><!-- end container -->
 </body>
</html>

PHP’s built-in mail() function allows you to send emails from PHP code.  This can be useful when, for example, you want to include a “Contact Me” form on your website, and you want an email to be sent to you whenever someone fills out that form.

The mail() function has three required parameters: the “To” email address, the subject of the message, and the content of the message.  There is also a fourth additional header that can contain one or more additional information, such as who the message is “From”, any “CC” or “Bcc” email address, and some other obscure things.

Click here to view this example code in action.

XHTML page with form

In this example, we’ll look at the most common scenario: how to use an XHTML form that a user can fill in to send a message with all four parameters, “To”, “From”, subject, and message content.

The first thing to do would be set up an XHTML document called “index.html” with a form that users can fill in to send the email.  A typical “Contact us” XHTML document might look like this:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <title>Contact us</title>
 <link rel="stylesheet" type="text/css" href="styles/main.css" />
 </head>
 <body>
 <div id="container">

 <h1>Contact us</h1>
 <p>Fill out this form, and click submit, and we'll receive an email!</p>

 <form action="process_email.php" method="POST">
 <label for="name">Your Name:</label>
 <input type="text" id="name" name="name"/>
 <br />
 <label for="email">Your Email:</label>
 <input type="text" id="email" name="email"/>
 <br />
 <label for="subject">Subject:</label>
 <input type="text" id="subject" name="subject"/>
 <br />
 <label for="message">Message:</label>
 <textarea id="message" name="message"></textarea>
 <br />
 <input type="submit" value="Send email!"/>
 <br />
 </form>

 </div><!-- end container -->
 </body>
</html>

As you can probably notice from the attributes of the <form> tag, this form will be using the HTTP POST method of passing data to the server.  And when a user clicks submit, the data the user entered gets sent, along with the POST request, to the file called “process_email.php”.

PHP script to process the data entered in the XHTML form

The PHP script that receives this data, called “process_email.php“, looks like the following:

<?php

$to = "yourname@yourdomain.com"; //the email address where you want to receive these emails//get the data the user entered in the form

$name = $_REQUEST['name']; //get the data the user entered in the name field and store it in a variable
$email = $_REQUEST['email']; //get the data the user entered in the email field and store it in a variable
$subject = $_REQUEST['subject']; //get the data the user entered in the subject field and store it in a variable
$message = $_REQUEST['message']; //get the data the user entered in the message field and store it in a variable

//put together the email headers, which contain the name, email, and other info about the mail program we're using
$phpVersion = phpversion(); //this calls a built-in PHP function that returns the current version of PHP installed

$headers = <<<END
From: {$name} <{$email}>
Reply-To: {$email}
X-Mailer: PHP/{$phpVersion}
END;

//use php's built-in mail() function to send this e-mail
mail($to, $subject, $message, $headers);

//redirect to success page
header("Location: success.php?to={$to}");

?>

Let’s analyze this file. You can see that the first thing this code does is hard-code the “To” address.  Since these emails are supposed to all be going to us, we don’t want to ask the users of our site to enter in the email address to which they want to send these emails.  We don’t want to give them the ability to send emails to anyone but us!  So we hard-code that address.

The other variables we set up are all just storing the data the user entered on the XHTML page in the form.  We access that data by reading it from PHP’s built-in $_REQUEST array, which has all the information on all data that was submitted along with any type of HTTP request.

Then we assemble a set of extra headers that indicate who the email is “From”, what address the receiving email program should use as the “Reply-to” address, and the name of the program that was used to send this email (in this case, we’re simply substituting the version of PHP).

Finally, we call the mail() function of PHP, along with the parameters necessary to actually send the email.  Once that is finished, we redirect the user’s browser to the page, success.php, which has a user-friendly confirmation message.  We redirect the browser by sending a “Location” HTTP header to the browser in response to the browser’s request for this page.  This tells the browser that it should redirect to success.php, rather than stay on the current page.

The header we send to the browser in response to its request for this page looks like this:

header("Location: success.php?to={$to}");

You may recognize that we are attaching some data onto the query string of the file that we are instructing the browser to request instead of the current page.  So when the browser requests that other file, it will pass some data back to that other server script along with the new request.  To be exact, the browser will request the following file from the server with the following query string:

sucess.php?to=yourname@yourdomain.com

In this case, the data being passed is the “To” address indicating to where the email was sent.

Confirmation screen

The confirmation page, called “success.php“, receives that data in the $_REQUEST array, and outputs a friendly message that acknowledges the successful sending of the email to that address that was passed along with the request.

<?php echo "<?" ?>xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <title>Success! Email Sent</title>
 <link rel="stylesheet" type="text/css" href="styles/main.css" />
 </head>

 <body>
 <div id="container">

 <h1>Success! Email Sent</h1>
 <p>Your email has been successfully sent to <?php echo $_REQUEST['to'] ?></p>
 <p><a href="index.html">Click here to send another</a></p>

 </div><!-- end container -->
 </body>
</html>

Here are some posts that attempt to elucidate the concepts behind working with databases.

Intro to Relational Databases – the concepts behind databases, tables, rows, and fields

Administering MySQL using phpMyAdmin – another post explaining how to use phpMyAdmin to manage your tables

Select Statements in MySQL – reading data from databases can be more sophisticated than simply pulling all the data from a specific table.  This post explains some of the finer techniques of reading from a table.

Debugging PHP and MySQL - some tips on how to figure out where any errors are coming from

Making Timestamps User-Friendly – the “created” field in all of our tables in the database stores the date a row was entered into the table.  This post explains how to output those dates in a user-friendly way.

Pagination in PHP – when you pull lots of data from the database, you often will not want to display it all on one page.  This post explains the concepts behind breaking up the set of results onto multiple pages.   It’s a little advanced at this point, but if you’re brave…

For our assignment today, we will need to set up a table in the database where the information about all the products in our e-commerce site will be stored.  This post outlines the steps necessary to create a table in the database.

All administration of databases for our class will be handled through phpMyAdmin, a web-based database administrative tool written in PHP and published for free by an altruistic development team.  phpMyAdmin allows us to easily do common database tasks using a (relatively) intuitive graphical interface.

The first thing we will be doing is setting up a table in the database.  Database servers can hold more than one database.  To create a table in the database, we need to first select which database we want to use.  On the left side of phpMyAdmin, select “classdb”: this is the database for this class.

Select database

Selecting the database will bring up a list of all the tables that already exist in that database.  At the bottom of this list, you will see a space in which we can create a new table on database “classdb”.  Enter in the name of your table, and the number of fields it will hold.  The name should be your first initial followed by your last name, followed by an underscore, followed by the name you want to give the table.  So, for example, my table would be “abloomberg_products”.  The number of fields in this example is 5.

Create new table

Once we click “Go”, we are brought to a page that asks us to indicate what fields we would like the table to have, and what type of data will be stored in these fields.

• For any table we ever make, the first field will always be called “id”, and will be of type “Integer”, and have an extra property called “auto_increment”.  It will also be selected as being the “Primary Key”.
• Likewise, all tables we make will have the last field named “created”, which will be of type “Timestamp”, and will have the CURRENT_TIMESTAMP checkbox selected.

The other fields that go between “id” and “created” will depend upon the table.  In this case, they are:

• “title” of type “Varchar” – this will hold the title of any products in our table
• “description” of type “Text”, and length 255 – this will hold the description of any products in our table
• “price” of type “Float” – this will hold the price of any products in our table

Make sure your table has the same settings as the following:

Set fields for new table

Once you click “Save”, you should see your table name show up in the left navigation area in phpMyAdmin:

Verify new table created

If you click the tab named “Structure”, you will see the structure of the table you just created.  Yours should look like this:

Table structure

If you click the tab named “Insert”, you will be able to insert a few rows into this table.  Type the following two rows of data, and click the “Go” button.  Notice that we are leaving the “id” field blank – the id for any row will be automatically created because we selected the “auto_increment” extra when we created the table.

Insert new rows into table

At any time, you can browse the rows you have stored in your table by clicking the “Browse” tab.  This will show you a paginated list of data that is stored in the table.

Browse existing rows in table

The “SQL” tab allows you to run arbitrary SQL commands on the table.  This is an example of asking for the database to show you a list of all the data in the table:

SELECT * FROM abloomberg_products WHERE 1

Run SELECT query on table

And this is an example of inserting a new row into the table:

INSERT INTO abloomberg_products (title, description, price) VALUES ('Another great cookie', 'This one has ginger schnapps in the mix', 7.49)

Run INSERT query on table

The following is how you would update an existing row in the table:

UPDATE abloomberg_products SET title='Dark Chocolate Cake', description='Only the finest 85% cocoa goes into each hand-made cake.', price=19.99 WHERE id=1

Run UPDATE query on table

And this shows how you would delete a row from the table, depending on the value stored in its id field:

DELETE FROM abloomberg_products WHERE id=1

Run DELETE query on table