Using QueryPath to interact with a SQL database, Part 1

May 7 2009

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.

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. <!--break-->