This lab explores the interface between PHP and SQL. You will begin by creating pages that embody 'fixed' queries -- they will pull information out of the database using a predetermined query, and display the results in a table or other appropriate format. This is a first step towards pages that display the results of a 'flexible' query, where part or all of the query parameters are supplied by the user. Eventually, we will develop pages that actually modify the database in some way, for example by inserting a new element.
This course does not aim to teach web page development. However, a little bit of knowledge about HTML and how web pages work will be useful. For the most part we will work from prebuilt example pages, and make specific changes to them that focus on the database aspects. Therefore we will be teaching only the minimal amount of HTML and PHP necessary to accomplish our immediate goals. If you wish to explore these subjects further on your own, there are many resources available on the web, and books in the library.
A Sample Database
For these exercises, you will use a set of tables provided with the lab. Later on in homework assignments, you will design and build tables of your own. To install the data we will need, you should first connect to the MySQL command line for your course account and then paste in the text dump of the database entries.
The database for this lab is designed for a small business that runs several martial arts studios. It contains four relations, whose schemata are shown below.
- People (name TEXT, belt TEXT, id INTEGER PRIMARY KEY)
- This holds information about all the martial artists known to the database
- Dojos (name TEXT, address TEXT, sensei INTEGER, id INTEGER PRIMARY KEY)
- This holds information about all the martial studios known to the database
- StudentAt (student INTEGER, dojo INTEGER, PRIMARY KEY (student,dojo))
- This shows which students attend each dojo.
- TeachesAt (sensei INTEGER, dojo INTEGER, schedule TEXT, PRIMARY KEY (sensei,dojo))
- This holds information on the teachers at each dojo.
(A note on vocabulary: in this usage, a dojo refers to a martial arts training place, and a sensei refers a martial arts teacher.)
Once you have entered the data, you may wish to experiment with some simple queries on this database using the native MySQL interface. Note that there are many interesting questions you can ask via this interface that would probably not be feasible to put into a web page. Which sensei no longer teaches at her dojo? How many students are members at more than one dojo? Which dojos are on the same street? For the lab, we will stick to simpler queries than these.
Displaying Query Results
For the first example, we will create a simple query and display the results in a table. Below is the full text of a web page that will display a table of all the people in the database, along with their belt level. Create a file called belts.php and paste this into it, changing the yellow text to match your account. You will also need to have created the mysql_config.php file in your home directory, as described in the account configuration page. Next make sure that the file is saved in the public_html folder of your course account, and try to view it in a web browserat the URL specified in the aforementioned page.
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>
<html>
<head>
<title>Dojo Demonstrator</title>
</head>
<body>
<?php
try {
echo "Beginning PHP database test...<hr>";
// grab connection details
require("/Users/classes/cs230a/students/cs230a-xx/mysql_config.php");
// connect to MySQL database and set some configurations
$dbh = new PDO("mysql:host=localhost",$mysql_user,$mysql_pw);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_PERSISTENT, false);
$dbh->query("USE ".$mysql_db); // selects the database to use
// now try a query
$results = $dbh->query("SELECT * FROM People;");
if (!$results) {
// if there is something wrong with the query $results will be false
echo "Query error!";
} else {
// otherwise we can proceed to process the results
// begin with some HTML to set up a table...
echo "<table border=\"1\" cellpadding=\"1\">\n";
echo "<tr><th>Name</th><th>Belt</th></tr>\n";
// now loop through the results line by line
foreach($results as $row) {
$name = $row['name'];
$belt = $row['belt'];
printf("<tr><td>%s</td><td>%s</td></th>\n",$name,$belt);
}
// finish the table
echo "</table>";
}
} catch(PDOException $e) {
// this code will run in case of a connection glitch
echo "Database access problem:<br />";
echo $e->getMessage();
}
echo "<hr>End of PHP database test...";
?>
</body>
</html>
Please note a few points about the code above. It consists of PHP code wrapped inside an HTML shell. The PHP program queries the database for information and then uses that information to produce an HTML table that contains a live view of the current database contents. (You can test this by making a change to the database via the MySQL command line and then refreshing the page -- that change should be instantly visible.) Line 21 performs the query, given as a string argument to the $dbh->query method. Line 36 produces the HTML content that ultimately appears in the table. Each time through the loop, it emits one row of the table, corresponding to one tuple of database results.
To Do
- Experiment with changes to the code above that produce different errors. What happens if you use the wrong connection details (password, username, database name)? What happens if you don't select the database before issuing a query? What happens if the query is not a properly formed SQL statement? Restore the code to functionality when you are done.
- Belt color indicates level of training. Modify belts.php so that instead of a single table listing every person in the database, it produces two tables -- one with black and brown belts (the highest levels), and another with the other remaining colors. (Note: you could do this with a single query and PHP code that sifts through the results differently, or you could do it with two separate SQL queries and use the same PHP code to turn it into a table. Which do you think is better?)
- Queries can include the full range of SQL options. First order the listings in your two tables by belt levels, so that all the belts of one color appear together. (The order of colors doesn't matter.) Next modify your table for the upper belt levels to add a third column. This column should show the dojo name on the row for the corresponding lead sensei (whose id is specified in the dojo table). It should be blank for others. You can accomplish this with a lookup on a joined table. Experiment to see how to null values work. Your finished table should look something like this:
- Produce a new file dojos.php that will print the name and address of each dojo.
- Below each dojo name and address, give a listing of all the sensei who teach there and the times of the classes they offer. Then list the students who study there. You may use tables for all of this information, or if you know some other HTML you may choose to format it differently (perhaps in paragraphs or lists).
- Finally, set up your files so I can find them easily. Create a querylab folder inside the public_html on zapp. Put the two files from the lab inside it. Then create a file named index.html inside public_html, and copy the contents below into it. Go to the corresponding URL and test that the links take you to the appropriate pages.
| Name | Belt | Owns |
|---|---|---|
| Gillian Gorilla | Black | Blue Moon Fight Club |
| Luna Lionheart | Black | 13 Monkeys Dojo |
| Sandra Scorpion | Black | Main Street Martial Arts |
| Ida Incredible | Brown | |
| Jill Jovial | Brown | |
| Tracy Teetotaller | Brown |
<html>
<head>
<title>CSC 230 Files</title>
</head>
<body>
<h1>Labs</h1>
<ul>
<li>Query Lab: <a href="querylab/belts.php">Belts</a>,
<a href="querylab/dojos.php">Dojos</a></li>
</ul>
<h1>Homeworks</h1>
<ul>
<li>Homework #3</li>
</ul>
</body>
</html>