This is Part 1 of a lab on using MySQL in PHP. We will continue the lab with Part 2 next week. The Exercises from this lab are not due next week. You will turn in this week's Exercises along with additional exercises from Part 2 during the week of October 1.
You can use your survey form from previous labs as a starting point for this lab. If you prefer, you can use a sample form that I have created instead. To try this form, click here. The source for this form is in the file /home/cs371/signup.php. Copy this file into your public_html directory if you want to use it for this lab.
You will be using a database that I have set up for you on the computer named cslab9. You can access this database using your regular user name and the password that I emailed to you. The name of the database is the same as your user name. (This is a pretty silly way to name a database, but I couldn't think of a better way to choose a different name for each person's database.) The database can be accessed from any of the cslab computers. This means it can be accessed from PHP Web pages that are loaded from those computers, even if the Web browser is not running on one of the cslab machines. (It's the Web server that actually accesses the database, and as long as the server is on one of the cslab machines, access is allowed.)
I have written a simple Web interface to MySQL, which will allow you to type in SQL statements directly and see the result. You can find this interface at http://cslab0.hws.edu/~eck/mysql.php. (This is available only from computers on the HWS network.) If you want to check out the source code for this page, click here.
Once you get to the page, fill in the fields on the right: The Host Name is cslab9. Use your regular user name and the password that I emailed to you. The database name is the same as your user name.
If you type an SQL statement in the Textarea on the left and hit the Submit button, the result of the query will be printed on the bottom of the page. Depending on the query, this might be an error message, a note that the command has succeeded, or a table of results from the query.
Here are some possible simple queries:
You will need a table that can hold data from your survey form (or from mine, if you choose to use that instead). You will need one column for each input item on the form. (If you have too many items on your form, you might want to ignore some of them or use my form instead.) Create your table by entering a CREATE TABLE command in the MySQL interface. We covered the syntax of CREATE TABLE statements in class.
Data from a text input should ordinarily be stored in a column with a type such as VARCHAR(60). Data from a select menu or from a group of radio buttons could also be stored in a VARCHAR column, or you could use an INT to specify which item was selected. For a checkbox, you could use an INT value of 1 or 0 to specify whether the box is checked or not. Alternatively, you could use a VARCHAR column that holds a blank string if the box is unchecked and a non-blank string if it is checked. Data from a Textarea should probably go into a column of type TEXT.
You should think about how you might want to access the data that you store in the table, and you should create one or more indices for the table accordingly.
After creating your table, you can use the DESCRIBE TABLE <table_name> command in the query box to see that it was created properly.
You could insert data into your table directly, using the mysql interface. Just type the appropriate INSERT statement into the query box. You might want to try this. However, what you really want to do is insert data from your form using PHP. To use MySQL in PHP, you must first open a connection to the database server and select a database. You should only do this once. The connection will remain open until the page ends, or until you explicitely close it. Once the connection is open, you can send multiple queries to it. The typical code for opening a connection, as given in class, is:
if ( ! (mysql_connect(<host>,<user>,<password>) && mysql_select_db(<database>)) ) {
// Can't open a connection! Report the error and
die();
}
If a connection is open, you can send a query to the database with the mysql_query() function. For an INSERT query, this looks something like:
$query = "INSERT into <table_name> ( <column_names> ) VALUES ( <values> )";
if ( mysql_query($query) ) {
// INSERT operating succeeded
}
else {
// there was an error
}
You can leave out the (<column_names>) in the query if you are providing a value for every column in the table, in the correct order.
In order to insert data from your survey form into your table, you should add code similar to the above to your PHP page, at the point where you have verified that the data that the user submitted is OK. When constructing your query, you should use the variables that represent data items from the form, such as $name and $email. Don't forget that String values in the query must be quoted. Quotes around numeric values are optional.
In some cases, depending on how you have set up your table, you might have to "massage" the variables a bit. For example, if the variable $sports represents the data from a checkbox, and if you are using a column type of INT in the table, you will want to make sure that $sports holds an INT before you use it in the query:
if (isSet($sports))
$sports = 0; // user didn't check the box, so use a value of 0
else
$sports = 1; // user checked the box, so use a value of 1
If you have trouble getting your query to work, try printing out the query itself on the web page, to make sure that it's what you think it is. You can get the text of the mysql error message by calling the PHP function mysql_error(). You could also try typing the query directly into the MySQL interface web page to see if it works there.
A SELECT statement is used to retrieve data from a database. You can retrieve all the data from all columns in a database by saying
SELECT * from <table_name>
The "*" is an abbreviation for "all columns". Of course, you could also list the names of columns that you want, separated by commas. If you want the rows to be sorted so that the entries in a given column are in ascending order, you can add an "ORDER BY" clause to the statement:
SELECT * from <table_name> ORDER BY <column_name>
You can even get descending order instead of ascending by adding DESC at the end.
Assuming that you have inserted a few rows into your table, you can try out some commands like these in the MySQL interface Web page. The table retrieved by the command will be printed at the bottom of the page.
Your second assignment for this lab is to write a Web page that displays the data in your table by using a MySQL SELECT query. You should display all the columns except possibly for TextArea columns, since they might display too much data to display neatly. This query will not depend on any input from the user; there is no HTML form involved. The Web page should simply display a table of data as soon as a user visits the page. The point of the exercise is to get the data out of the MySQL table and into an HTML table!
The PHP page that you write will open a connection to the database and then do a typical SELECT query, of the form:
$query = "SELECT .....";
if ($result = mysql_query($query) ) {
// Query succeeded and the data can be accessed through the $result.
if (mysql_num_rows($result)) {
// There are no rows in the result; table was empty.
}
else {
// Process the rows of the result; just show them in a table.
// Start the table with <table... >
// Output headers for the columns of the table ?
while ($row = mysql_fetch_array($result)) {
// Make one row of the table to display the items in the array $row.
}
// End the table with </table>.
}
}
else {
// An error occurred while trying to process the query.
}
The function mysql_fetch_array($result) returns an array that contains one row of the result from the query. If you use "SELECT *", it will contain one item for each column in the table. You can access the items in two ways: Using numerical indices ($row[0],$row[1],$row[2],...) or using column names as indices ($row['name'],$row['email'],$row['sports'],...). If you are using a for loop to process the row, then obviously the numerical indices will be more convenient. If you want to pull individual items out of the row, then the column-name indices are probably easier. As an alternative to mysql_fetch_array($result), you can call mysql_fetch_row($result). The array that is returned by mysql_fetch_row() uses numerical indices only, and no column-name indices.
These assignments should be turned in along with additional exercises from Part 2 of the lab, which we will do next week.
Exercise 1: The first exercise comes from "Step 3" above: You should have a PHP page that inserts data from a form into your database. Turn in a printout of the PHP code, and put a link to this page on your main CS371 Web page.
Exercise 2: The first exercise comes from "Step 4" above: You should have a PHP page that displays all the data from your database. Turn in a printout of the PHP code, and put a link to this page on your main CS371 Web page.