The lab this week continues the study of MySQL and PHP. The basic techniques that you will need for the lab are the same as those that were covered in Part 1 of the lab. However, as mentioned in class, we will apply them to a new project: providing a basic "web counter service." We will continue to use this example next week, when we will cover the idea of sessions in PHP.
The directory /home/cs371/webcount contains some files that you will need for this lab. You should copy this directory into your public_html directory with a command such as
cp -r /home/cs371/webcount ~/public_html
I encourage you to work on this week's lab in groups of two, three, four, or five people. Larger groups will have to do proportionately more work. There is more information on this later in the lab.
For this project, "Web counter" means a number that appears on a Web page. Every time the page is loaded, the number is supposed to go up by one. The number is actually an image, which is generated by the PHP script webcount.php. (For the source code of this script, click here.) In fact, the counter really counts hits on the image rather than on the Web page. For example, if a user visits the page using a text-only Web browser such as Lynx or w3m, the image won't be loaded and the hit won't be counted. Here is an example of a Web counter:
For the Web counter project, you will have to keep track of "users" who have usernames (which are assumed to be email addresses) and passwords. Users are identified internally in the database by user ID numbers. Data about users is stored in a table named "users" that has columns for the user ID, email address, and password. A user can own can own one or more web counters. A Web counter can be used to keep track of the number of visits to a Web page. Each Web counter will be identified by a unique ID number in your database. Associated with that ID are: the user who owns the counter, the current value of the counter (which goes up by one each time the page is visited), and a timestamp that records the most recent time the page was visited. There is also a short info string that the user can set to any value (with the suggestion that it be set to the URL of the page on which the counter is being used). The data for the counters is stored in a database table named "counters".
I have already created the "users" and "counters" tables in your database on cslab9, using the following CREATE TABLE statements:
CREATE TABLE users (
userid INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(75) NOT NULL,
password VARCHAR(16) NOT NULL,
preferences VARCHAR(20),
UNIQUE (email)
)
CREATE TABLE counters (
id INT PRIMARY KEY AUTO_INCREMENT,
userid INT NOT NULL,
counter INT NOT NULL,
accessed TIMESTAMP,
info VARCHAR(100),
preferences VARCHAR(20),
INDEX (userid)
)
In addition to the fields mentioned above, I've added a "preferences" field to each table, with the idea that this field could be used to store coded user preferences such as a color scheme for a web counter. There is one new field in the users table: "UNIQUE (email)" is similar to "INDEX (email)" but it also ensures that no two rows in the table can have the same value for email. If you try to do an INSERT operation with an email address that duplicates one that is already in the table, a MySQL error will occur and the INSERT operation will fail.
To implement the web counter service, you need at least two capabilities: It must be possible for a new user to get an account, and it must be possible for a user who already has an account to request a Web counter. The basic assignment for this lab is to write two PHP scripts to provide these capabilities. If you are working in a group, the group will have to provide other capabilities such as making it possible for the user: to view all his Web counters; to change his password; if he forgets his password, to have it emailed to him; to delete a counter from the database; to reset a counter to zero.
I have already written a Web page that you can use for your project. It is in the webcount directory with the name index.html. (Click here to see it.) The page contains two HTML forms to invoke the two basic scripts. You can use this page as is or modify it if you like. If you are working in a group, you can add additional forms to this page or you can provide multiple pages.
You should have a copy of the webcount directory in your public_html directory. (See the second paragraph of this worksheet.) Begin by editing the webcount.php script so that it uses your username, password, and database instead of mine. Then you can start work on your own scripts. Note that all scripts that you write should be reasonably robust. They should detect errors and report them to the user. Here is a list of the things that you can do. Everyone should do items 1 and 2. A group of N people working together should do at least N+1 items from this list, including items 1 and 2. (If you have better ideas for features that you want to add instead of these, discuss them with me.)
Item 1) Write a script named getaccount.php that process a request for a new account. There is already a form that calls this script in the index.html file in the webcount directory, so you don't have to write one. The script gets an email address and password for the new account in the variables $email and $password. This information must be inserted into the "users" table. Note that an error can occur during the process of the SQL INSERT command if a user with the same email address already exists in the table. You won't be inserting a value into the userid column of the table. This value will be set automatically to be a unique ID number for the user.
Item 2) Write a script named getcounter.php that creates a new web counter for an already registered user. Again, the HTML form for this part is already in the index.html file. The data from the form is the user's email address and password in the variables $email and $password, plus an information string in the variable $info. This item is quite a bit more complicated than item 1. You have to check that the email address is in fact in the user table, using a SELECT statement. This also gives you the user's userid and the user's correct password. If the user exists and if the password that she provided is the same as the correct password, then you should go ahead and make a web counter for the user. Insert the userid and info string into the "counters" table. The id column and the timestamp will be filled in automatically. The user will need the id in order to use the web counter. You can find out the id by calling the function mysql_insert_id(). This function returns an integer which is the AUTO_INCREMENTed INT from the most recent INSERT operation. You should report this id back to the user, preferably with instructions on how to use it. You could say something sort-of like this:
To use your new counter on your page,
just copy the following image tag into your HTML file:
<src="http://cslab0.hws.edu/~jsmith/webcount/webcount.php?id=467" width=90 height=26>.
Note that the webcount.php script is already set up to work with image tags of this form, so you don't have to worry about implementing the actual web counter. If you are working in a group, I strongly suggest that someone write a function called validateUser($email,$password) that validates a user by checking whether the given $email exists in the "users" table with the given $password. It will be useful for this function to return the userid of the user, if the user is valid, and to return 0 if the user is not valid. You can use this function in all the remaining items.)
Item 3) Write a script that will display the webcounter data for a given user. That is, it should show a table with the id, info string, and number of hits for each counter in the "counters" table that belongs to that user. The data from the script should come from a form that gets the user's email and password. You can add this form to the existing index.html file if you want. You have to validate the user in the same way as in Item 2.
Item 4) Write a script that allows a user to change his password. You will need another HTML form in which the user specifies his email, old password, and new password. After validating the user, you have to change the password in the "users" table for that user. For example, to change the password of the user with userid 17 to fred, you would use an SQL UPDATE statement of the form: UPDATE users SET password='fred' WHERE userid='17'
Item 5) Write a script that allows a user to reset one of her webcounters to zero, and make an HTML form that calls the script. The form should include fields for the user's email and password and for the id of the web counter that is to be set to zero. Don't forget to validate the user and check that the user does actually own the counter with the specified id. You will need an SQL UPDATE statement to change the value in the counter column of the "counters" table.
Item 6) Write a script that allows a user to delete one of her webcounters, and make an HTML form that calls the script. The form should include fields for the user's email and password and for the id of the web counter that is to be set to be deleted. Don't forget to validate the user and check that the user does actually own the counter with the specified id. You will need an SQL DELETE statement to delete the counter. To delete the counter with id 3788, for example, you would use: DELETE FROM counters WHERE id='3788'
Item 7) Write a script that will mail a forgotten password to a user's email address, and make an HTML form to call the script. The only data needed by the form is the user's email address. You will have to look up the PHP mail() function in the PHP manual. See http://math/eck/cs371/php_manual/function.mail.html.
Exercise: Turn in printouts of all the scripts and HTML pages that are written by you or your group. For individuals, this will presumably be just the two scripts getaccount.php and getcounter.php. Your Web Counter Service site should be accessible at a URL of the form http://cslab9/~username/webcount/. If you are working in a group, please tell me whose account I should look in to find the main page for your site. This assignment should be turned in along with additional exercises from Part 1 of Lab 4.
The assignment is due on or before Friday, October 5.