PGV Database Functions

Until version 4.2.1, PhpGedView used the Pear::DB library for database access. From version 4.2.2, it is planned to use the PDO library. During the migration period, there may be elements of both in use at the same time.

Rationale for change

• Pear::DB is deprecated, PDO is actively maintained
• Pear::DB is a separate library, PDO is a native part of PHP5
• PDO is faster than Pear::DB
• PDO supports prepared statements

Comparison

Basic usage of the two libraries is very similar. See these two examples:

// Pear::DB                                      // PDO
gloabl $DBCONN; // a database connection         global $DBH; // a database handle
                                                 
// SQL with no parameters/returned rows          // SQL with no parameters/returned rows
$DBCONN->query("DROP TABLE my_table");           $DBH->exec("DROP TABLE my_table");
                                                 
// SQL with fetched rows                         // SQL with fetched rows
$res=$DBCONN->query("SELECT * FROM my_table");   $statement=$DBH->prepare("SELECT * FROM my_table");
                                                 $statement->execute();
while ($row=$res->fetchRow()) {                  while ($row=$statement->fetch()) {
  print_r($res);                                   print_r($res);
}                                                }
$res->free();                                    $statement->closeCursor();

class PGV_DB - a wrapper for PDO

As the above example shows, PDO is (like Pear::DB) repetitive, verbose and hence error prone. It doesn't provide any facility for profiling or logging. To resolve this, the class PGV_DB was written. It provides a number of features.

Singleton Pattern

The singleton pattern is a design approach which ensures that only a single instance exists of a particular entity.

Since we will only be using a single database connection, we can either create this value once and pass it using global or fetch it as we need it using getInstance().

// These are equivalent

function foo() {
  global $DBH;
}

function foo() {
  $DBH=PGV_DB::getInstance();
}

Although this looks like more typing, we typically perform just a few action on this single instance many times over. Instead, we would use the following syntax. It means that we no longer need to pass a global $DBH variable to every function that accesses the database.

// These are equivalent

$DBH=PGV_DB->getInstance();
$statement=$DBH->prepare($sql);

$statement=PGV_DB::prepare($sql);

Fluent Interface

A fluent interface allows commands to be chained, without the need for intermediate/temporary variables. This leads to much more readable code.

// These are equivalent

$statement=PGV_DB::prepare("SELECT COUNT(*) FROM employees WHERE salary>?");
$statement->execute(array(10000));
$total=$statement->fetchOne();

$total=
  PGV_DB::prepare("SELECT COUNT(*) FROM employees WHERE salary>?")
  ->execute(array(10000))
  ->fetchOne();

Automatic Execution

PGV_DB keeps track of whether a statement has been executed before you fetch data from it. This means that a parameter-less execute() will be called automatically. e.g.

// These are equivalent

$results=
  PGV_DB::prepare("SELECT MIN(age), MAX(age) FROM employees GROUP BY department")
  ->execute()
  ->fetchAll();

$results=
  PGV_DB::prepare("SELECT MIN(age), MAX(age) FROM employees GROUP BY department")
  ->fetchAll();

HOWTO / Examples

The following examples demonstrate how to perform various tasks.

DDL Statements

A statement with no parameters or returned rows (typically a DDL statement), can be executed in a single step.

PGV_DB::exec("CREATE TABLE my_table (col1 INTEGER, col2 INTEGER)");

Other statements need to be executed using a two-step approach (prepare and execute).

Note that commands such as "OPTIMIZE TABLE" actually return rows, and need to be executed as if they were SELECT queries.

Parameters

Most queries have parameters (bound variables). These can either specified individually or as an array.

// These are equivalent

PGV_DB::prepare("UPDATE my_table SET column=? WHERE id=?")
  ->execute(array($value, $id));

PGV_DB::prepare("UPDATE my_table SET column=? WHERE id=?")
  ->bindValue(1, $value)
  ->bindValue(2, $id)
  ->execute();

The latter format can be useful when re-using the same statement, as it is only necessary to set parameters that have changed.

$statement=
  PGV_DB::prepare("UPDATE my_table SET column=? WHERE id=?")
  ->bindValue(1, $value)
  ->bindValue(2, $id)
  ->execute();

$statement
  ->bindvalue(2, $other_id)
  ->execute();

$statement
  ->bindvalue(2, $yet_other_id)
  ->execute();

You can easily run the same query several times, with different parameters

// These are equivalent

$statement=PGV_DB::prepare("INSERT INTO my_table (col, id) VALUES (?, ?)");
$statement->execute($value1, $id1));
$statement->execute($value2, $id2));
$statement->execute($value3, $id3));

PGV_DB::prepare("INSERT INTO my_table (col, id) VALUES (?, ?)")
  ->execute($value1, $id1))
  ->execute($value2, $id2))
  ->execute($value3, $id3));

Instead of using "?" and numeric placeholders, you could use named placeholders.

// These are equivalent

PGV_DB::prepare("UPDATE my_table SET column=:foo WHERE id=:bar")
  ->execute(array('foo'=>$value, 'bar'=>$id));

PGV_DB::prepare("UPDATE my_table SET column=:foo WHERE id=:bar")
  ->bindValue('foo', $value)
  ->bindValue('bar', $id)
  ->execute();

Parameter Types

The bindValue() functions will look at the type of the value, and use it as a null/integer/string as appropriate. You can force the type by supplying a third parameter. e.g.

$statement->bindValue(1, $var, PDO::PARAM_STR);
$statement->bindValue(1, $var, PDO::PARAM_INT);
$statement->bindValue(1, $var, PDO::PARAM_NULL);

This can be used to ensure that only integers are inserted into certain columns, etc. these two statements are equivalent:

$statement->bindValue(1, $var, PDO::PARAM_INT);
$statement->bindValue(1, (int)$var);

It is not generally necessary to use this parameter. By default, all values (including numbers) are quoted as strings, and all databases will accept quoted numeric values.

Fetching Data

The traditional way of fetching rows of data is to use the following sequence of events

$statement=PGV_DB::prepare("SELECT * FROM table")->execute();
while ($row=$statement->fetch()) {
  print_r($row);
}
$statement->closeCursor();

Fetching data one row at a time can be very efficient. Depending on the design of the query, the database may be able to send the first rows before it has found the last ones. It also reduces memory requirements, as only one row needs to be held in memory at a time. However, for the majority of PhpGedView's queries, these factors don't apply. Instead we want to fetch the data as simply as possible.

To fetch all the rows from a query, as an array, use the fetchAll() function. This will call execute() if needed, and closeCurser() afterwards. The previous example can be written as

$rows=PGV_DB::prepare("SELECT * FROM table")->fetchAll();
foreach ($rows as $row)) {
  print_r($row);
}

Note that where no rows are found, fetchAll() returns an empty array.

Where we only want a single row from a query, such as when selecting data based on a primary key, use the fetchOneRow() function.

$row=
  PGV_DB::prepare("SELECT * FROM table WHERE pk=?")
  ->bindValue(1, $key)
  ->fetchOneRow();

Where we only want a single value from a single row, such as when using aggregate functions, use the fetchOne() function.

$count=PGV_DB::prepare("SELECT COUNT(*) FROM employees")->fetchOne();

Note that where no row is found, fetchOneRow() and fetchOne() both return null.

To fetch two columns, as an associative array of col1=>col2, use the fetchAssoc() function

$count=PGV_DB::prepare("SELECT user_id, last_login FROM users")->fetchAssoc();

Row Format

The fetch(), fetchOneRow() and fetchAll() functions take an optional parameter. This parameter determines the format of the returned row

• PDO::FETCH_OBJ - fetch the row as an anonymous object. echo $row->col1, $row->col2, ...
• PDO::FETCH_ASSOC - fetch the row as an associative array. echo $row['col1'], $row['col2'], ...
• PDO::FETCH_NUM - fetch the row as a numeric array. echo $row[0], $row[1], ...

The default is FETCH_OBJ.

UPDATE/INSERT/DELETE Statements

Queries without returned rows just need a call to execute().

PGV_DB::prepare("UPDATE my_table SET column=? WHERE id=?")
  ->execute(array($col, $id));

Error Handling

When an error occurs, a PDOException is thrown. For example

try {
   PGV_DB::exec("CREATE TABLE my_table (col1 INTEGER, col2 INTEGER)");
} catch (PDOException $ex) {
   //echo "Couldn't create the table ", $ex->getMessage();
}

Native PDO Functions

All the native PDO functions are still available, and are passed directly to the underlying object. For example, to access the PDO transaction functions, you could do this.

PGV_DB::beginTransaction();
PGV_DB::prepare("INSERT INTO table foo (?)")->execute(array("bar"));
PGV_DB::commit;();

Logging

All queries are logged, along with their execution times and number of rows affected. To display them as a formatted HTML table, use the following function.

// execute some statements...

if ($debug) {
  echo PGV_DB::getQueryLog();
}

Quoting/Escaping

Sometimes it is necessary to generate SQL containing escaped data, rather than using placeholders. Examples are where fragments of SQL are created separately. To escape data, use the quote() function.

$sql="SELECT foo FROM bar WHERE col=".PGV_DB::quote("value");

Note that, unlike the Pear::DB function escapeSimple(), quote() adds the surrounding quotation marks.