Using QueryPath to interact with a SQL database, Part 2
This is the second part in a series on how to use QueryPath to work with databases. In this article we will work with SELECT statements. We will see how to work with returned results.
In the first part of this series, we looked at the process of connecting to a database and executing basic SQL statements. As part of the process, we created a simple table and added a row of data. Here's the final snippet of code that we worked with:
<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
QPDB::baseDB('sqlite:./qpExample.db');
qp()->query('CREATE TABLE IF NOT EXISTS person (firstName, lastName)')
->query(
'INSERT INTO person (firstName, lastName) VALUES (:first, :last)',
array(':first' => 'matt', ':last' => 'butcher')
);
?>
In the example above, we weren't particularly concerned with the return values of those two statements. While QueryPath did store the results we had no reason to work with them. Now we are going to work with SELECT statements and the values returned.
A Simple SELECT
Let's begin with a simple SELECT statement:
<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
QPDB::baseDB('sqlite:./qpExample.db');
qp()->query('SELECT firstName FROM person');
?>
Given the previous article in this series, this should look familiar. We are requiring the QueryPath.php library and the QPDB.php database extension. We use QPDB::baseDB() to set up a shared connection to our database. Finally, we execute a query using the query() method.
Now, let's talk briefly about what is going on behind the scenes. The QPDB extension uses PHP's built-in PDO library. When a statement is executed, QueryPath stores the PDO result set internally. At any time, you can get the PDOStatement object using the getStatement() method. However, the intention is that you will use QueryPath itself to manage the data set.
How can QueryPath be used to work with SQL results? Let's take a look.
Creating an HTML list from a SELECT
In our next example we will run a query and then insert the results directly into the document.
<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
QPDB::baseDB('sqlite:./qpExample.db');
qp(QueryPath::HTML_STUB, 'body')
->queryInto('SELECT firstName FROM person')
->doneWithQuery()
->writeHTML();
?>
This example is a little more complex than the previous example, and I have broken it into multiple lines so that it is easier to read.
The first important thing to notice is that the qp() function now has two arguments. First, it uses the HTML stub document, which provides a standard (but empty) HTML document. Next, it uses the selector body to select the body element in the HTML document.
What does this mean? We are creating a new HTML document, and we are going to populate it with the results of our SQL query.
On the next line, we use the queryInto() method to execute a SQL statement and inject the results directly into the document. When called this way, it will drop all of the results directly into the document. You can also pass in a template (which we will see later) that can be used to format each returned row.
After the queryInto() method, we call doneWithQuery(). This method frees up the result set and readies QueryPath for another database query. While QueryPath does not consider this strictly necessary, some PDO drivers may require this step, and it is good for memory management. For those reasons, you are encouraged to use doneWithQuery() when you are finished working with a query's results.
Finally, the familiar writeHTML() method writes the HTML document to the browser. The resulting document will look something like this:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"></meta>
<title>Untitled</title>
</head>
<body>matt</body>
</html>
The important thing to notice here is the content of the body tag, which is now set to matt. That is the string returned from our database query.
We have taken a step forward in our use of the QPDB extension. Now let's add another step. let's use a template to format some SQL results.
Formatting results with templates
In the previous example, the retrieved database results were simply dumped into the HTML document without any formatting. More often than not, when we write applications, we need to be able to control the formatting of the output. That is what we are going to look at right now.
In addition to the QPDB extension, QueryPath ships with a few other extensions. One of them is the QPTPL extension, which provides support for templates.
A template is a fragment of markup that QueryPath can fill with content. We are going to write a small template. QueryPath will merge the template and the database content, and then add this newly generated markup to an HTML document.
Here's the code:
<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
require 'QueryPath/Extension/QPTPL.php';
QPDB::baseDB('sqlite:./qpExample.db');
$template = '<?xml version="1.0"?>
<div class="name">
<div class="firstName"/>
<div class="lastName"/>
</div>';
qp(QueryPath::HTML_STUB, 'body')
->queryInto('SELECT firstName, lastName FROM person', array(), $template)
->doneWithQuery()
->writeHTML();
?>
The first thing to notice in this example is that we included another extension, 'QueryPath/Extension/QPTPL.php'. This is the template extension.
Next, we define a simple template that looks like this:
<div class="name">
<div class="firstName"/>
<div class="lastName"/>
</div>
Notice that this piece of content has to be well-formed, and that means that the whole template needs to have one root element.
QueryPath will populate the template by matching a database column name to a CSS class name. So the contents of the firstName will be written into the div with class="firstName", and likewise for the lastName column.
With the template extension loaded and a template defined, we can run a QueryPath chain that is just slightly altered from what we saw in the previous section. In fact, the only real change occurs in the queryInto() method:
queryInto('SELECT firstName, lastName FROM person', array(), $template)
Now instead of having just a SQL query we have a query, an empty array, and the template. The array here is used to pass in placeholders were there any in our SQL. Recall an earlier section where we created an insert statement with placeholders. The same can be done here, perhaps to pass arguments into a WHERE clause.
The code above will generate an HTML document that looks like this:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"></meta>
<title>Untitled</title>
</head>
<body>
<div class="name">
<div class="firstName">matt</div>
<div class="lastName">butcher</div>
</div>
</body>
</html>
Using this method of template building, you can build complex markup and then merge data from the database. And it works not only when there is one row, but when there are many. Consider the case where we add another row to the database:
<?php
qp()->query(
'INSERT INTO person (firstName, lastName) VALUES (:first, :last)',
array(':first' => 'immanuel', ':last' => 'kant')
);
?>
Now when we run the template code, we will see HTML that contains both records:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"></meta>
<title>Untitled</title>
</head>
<body>
<div class="name">
<div class="firstName">matt</div>
<div class="lastName">butcher</div>
</div>
<div class="name">
<div class="firstName">immanuel</div>
<div class="lastName">kant</div>
</div>
</body>
</html>
This method of building can be used to dump a database table into an HTML table, a list, or whatever other HTML markup you desire. For examples, check out the documentation for QPTPL.
The queryInto() function will iterate through every item in the returned set. As we will see later, it is possible to do more refined iteration through results.
Iterating Through Results
In the examples above, we used the queryInto() method to iterate through the examples for us. But sometimes finer grained control of a database result set is needed. Let's see how we can control iteration using query() and the nextRow() and *Columns() methods.
<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
require 'QueryPath/Extension/QPTPL.php';
QPDB::baseDB('sqlite:./qpExample.db');
qp(QueryPath::HTML_STUB, 'body')
->query('SELECT firstName, lastName FROM person')
->nextRow()
->appendColumn('firstName')
->append(' ')
->appendColumn('lastName')
->doneWithQuery()
->writeHTML();
?>
In the example above, we use nextRow() to tell QueryPath to select the next row of a result set. Note that we have to use it initially to load the first row.
Once a row is selected, you can use appendColumn(), prependColumn(), columnBefore(), and columnAfter() to insert database contents in much the same way that before(), after(), append(), and prepend() are used to insert XML or HTML. Check out the API reference for more on these functions.
The example above only fetches on row. We could, of course, call nextRow() again to get the next row of data. Unfortunately, PDO drivers do not all currently indicate how many matched rows were returned. Consequently, you will need to execute a COUNT(*) query in order to find the number of rows returned.
However, if you want to do the same thing with all of the rows, but don't want the template functionality provided by queryInto(), you may want to use the withEachRow() method, which essentially repeats an action for each row in a result set. Here's a revised version of the above, using withEachRow() instead of nextRow():
<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
//require 'QueryPath/Extension/QPTPL.php';
QPDB::baseDB('sqlite:./qpExample.db');
qp(QueryPath::HTML_STUB, 'body')
->query('SELECT firstName, lastName FROM person')
->withEachRow()
->appendColumn('firstName', '<span/>')
->doneWithQuery()
->writeHTML();
?>
This time, each row will be added. Note that we have used a second argument to appendColumn(). This argument is called a wrap. The contents of the column will be wrapped with the markup in this argument. The above example, then, will output something like this:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"></meta>
<title>Untitled</title>
</head>
<body>
<span>matt</span>
<span>immanuel</span>
</body>
</html>
If we omit the wrap, no separator would be used for the data, and we would get a string that looked like mattimmanuel.
Before QueryPath 1.3, the wrap needed to be a full XML document, including the XML declaration at the beginning.
Wraps can contain nested elements. Here's an example:
<span><em><strong/></em></span>
This wrap produces content like this:
<span><em><strong>matt</strong></em></span>
<span><em><strong>immanuel</strong></em></span>
That is how query(), withEachRow(), and appendColumn can be used together to merge data into an existing document.
Conclusion
Now we have seen a few different ways of working with database results that have been retrieved from a database. We have looked at several methods for using the results of a SELECT statement in the QueryPath context.
In the third part of this series we will see how QPDB can be used to extract content from an XML or HTML document and then insert that data into a database. <!--break-->
