CS 120, Fall 2011, Lab 13:
MySQL and PHP

We are in the middle of a brief look at server-side programming with PHP and MySQL. In this lab, you will do a bit of work with these two server-side technologies. In the first part of the lab, you will perform some operations on MySQL databases by entering SQL commands directly. In the second, you will use PHP to access a MySQL database.

This lab is due by class time on the last day of classes, Friday, December 9.

About SQL Commands

The exercises for the lab start later on this page. This section contains a review of the basic SQL commands that you will need: INSERT, DELETE, UPDATE, and SELECT. Recall that these commands operate on the tables in a relational database. Each table has a name, and each column in the table has a name; these names are used in the commands. Each column also has a particular type of data that it can hold.

DELETE FROM table_name WHERE condition

Delete any rows from the table that match the condition. This might be one row, many rows, or even zero rows, if no rows match the condition. A condition here could be something like name = "fred" or id >= 27 where name and id are names of columns in the table. More complex conditions using AND and OR are also possible.

UPDATE table_name SET column = expression WHERE condition

Modifies all rows in the table that match the condition by setting the value stored in the specified column in each row. The value that is stored in the column is given by the value of the expression. Column names can be used as variables in the expression.

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)

Adds one row to the table. The first set of parentheses contains a list of the columns whose values are to be set; columns that are not named here will get a default value. The second set of parentheses contains a list of values that are assigned to the columns. The number of values must equal the number of columns in the first set of parentheses.

SELECT * FROM table_name

A SELECT command can be very complicated. This is the most basic form. It essentially returns a copy of the table. A SELECT always returns a set of rows; in this case, every row from the table is returned. More examples of SELECT statements follow.

SELECT * FROM table_name WHERE condition

Returns the set of rows from the table that match the condition. In the condition, column names can be used like variables. Example: SELECT * FROM message WHERE username="eck"

SELECT (column1, column2, ...) FROM table_name WHERE condition

Select the set of rows in the table that match the condition, but only the values in the specified columns are returned.

SELECT (column1, column2, ...) FROM table_name WHERE condition ORDER BY column

Returns the same set of data as the previous example, but the rows are sorted so that the values in the column specified by "ORDER BY" are in increasing oder. ORDER BY is one of a variety of modifiers that can be added to SELECT commands

SELECT count(*) FROM table_name WHERE condition

This special form of the SELECT command returns a single row that contains a single number. The number is the number of rows that are selected. In this case, you get the number of rows in the table that match the condition; if you leave out the condition, you'll get the number of rows in the entire table.

(In addition to DELETE, INSERT, UPDATE, and SELECT, MySQL defines many other commands, including commands for creating databases and tables, backing up the data, creating database users, and saying what a given user is allowed to do. However, we will not cover any of these commands.)

The cs120_f11 Database

I have created a small database for use in this class. You have already used the database, indirectly, in Lab 9 and Lab 11. In those labs, you wrote client-side forms and AJAX calls that contacted programs on the server. Those server-side programs made use of the database. The database has four tables: user, message, poll, and poll_answers. (The programs for Labs 9 and 11 only used user and message.) For today's lab, I have created a database username and password that you will use to access the database. I'll tell you the username and password in class. The username only gives you access to certain operations on each table. For example, for the user database, you can only use the SELECT command. Here is a description of the tables:

The user table (users of the message board):

Commands that you can use on this table: SELECT

Columns in this table:

The message table (messages posted to the message board):

Commands that you can use on this table: SELECT, INSERT, DELETE, UPDATE

Columns in this table:

The poll table (questions for single-question polls):

Commands that you can use on this table: SELECT, INSERT

Columns in this table:

The poll_answers table (answers for all the polls in the poll table):

Commands that you can use on this table: SELECT, INSERT, UPDATE

Columns in this table:

Part 1: MySQL Exercises

The first part of the lab asks you to apply some SQL commands to the tables in the cs120_f11 database. To do this, you should go to the following page in a web browser:

http://math.hws.edu/local/mysql.php

(This links only works when you are on the HWS network.) This is the very simply mysql web interface that was demonstrated in class on Wednesday. You need to fill in the Username, Password, and Database name (and leave the Host name set to localhost). The Database name is cs120_f11. The username and password will be announced at the lab.

To use this interface, you just have to type an SQL command in the large box, labeled "Your SQL query". Then, click "Submit Query". The output from your command will appear at the bottom of the page. If there is an error in your command, an error message will be shown instead.

The exercises for this part of the lab are on a separate page. Hard copies of that page will be handed out at the lab. You will be asked to write down the command that you use to complete each exercise. If you finish this part of the lab during the lab period on Friday, you can turn it in at the end of the period. Otherwise, you should turn it in next Friday, when the second part of the lab is due.

Part 2: A PHP Program

In Part 1 of this lab, you added data for a poll to the poll table of the cs120_f11 database. For the second part of the lab, you will implement the actual poll using an HTML form and a php file to process the data from that form.

You can do this part of the exercise in Aptana Studio. However, do not create a new project. For PHP to work, it has to be accessed through an actual web server. So, you should work in your Web Portfolio project. Create a new folder in your Web Portfolio named lab13. (Remember that the Web Portfolio is actually the www folder in your home directory, and that that directory is accessible on the web server that is running on math.hws.edu.)

In your lab13 folder, you should first create a plain HTML file with a form that can be used to respond to your poll. The form should display the question for your poll, and it should have a set of radio buttons to represent the possible answers. (You do not have to pull this data out of the database; you can just code it directly on the page.) We looked at a sample poll in class. That poll is at

http://math.hws.edu/eck/cs120/f11/demo/colorpoll.html

(Hopefully, though, your poll will look nicer than this one.) You have used forms and radio buttons before, in Lab 9.

The action in your form should be a PHP file. You should create the PHP file in the same directory as the HTML file that uses it. The name of the PHP file must end with .php; otherwise, the web server won't recognize the file as a PHP file.

We looked at a sample PHP file in class, process_addition.php, and you will find that example helpful. In particular, this example shows how to use PHP to access data from a form, using $_POST. It also shows how to test whether the file actually received data from a form. Here is the source code for that example:

<html>
<head>
<title>Addition Quiz Results</title>
</head>
<body>

<h2>Addition Problem Results</h2>

<?php
 
   if ( ! $_POST["answer"] ) {
       print("<p>This page should not be used directly. It's for ");
       print("use with <a href='addition.php'>addition.php</a>.\n");
   }
   else {
      $n1 = $_POST["n1"];
      $n2 = $_POST["n2"];
      $user_answer = $_POST["answer"];
      $correct_answer = $n1 + $n2;
      print("<p>The question was, <b>Compute $n1 + $n2</b>.</p>\n");
      print("<p>Your answer was: <b>$user_answer</b>\n");
      if ( $user_answer == $correct_answer ) {
          print("<p><i>Congratulations!</i> You got it right!</p>\n");
      }
      else {
          print("<p>Sorry, the correct answer is <b>$correct_answer</b>.</p>\n");
      }
      print("<p><a href='addition.php'>Click here to try another problem.</a></p>\n");
   }

?>

</body>
</html>

However, this example does not show how to use PHP to access a database. We will discuss this further in class on Monday, but here are some of the basics that you need to know for this lab:

The MySQL database server is a separate program from the web server that is running the PHP code. To access a database, the PHP program must first open a "connection" to the database server, and it must specify which database it wants to use. The PHP statements for doing this have the form:

mysql_connect( "localhost", databaseUser, databasePassword );
mysql_select_db( databaseName );

Here, "localhost" means that the database server is on the same computer (or "host") as the web server. The databaseUser and databasePassword are the username and password that give access to the database; they should be the same as the username and password that you used with mysql.php in Part 1 of the lab (enclosed in quotes). The databaseName is the name of the database that you want to use; in this case, it should be "cs120_f11". Both of these commands can cause errors, for example if the password is incorrect or if the named database doesn't exist. If an error occurs, you will see the error on the web page -- and you should probably try giving incorrect information to see what the error looks like. However, if you have all the parameters correct, you shouldn't get an error.

Once the connection to the database is set up, you want to send commands to the database and get back the output of the commands. For that, you can use a PHP statement of the form

$result = mysql_query( query );

where query is a string containing the SQL command that you want to perform. $result is a variable that will contain the output of the command. A simple example would be 

$result = mysql_query( "SELECT * FROM user" );

In this case, $result would essentially be the entire user table.

Your PHP file is meant to receive an answer to your poll. You need to record that answer by modifying the appropriate count in the poll_answers database. You can do that with mysql_query. You will also want to display the current statistics for your poll. That is, you want to make a table to show each answer and the number of times that answer has been chosen. You can get the information you need with another mysql_query. To make it easy to create the table, you can copy-and-paste the following PHP function definition into your PHP code:

function make_table($result) { 
  print("<table cellpadding=5 border=2>\n");
  $fields = mysql_num_fields($result);
  print("<tr bgcolor='#DDDDDD'>\n");
  for ($i = 0; $i < $fields; $i++) {
      // Write the column names on the first row of the table.
    $fieldname = mysql_field_name($result,$i);
    print("  <td><b>$fieldname</b></td>\n");
  }
  print("</tr>\n");
  while ($row = mysql_fetch_row($result)) {
      // Write out one row of the output from the command.
    print("<tr>\n");
    for ($i = 0; $i < $fields; $i++)
      print("  <td> $row[$i]</td>\n");
    print("</tr>\n");
  }
  print("</table>\n");
}

Then just call this function, with a statement of the form:  make_table($result);

As you work on your PHP file, you are likely to make some syntax errors. Syntax errors will be reported in the web browser when you try to access the PHP file. Remember that you have to do this through the web server on math.hws.edu, using a URL that will look something like

http://math.hws.edu/~zz9999/lab13/process_poll.php

When the syntax errors have been eliminated, you can try it with the HTML form. Again, you have to access the HTML page through math.hws.edu using a URL that will look something like

http://math.hws.edu/~zz9999/lab13/poll.html