Docs For Class QPDB

Description

Located in File: /src/QueryPath/Extension/QPDB.php

Provide DB access to a QueryPath object.

This extension provides tools for communicating with a database using the QueryPath library. It relies upon PDO for underlying database communiction. This means that it supports all databases that PDO supports, including MySQL, PostgreSQL, and SQLite.

Here is an extended example taken from the unit tests for this library.

Let's say we create a database with code like this:

<?php
public function setUp() {
$this->db = new PDO($this->dsn);
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->db->exec('CREATE TABLE IF NOT EXISTS qpdb_test (colOne, colTwo, colThree)');
$stmt = $this->db->prepare(
'INSERT INTO qpdb_test (colOne, colTwo, colThree) VALUES (:one, :two, :three)'
);
for ($i = 0; $i < 5; ++$i) {
$vals = array(':one' => 'Title ' . $i, ':two' => 'Body ' . $i, ':three' => 'Footer ' . $i);
$stmt->execute($vals);
$stmt->closeCursor();
}
}
?>

From QueryPath with QPDB, we can now do very elaborate DB chains like this:

<?php
$sql = 'SELECT * FROM qpdb_test';
$args = array();
$qp = qp(QueryPath::HTML_STUB, 'body') // Open a stub HTML doc and select <body/>
->append('<h1></h1>') // Add <h1/>
->children() // Select the <h1/>
->dbInit($this->dsn) // Connect to the database
->query($sql, $args) // Execute the SQL query
->nextRow() // Select a row. By default, no row is selected.
->appendColumn('colOne') // Append Row 1, Col 1 (Title 0)
->parent() // Go back to the <body/>
->append('<p/>') // Append a <p/> to the body
->find('p') // Find the <p/> we just created.
->nextRow() // Advance to row 2
->prependColumn('colTwo') // Get row 2, col 2. (Body 1)
->columnAfter('colThree') // Get row 2 col 3. (Footer 1)
->doneWithQuery() // Let QueryPath clean up. YOU SHOULD ALWAYS DO THIS.
->writeHTML(); // Write the output as HTML.
?>

With the code above, we step through the document, selectively building elements as we go, and then populating this elements with data from our initial query.

When the last command, QueryPath:::writeHTML(), is run, we will get output 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" />
<title>Untitled</title>
</head>
<body>
<h1>Title 0</h1>
<p>Body 1</p>
Footer 1</body>
</html>

Notice the body section in particular. This is where the data has been inserted.

Sometimes you want to do something a lot simpler, like give QueryPath a template and have it navigate a query, inserting the data into a template, and then inserting the template into the document. This can be done simply with the queryInto() function.

Here's an example from another unit test:

<?php
$template = '<?xml version="1.0"?><li class="colOne"/>';
$sql = 'SELECT * FROM qpdb_test';
$args = array();
$qp = qp(QueryPath::HTML_STUB, 'body')
->append('<ul/>') // Add a new <ul/>
->children() // Select the <ul/>
->dbInit($this->dsn) // Initialize the DB
// BIG LINE: Query the results, run them through the template, and insert them.
->queryInto($sql, $args, $template)
->doneWithQuery()
->writeHTML(); // Write the results as HTML.
?>

The simple code above puts the first column of the select statement into an unordered list. The example output 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>
<ul>
<li class="colOne">Title 0</li>
<li class="colOne">Title 1</li>
<li class="colOne">Title 2</li>
<li class="colOne">Title 3</li>
<li class="colOne">Title 4</li>
</ul>
</body>
</html>

Typical starting methods for this class are QPDB::baseDB(), QPDB::query(), and QPDB::queryInto().

Class Variables

Summary:

static mixed $con

mixed $cycleRows

mixed $db

mixed $dsn

mixed $opts

mixed $qp

mixed $row

mixed $stmt

static $con = NULL (line 235)

Data type : mixed

access: - protected

$cycleRows = FALSE (line 299)

Data type : mixed

Used to control whether or not all rows in a result should be cycled through.

access: - protected

$db (line 230)

Data type : mixed

access: - protected

$dsn (line 229)

Data type : mixed

access: - protected

$opts (line 231)

Data type : mixed

access: - protected

$qp (line 228)

Data type : mixed

access: - protected

$row = NULL (line 232)

Data type : mixed

access: - protected

$stmt = NULL (line 233)

Data type : mixed

access: - protected

Class Constants

Summary:

Method Detail

Summary:

static void baseDB (string $dsn, [array $options = array()])

static void getBaseDB ()

QPDB __construct (QueryPath $qp)

void addData (mixed $columnName, [string $qpFunc = 'append'], [string $wrap = NULL])

void appendColumn (mixed $columnName, [string $wrap = NULL])

void columnAfter (mixed $columnName, [string $wrap = NULL])

void columnBefore (mixed $columnName, [string $wrap = NULL])

QueryPath dbInit (string $dsn, [array $options = array()])

QueryPath doneWithQuery ()

void exec (string $sql)

mixed getLastInsertID ()

PDOStatement getStatement ()

QueryPath nextRow ()

void prependColumn (mixed $columnName, [string $wrap = NULL])

void query (string $sql, [array $args = array()])

void queryInto (string $sql, [array $args = array()], [mixed $template = NULL])

QueryPath withEachRow ()

Static Method baseDB (line 272)

void baseDB( string $dsn, [array $options = array()])

Create a new database instance for all QueryPath objects to share.

This method need be called only once. From there, other QPDB instances will (by default) share the same database instance.

Normally, a DSN should be passed in. Username, password, and db params are all passed in using the options array.

On rare occasions, it may be more fitting to pass in an existing database connection (which must be a PDO object). In such cases, the $dsn parameter can take a PDO object instead of a DSN string. The standard options will be ignored, though.

Warning: If you pass in a PDO object that is configured to NOT throw exceptions, you will need to handle error checking differently.

Remember to always use QPDB::doneWithQuery() when you are done with a query. It gives PDO a chance to clean up open connections that may prevent other instances from accessing or modifying data.

Parameters

string $dsn: The DSN of the database to connect to. You can also pass in a PDO object, which will set the QPDB object's database to the one passed in.
array $options: An array of configuration options. The following options are currently supported:
- username => (string)
- password => (string)
- db params => (array) These will be passed into the new PDO object. See the PDO documentation for a list of options. By default, the only flag set is PDO::ATTR_ERRMODE, which is set to PDO::ERRMODE_EXCEPTION.

Info

throws - PDOException An exception may be thrown if the connection cannot be made.

Static Method getBaseDB (line 294)

void getBaseDB( )

This method may be used to share the connection with other, non-QueryPath objects.

Info

Constructor __construct (line 304)

QPDB __construct( QueryPath $qp)

Implementation of:

QueryPathExtension::__construct()

Construct a new QPDB object. This is usually done by QueryPath itself.

Parameters

QueryPath $qp:

Info

access - public

Method addData (line 559)

void addData( mixed $columnName, [string $qpFunc = 'append'], [string $wrap = NULL])

This is the implementation behind the append/prepend and before/after methods.

Parameters

mixed $columnName: The name of the column whose data should be added to the currently selected elements. This can be either a string or an array of strings.
string $qpFunc: The name of the QueryPath function that should be executed to insert data into the object.
string $wrap: The HTML/XML markup that will be used to wrap around the column data before the data is inserted into the QueryPath object.

Info

access - protected

Method appendColumn (line 634)

void appendColumn( mixed $columnName, [string $wrap = NULL])

Append the data in the given column(s) to the QueryPath.

This appends data to every item in the current QueryPath. The data will be retrieved from the database result, using $columnName as the key.

Parameters

mixed $columnName: Either a string or an array of strings. The value(s) here should match one or more column headers from the current SQL query's results.
string $wrap: IF this is supplied, then the value or values retrieved from the database will be wrapped in this HTML/XML before being inserted into the QueryPath.

Info

see - QueryPath::wrap()
see - QueryPath::append()
access - public

Method columnAfter (line 692)

void columnAfter( mixed $columnName, [string $wrap = NULL])

Insert data from the given column(s) after each element in the QueryPath.

This inserts data from the given columns after each element in the QueryPath object. IF HTML/XML is given in the $wrap parameter, then the column data will be wrapped in that markup before being inserted into the QueryPath.

Parameters

mixed $columnName: Either a string or an array of strings. The value(s) here should match one or more column headers from the current SQL query's results.
string $wrap: IF this is supplied, then the value or values retrieved from the database will be wrapped in this HTML/XML before being inserted into the QueryPath.

Info

see - QPDB::appendColumn()
see - QueryPath::after()
see - QueryPath::wrap()
access - public

Method columnBefore (line 671)

void columnBefore( mixed $columnName, [string $wrap = NULL])

Insert the data from the given column before each element in the QueryPath.

This inserts the data before each element in the currently matched QueryPath.

Parameters

mixed $columnName: Either a string or an array of strings. The value(s) here should match one or more column headers from the current SQL query's results.
string $wrap: IF this is supplied, then the value or values retrieved from the database will be wrapped in this HTML/XML before being inserted into the QueryPath.

Info

see - QPDB::prependColumn()
see - QueryPath::before()
see - QueryPath::wrap()
access - public

Method dbInit (line 343)

QueryPath dbInit( string $dsn, [array $options = array()])

Create a new connection to the database. Use the PDO DSN syntax for a connection string.

This creates a database connection that will last for the duration of the QueryPath object. This method ought to be used only in two cases:

When you will only run a couple of queries during the life of the process.
When you need to connect to a database that will only be used for a few things.

Otherwise, you should use QPDB::baseDB to configure a single database connection that all of QueryPath can share.

Remember to always use QPDB::doneWithQuery() when you are done with a query. It gives PDO a chance to clean up open connections that may prevent other instances from accessing or modifying data.

Parameters

string $dsn: The PDO DSN connection string.
array $options: Connection options. The following options are supported:
- username => (string)
- password => (string)
- db params => (array) These will be passed into the new PDO object. See the PDO documentation for a list of options. By default, the only flag set is PDO::ATTR_ERRMODE, which is set to PDO::ERRMODE_EXCEPTION.

Info

return - The QueryPath object.
throws - PDOException The PDO library is configured to throw exceptions, so any of the database functions may throw a PDOException.
access - public

Method doneWithQuery (line 477)

QueryPath doneWithQuery( )

Free up resources when a query is no longer used.

This function should always be called when the database results for a query are no longer needed. This frees up the database cursor, discards the data, and resets resources for future use.

If this method is not called, some PDO database drivers will not allow subsequent queries, while others will keep tables in a locked state where writes will not be allowed.

Info

return - The QueryPath object.
access - public

Method exec (line 502)

void exec( string $sql)

Execute a SQL query, but expect no value.

If your SQL query will have parameters, you are encouraged to use query(), which includes built-in SQL Injection protection.

Parameters

string $sql: A SQL statement.

Info

throws - PDOException An exception will be thrown if a query cannot be executed.
access - public

Method getLastInsertID (line 614)

mixed getLastInsertID( )

Get the last insert ID.

This will only return a meaningful result when used after an INSERT.

Info

return - Return the ID from the last insert. The value and behavior of this is database-dependent. See the official PDO driver documentation for the database you are using.
since - 1.3
access - public

Method getStatement (line 599)

PDOStatement getStatement( )

Get back the raw PDOStatement object after a query().

Info

return - Return the PDO statement object. If this is called and no statement has been executed (or the statement has already been cleaned up), this will return NULL.
access - public

Method nextRow (line 523)

QueryPath nextRow( )

Advance the query results row cursor.

In a result set where more than one row was returned, this will move the pointer to the next row in the set.

The PDO library does not have a consistent way of determining how many rows a result set has. The suggested technique is to first execute a COUNT() SQL query and get the data from that.

The withEachRow() method will begin at the next row after the currently selected one.

Info

return - The QueryPath object.
access - public

Method prependColumn (line 652)

void prependColumn( mixed $columnName, [string $wrap = NULL])

Prepend the data from the given column into the QueryPath.

This takes the data from the given column(s) and inserts it into each element currently found in the QueryPath.

Parameters

mixed $columnName: Either a string or an array of strings. The value(s) here should match one or more column headers from the current SQL query's results.
string $wrap: IF this is supplied, then the value or values retrieved from the database will be wrapped in this HTML/XML before being inserted into the QueryPath.

Info

see - QueryPath::wrap()
see - QueryPath::prepend()
access - public

Method query (line 405)

void query( string $sql, [array $args = array()])

Execute a SQL query, and store the results.

This will execute a SQL query (as a prepared statement), and then store the results internally for later use. The data can be iterated using nextRow(). QueryPath can also be instructed to do internal iteration using the withEachRow() method. Finally, on the occasion that the statement itself is needed, getStatement() can be used.

Use this when you need to access the results of a query, or when the parameter to a query should be escaped. If the query takes no external parameters and does not return results, you may wish to use the (ever so slightly faster) exec() function instead.

Make sure you use doneWithQuery() after finishing with the database results returned by this method.

Usage

Here is a simple example:

<?php
QPQDB::baseDB($someDSN);
$args = array(':something' => 'myColumn');
qp()->query('SELECT :something FROM foo', $args)->doneWithQuery();
?>

The above would execute the given query, substituting myColumn in place of :something before executing the query The doneWithQuery() method indicates that we are not going to use the results for anything. This method discards the results.

A more typical use of the query() function would involve inserting data using appendColumn(), prependColumn(), columnBefore(), or columnAfter(). See the main documentation for QPDB to view a more realistic example.

Parameters

string $sql: The query to be executed.
array $args: An associative array of substitutions to make.

Info

throws - PDOException Throws an exception if the query cannot be executed.
access - public

Method queryInto (line 444)

void queryInto( string $sql, [array $args = array()], [mixed $template = NULL])

Query and append the results.

Run a query and inject the results directly into the elements in the QueryPath object.

If the third argument is empty, the data will be inserted directly into the QueryPath elements unaltered. However, if a template is provided in the third parameter, the query data will be merged into that template and then be added to each QueryPath element.

The template will be merged once for each row, even if no row data is appended into the template.

A template is simply a piece of markup labeled for insertion of data. See QPTPL and QPTPL.php for more information.

Since this does not use a stanard query(), there is no need to call doneWithQuery() after this method.

Parameters

string $sql: The SQL query to execute. In this context, the query is typically a SELECT statement.
array $args: An array of arguments to be substituted into the query. See query() for details.
mixed $template: A template into which query results will be merged prior to being appended into the QueryPath. For details on the template, see QPTPL::tpl().

Info

see - QPDB::query()
see - QPTPL::tpl()
see - QPTPL.php
access - public

Method withEachRow (line 541)

QueryPath withEachRow( )

Set the object to use each row, instead of only one row.

This is used primarily to instruct QPDB to iterate through all of the rows when appending, prepending, inserting before, or inserting after.

Info

return - The QueryPath object.
see - QPDB::columnAfter()
see - QPDB::appendColumn()
see - QPDB::columnBefore()
see - QPDB::prependColumn()
access - public