qpdb

This is the third part in a series on how to use QueryPath to work with databases. In this part, we will see how the QPDB extension to QueryPath can be used to extract data from an XML or HTML document and store it in a database.

In the first part of this series, we looked at the basics of QueryPath and databases. There we connected to a database and created some new tables.

In the second part we took a detailed look at the process of retrieving data from the database and then using that data within QueryPath. Specifically, we selected data from a database and then merged it into existing documents and templates.

Now we will look at the opposite process. We will extract data from an XML or HTML document and insert it into a database.

Importing Data

The focus of these articles has thus far been on taking information from a database and putting it into an HTML document. But QueryPath can be used the other way. It can query a document and place that data into a database. In this article, we will see how.

Creating Tables

Our example document will be an RSS 2.0 feed. First, let's set up a very simple database for storing information. We will need two tables: channel to store information about a channel and item to store information about each item in a feed.

The two tables will be created with SQL like this:

CREATE TABLE IF NOT EXISTS channel
  (channel_id INTEGER PRIMARY KEY AUTOINCREMENT, title, link);
CREATE TABLE IF NOT EXISTS item 
  (channel_id INTEGER, guid, title, link, description);

Keep in mind that we are working with SQLite. Other databases, such as MySQL, will have different data definition syntax.

These are both very minimalist tables. Only a fraction of the information in the original RSS document is going to be stored here. But for our purposes, this will do. The channel table will contain three fields: channel_id, which is just an auto-incremented integer, title and link. These last two will come from the RSS channel information.

The second table, item, will be used to store an RSS item. Its channel_id field will be used to tie an item back to the original channel. The other fields will map to information from the RSS feed.

As we did before, we can execute these queries inside of QueryPath like this:

<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
 
QPDB::baseDB('sqlite:./qpExample.db');
 
 
qp()
  ->query('CREATE TABLE IF NOT EXISTS channel (channel_id INTEGER PRIMARY KEY AUTOINCREMENT, title, link)')
  ->query('CREATE TABLE IF NOT EXISTS item (channel_id INTEGER, guid, title, link, description)');
exit;
?>

Now we have our tables ready.

Inserting Channel Information

Once we have some tables defined we can set up a simple tool for reading the remote RSS feed and injecting the contents into our newly defined tables.

Let's begin with a simple script that adds a channel to the database:

<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
 
QPDB::baseDB('sqlite:./qpExample.db');
 
$url = 'http://localhost:8888/drupal/rss.xml';
$qp = qp($url);
 
$values[':title'] = $qp->find('channel>title')->text();
$values[':link'] = $qp->siblings('link')->text();
$qp->query('INSERT INTO channel (title, link) VALUES (:title, :link)', $values);
?>

This short script does a lot of things. After requiring the two necessary libraries and setting the database connection, the script defines the URL ($url) that points to the RSS feed.

Next, a new QueryPath object is created and stored in $qp. As soon as we pass QueryPath the URL, it will retrieve and parse the remote document. In this case, the document is a standard RSS 2.0 feed.

Our plan is to query that RSS document and then store the retrieved information in our newly defined database. In the code above, we first retrieve the two values we want, storing them in $values. Note that these are keyed using the PDO placeholder syntax (:title and :link).

$values[':title'] = $qp->find('channel>title')->text();
$values[':link'] = $qp->siblings('link')->text();

Next, we use $qp->query() to insert the results into the database, passing in $values as the substitution array.

By the end of the script above, the main channel data will be written to the database.

Getting an Insert ID

One thing we often need to do after an insert is get the auto-generated ID back from the database. Most databases support this operation. In our application we need to do this because channel_id, our primary key, is a parameter that we will need when inserting into the next table.

In QueryPath 1.2, retrieving the last insert ID required just a little bit of fancy footwork. It was done like this:

<?php
$channel_id = $qp->getBaseDB()->lastInsertId();
?>

This essentially gets the PDO database connection (with getBaseDB()) and then calls the PDO function lastInsertId() on that.

In QueryPath 1.3, this has been streamlined slightly, and you will be able to do something like this:

<?php
$channel_id = $qp->getLastInsertID();
?>

Now that we have the last insert ID, we have all we need to iterate through the RSS document and add items to our item table.

Iterating through Matches and Inserting Data

An RSS document has multiple item elements. What we want to do is iterate through those items and store some of the contained data inside of our newly defined database.

Let's continue building on the previous examples. This may make our code snippet longer, but it will help keep things in context.

<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
 
QPDB::baseDB('sqlite:./qpExample.db');
 
$url = 'http://localhost:8888/drupal/rss.xml';
$qp = qp($url);
$values[':title'] = $qp->find('channel>title')->text();
$values[':link'] = $qp->siblings('link')->text();
$qp->query('INSERT INTO channel (title, link) VALUES (:title, :link)', $values);
 
// Get the channel ID
 
// In QueryPath <= 1.2:
//$channel_id = $qp->getBaseDB()->lastInsertId();
 
// In QueryPath 1.3+
$channel_id = $qp->getLastInsertID();
 
$sql = 'INSERT INTO item (channel_id, guid, title, link, description) 
  VALUES (:channel_id, :guid, :title, :link, :description)';
 
foreach($qp->find('item') as $item) {
  $data = array(
    ':channel_id' => $channel_id,
    ':title' => $item->children('title')->text(),
    ':link' => $item->siblings('link')->text(),
    ':description' => $item->siblings('description')->text(),
    ':guid' => $item->siblings('guid')->text(),
  );
  // Notice that we execute from $qp, not $item.
  $qp->query($sql, $data)->doneWithQuery();
}
?>

The new code begins abut halfway down, where $sql is defined. That SQL template will be used for each of our inserts.

The foreach loop beneath is the most important part. Here, QueryPath iterates through each <item/> element in the RSS feed. Each item is wrapped in a QueryPath object assigned to $item.

Inside the loop, we first build up a list of values to insert. These are stored in $data. Since $data is going to used to substitute values into the SQL, we use the placeholder syntax, :channel_id, :title, and so on.

The $channel_id will be the same for every item, since they all belong to the same RSS channel. All other values come from the RSS item, and so we query the item to find the title, link, description, and GUID.

Finally, we perform the INSERT:

$qp->query($sql, $data)->doneWithQuery();

As a slight performance optimization, we run the query using the $qp object instead of the $item object.

That's all there is to our RSS importing script. Now our new database should contain information retrieved from the RSS feed.

Retrieving the Data

Now that we have the data in the database, we can retrieve it using the methods we studied in the previous part of this series. For example, let's add one more part to our script: Let's dump a list of item titles into an HTML document.

<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
 
QPDB::baseDB('sqlite:./qpExample.db');
 
qp(QueryPath::HTML_STUB, 'body')
  ->query('SELECT title FROM item')
  ->withEachRow()
  ->appendColumn('title', '<p/>')
  ->doneWithQuery()
  ->writeHTML();
?>

This little snippet of code will select all titles from the item table. Then each title will be wrapped in a paragraph and added to the stub HTML document. Finally, the document will be written out. This will produce HTML 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>
      <p>Chicago Bears players warm up to Jay Cutler</p>
      <p>Broncos trade Cutler to Bears</p>
      <p>Thanks to benefactors, fountain will offer summer respite</p>
      <p>Learning Drupal 6 Module Development</p>
      <p>Story with lots of tags</p>
      <p>A Sticky Node</p>
      <p>De natura mentis humanae: quod ipsa sit notior qu&agrave;m corpus.</p>
      <p>De iis quae in dubium revocari possunt.</p>
      <p>This is a node</p>
    </body>
    </html>

At this point in the article, we have created some tables, read a remote document into these tables, and then retrieved data back out of the tables.

Conclusion

This is the final article in our three-part series on using QueryPath to work with databases. In the first article, we performed basic database tasks like connecting and running basic queries. The second article focused on using SELECT to retrieve data, and also using QueryPath's built in tools for working with the returned results. This third part has focused on using QueryPath's tools to retrieve information from an XML document, and then store that information inside of a database.

The major features of QueryPath's QPDB extension have been explained and used in this series, but there is still more. You can, for example, share a database connection with non-QueryPath tools, or work more directly with the PDO result set. But this series has, I hope, equipped you to make use of the most commonly used QPDB features.

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.

QueryPath is designed to make working with XML and HTML simpler. It can also be used to interact with relational databases. Through the QPDB extension, which is bundled with QueryPath, QueryPath can execute queries and work with them in the context of an XML or HTML document. This series of articles provides an overview of this capability.

In this first part, we will look at connecting to a database and executing basic SQL statements. In the second part we will look at executing SELECT statements and managing the results from within QueryPath.

Background

Before we dive into some examples, a little bit of background is in order.

The QP database tool works with most of the databases that PHP 5 supports. To be more specific, it works with any PDO driver that ships with PHP 5. This includes MySQL, PostgreSQL, and SQLite. PDO (PHP Data Objects) is a driver abstraction layer that provides a standard set of tools for working with a wide variety of databases (there are at least ten drivers). It provides the perfect fit for QueryPath.

In this document, we are going to connect to a SQLite database. With just minimal changes to the connection string and DDL, the examples here can be made to run on MySQL or PostgreSQL.

Other Database Libraries

The QPDB extension uses PDO. However, it is possible to write other QueryPath extensions that use different underlying database libraries. For example, the Drupal QueryPath module includes a database layer for working with the Drupal database API.

A First Example: Connecting to a database and defining a table

The first example we are going to look at will connect to a SQLite database and create a new table. Here's the code:

<?php
require 'QueryPath/QueryPath.php';
require 'QueryPath/Extension/QPDB.php';
 
qp()->dbInit('sqlite:./qpExample.db')
  ->query('CREATE TABLE IF NOT EXISTS person (firstName, lastName)');
?>

The first thing to note about this example is that we require not only the QueryPath.php library, but also the QueryPath/Extension/QPDB.php extension. This is how extensions are loaded with QueryPath. As soon as the extension is loaded, it becomes available for use.

Next, we have a single QueryPath chain which initializes the database and then runs a SQL query. There are two database methods used:

• dbInit()
• query()

Let's start by looking at dbInit() and other ways of initializing a database connection.

Initializing the database

The dbInit() method provides one way of connecting to a database. Like any PDO connection, dbInit() takes a connection string called a DSN. The one above is for SQLite.

A DSN begins with the database name followed by a colon. From there, the rest of the string is determined by the PDO driver. Here are a few examples:

• SQLite: sqlite:/path/to/database.db
• MySQL: mysql:host=example.com;dbname=example
• PostgreSQL: pgsql:host=Example.com dbname=example
• IBM DB2: ibm:DSN=DB2_9

The other six PDO drivers each have their own DSN formats as well. Ours uses the SQLite database driver and opens the database file called qpExample.db.

There are actually two ways of connecting to a database. The first is to use dbInit(), as we have done above. This is a per object connection, meaning that each QueryPath object must open its own connection to the database.

A second method shares one database connection across multiple QueryPath instances. This is called a shared database connection, and it is the preferred method anytime your code is doing more than a couple of queries.

Some other database extensions for QueryPath (such as the Drupal database extension) do not require database connection management, since that is provided by some other library.

Per-object Connections

The dbInit() method will connect the current QueryPath object to the database. That means that each QueryPath object that needs to execute database queries will need to initialize, first.

Shared Database Connections (the preferred method)

There is a second way of initializing QueryPath, and this second way makes it possible to share one connection across multiple QueryPath objects. It is the preferred method in any environment where QueryPath will be making frequent use of a database.

In this method, the database connection is opened outside of an individual QueryPath object. We could rewrite our initial example using a shared connection:

<?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)');
?>

In this case, we use QPDB::baseDB() to open a connection to the database. This connection is then referenced internally by the QPDB extension, and there is no need to use dbInit().

Note that while SQLite requires only one argument to open a database connection, other databases (including MySQL and PostgreSQL) may require other options, such as login credentials. These can be passed to the underlying database driver using a second argument, an associative array of information. The API documentation for QPDB::baseDB() lists all options.

That's how to connect. Next, we will look at functions to query the database.

<

h3>Querying the Database

In our examples above, we created a new table using code that looked like this:

<?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)');
?>

In the last line of code, some SQL is executed using the query() method. In a nutshell, this method executes a SQL statement.

In the example above, we use the query() method to execute a CREATE TABLE statement. This will create for us a new table in the database -- a table named person with a firstName and a lastName column.

Let's say that we want to add an entry to the table. We could do so by adding another query to the code above:

<?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')
  );
?>

The second query above, which is split over four lines because of our limited margins, is a simple INSERT statement. It inserts a first name
and a last name into the person table.

Take a close look at the VALUES portion of the Query:

INSERT INTO person (firstName, lastName)  VALUES (:first, :last)

The two values, :first and :last are placeholders. They will be replaced by the values in the passed-in array:

array(':first' => 'matt', ':last' => 'butcher')

When the query above is executed, :first and :last will be replaced by matt and butcher. Underneath the hood, what is actually happening here is that the basic query is being prepared, which means that the database engine is pre-compiling the SQL statement. Once the statement is prepared, the data can be inserted in a way that does not leave the database susceptible to SQL injection attacks.

IMPORTANT: The placeholders are not enclosed in quotation marks, even if they represent strings. The database driver automatically escapes and encodes data as necessary. This added layer of automation is another reason why using PDO-based database drivers can improve the security of your application.

Now we have opened a connection to a database, created a table, and added some data. In part two we will see what we can actually do with this data! We will be executing SELECT statements and then managing them with QueryPath.

In part three we will see how to read XML or HTML and insert the found data into a database.