This lab explores the interface between PHP and SQLite. 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. Finally, if there is time, you can try out pages that actually modify the database in some way, for example by inserting a new element.
A Sample Database
For these exercises, you will use a preexisting database provided as a part of the lab. Later, for your homework assignment, you will build a database of your own. You should download the database file to the directory where you will be working. (In case there is any problem, a text dump of the database entries is also available.)
The database contains four relations, whose schema 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: a dojo is a martial arts training place, and a sensei is a martial arts teacher.)
You may wish to experiment with some simple queries on this database using the native SQLite 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 probably 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. Here's a piece of PHP that will create a table of all the people in the database, along with their belt level.
<?php
try {
/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:martial.db3");
$results = $dbh->query("SELECT * FROM People");
echo '<table border="1" cellpadding="1">';
echo '<tr><th>Name</th><th>Belt</th></tr>';
while ($row = $results->fetch(PDO::FETCH_ASSOC)) {
$name = $row['name'];
$belt = $row['belt'];
printf("<tr><td>%s</td><td>%s</td></th>\n",$name,$belt);
}
echo "</table>";
} catch(PDOException $e) {
echo "Database access problem:<br />";
echo $e->getMessage();
}
?>
To Do: See if you can produce something similar that will print a list of the name and address of each dojo. If you are feeling like a challenge, you could also try adding the sensei's name to the table. Until we learn about table joins, this is actually fairly tricky -- you'll need to query People individually for each table row to find the name of the sensei. This is a good place for e the use of prepared queries, since they are far more efficient when used repeatedly inside a loop.
Query Functions
The example above works fine, but it can only really be used in one way: to print emit the table of names and belt levels. That would be fine if we never wanted to do anything else -- like sorting the table by name or belt level. If we wanted to do that, we would have to write a new query routine, or alter the old one. A better strategy is to write a more general function, that would query the table for the bag of names and return it as an array. Then we could easily do whatever we wanted with the array, much as we did during the lab on arrays -- and if we have functions for these, like the ones we wrote in the lab on functions, then changing what our script does is a simple matter of changing a few function calls. Below is an example of a function that reads the name column from the People table.
// Returns an array of the values stored in the names column of the People table
function queryNames() {
try {
$dbh = new PDO("sqlite:martial.db3"); // Connect to the SQLite database via PDO
$qresults = $dbh->query("SELECT name FROM People");
if ($qresults) {
$qresults = $qresults->fetchAll(); // Put all the query results into an array
foreach ($qresults as $row) {
$results[] = $row[0];
}
} else {
$results = Array();
}
} catch(PDOException $e) {
echo "Database access problem:<br />";
echo $e->getMessage();
$results = NULL;
}
return $results;
}
If we call the function above, followed by one that emits an array as an unsorted list, we can easily display all the names in the database.
To Do: Write a new function, based upon the one above, that returns the array of belt levels instead of names. It's not very different from the example shown, is it? Next, generalize these two functions to create queryColumn($table,$col) that will return an array holding the values of an arbitrary column from an arbitrary table.
When you have finished queryColumn, use it to retrieve arrays of dojo names and addresses. You then can print these out in a table using the function provided below.
// Emits the elements of the array, as an unordered list.
function tableArray($arrs,$headers = NULL) {
echo "<table>\n";
if (!is_null($headers)) {
echo "<tr>";
foreach ($headers as $elt) {
echo "<th>$elt</th>";
}
echo "</tr>\n";
}
for ($i=0; $i<sizeof($arrs[0]); $i++) {
echo "<tr>";
foreach($arrs as $arr) {
$elt = $arr[$i];
echo "<td>$elt</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
}
A word is in order about how to use the function above. The first argument is an array of arrays, holding all the data for the table organized by column. The second argument is an array of column headings. Thus if $name$ and $belt hold the arrays of names and corresponding belt levels, you could emit a table using the following call:
tableArray(Array($names,$belts),Array("Name","Belt Level"));
More General Still
The function you wrote above is more general than what we started with, but it still suffers from limitations. It is not really intended for querying multiple columns, or joined tables, or queries with WHERE clauses. The solution is to simplify: create a new function that accepts any query string as an argument, and passes it along to the database. There are two disadvantages to this approach: first, the likelihood of a malformed query increases since the function has no control over the query string supplied. Second, the result format must be more complicated since we don't know in advance what columns will be requested in any given query. The best way to handle the latter problem is also to simplify: return the results as reported by the database, with one caveat. If we supply the optional argument PDO:FETCH_ASSOC to the fetchAll method call, it will return the results as a linear array of rows, where each row is an associative array of the requested columns. This format can be further massaged using additional data manipulation functions. The separateCols function defined below converts the raw output to an associative array of linear arrays -- each column becomes its own separate subarray. If the optional $cols argument is supplied, then the result is returned instead as a linear array of linear arrays -- suitable for the list assignment construct. An example usage follows the function definition.
// Converts an linear array of associative arrays to an associative array of linear arrays
// (This might be used on the results of queryDB)
// If optional $cols argument is included, returns results with numeric indices (for use with list assignment).
function separateCols($qarr,$cols=NULL) {
foreach ($qarr as $row) {
foreach ($row as $col => $val) {
$result[$col][] = $val;
}
}
if (!is_null($cols)) {
foreach ($cols as $col) {
$result2[] = $result[$col];
}
$result = $result2;
}
return $result;
}
// Sample usage:
$dojo2roster = queryDB("SELECT name,belt FROM People JOIN StudentAt ON (id=student) WHERE dojo=2");
list($dojo2names,$dojo2belts) = separateCols($dojo2roster,Array('name','belt'));
tableArray(array($dojo2names,$dojo2belts),array("Name","Belt Level"));
You will need to write queryDB yourself before the above example will work.
To Do: Using the approach above, emit the following.
- A list of names of all white belts. (You may wish to use your function that prints an array as a list.)
- A list of names of all black belts. (You may wish to use your function that prints an array as a list.)
- A table showing the sensei name, belt level, and class times at Blue Moon Fight Club.
- [Challenge Question] A table showing all students who study at more than one dojo.