Using PDO with MODx

Often developing custom applications for MODx requires some kind of database access, whether this it to data in the MODx tables or for custom tables, and even occasionally to a custom database not associated with the MODx installation's database. While MODx offers a database abstraction class with its own DB API, for those who have the appropriate version of PHP the PDO class offers a much better abstraction and API.

There are any number of tutorials online on how to use PDO; a quick Google search will turn up pages and pages of them. But the big question for me was, since MODx already has a database connection opened before your snippet or plugin is processed, how nicely will PDO play with MODx?

It turns out, just fine.

While this is not intended to be a PDO tutorial, I'll go through a simple tutorial excercise to demonstrate how PDO and MODx can work together. Here's my snippet code.

// configuration
$dbtype =>   "mysql";
$dbhost =>   $modx->db->config['host'];
$dbname =>   trim('`',$modx->db->;config['dbase']);
$dbuser =>   $modx->db->config['user'];
$dbpass =>   $modx->db->config['pass'];
// switching
switch($dbtype){
  case "mysql":
    $dbconn = "mysql:host=$dbhost;dbname=$dbname";
    break;
  case "sqlite":
    $dbconn = "sqlite:$dbpath";
	break;
  case "postgresql":
    $dbconn = "pgsql:host=$host dbname=$db";
	break;
}
// database connection
try{
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));
}
catch(PDOException $pe)
{
  die('Connection error, because: ' .$pe->getMessage());
}
// build and execute queries
echo "
";
echo $conn->getAttribute(PDO::ATTR_DRIVER_NAME);
echo "
";
echo $conn->getAttribute(PDO::ATTR_CLIENT_VERSION);
echo "
";
echo $conn->getAttribute(PDO::ATTR_SERVER_VERSION);
echo "

";
// can MODx itself still access the database?
$tvPr = $modx->getTemplateVarOutput(array("loginName"),$docid='1',$published=1);
echo $tvPr['loginName'];
return;

Let's go over it one section at a time. To start up a PDO instance, you need to establish a new instance of the PDO object establishing a new database connection. If you're accessing the MODx installation's database, you can use the $modx->db->config array to get the database connection information. These are the values available; they are taken from the config.inc.php file.

[host] => localhost
[dbase] => `testing5`
[user] => root
[pass] => root
[charset] => utf8
[connection_method] => SET CHARACTER SET
[table_prefix] => modx_

From these db configuration settings, we can establish the settings for our PDO instance. Note that you'll need to get rid of the backticks surrounding the database name.

// configuration
$dbtype =>   "mysql";
$dbhost =>   $modx->db->config['host'];
$dbname =>   trim('`',$modx->db->;config['dbase']);
$dbuser =>   $modx->db->config['user'];
$dbpass =>   $modx->db->config['pass'];

A simple switch statement can make your code more flexible. You can add cases for as many database types as you like; you only need to change the one configuration line, "$dbtype => ..." to make your application work on any database you please.

// switching
switch($dbtype){
  case "mysql":
    $dbconn = "mysql:host=$dbhost;dbname=$dbname";
    break;
  case "sqlite":
    $dbconn = "sqlite:$dbpath";
	break;
  case "postgresql":
    $dbconn = "pgsql:host=$host dbname=$db";
	break;
}

Once the configuration settings are established, whether from the MODx settings or as needed for accessing your own database, the PDO object is initialized or instantiated or whatever you prefer to call it.

// database connection
try{
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));
}
catch(PDOException $pe)
{
  die('Connection error, because: ' .$pe->getMessage());
}

Hopefully the connection attempt was successful. Just for a simple check to make sure it was, I've used some built-in PDO constant values to check the database configuration.

// build and execute queries
echo "
";
echo $conn->getAttribute(PDO::ATTR_DRIVER_NAME);
echo "
";
echo $conn->getAttribute(PDO::ATTR_CLIENT_VERSION);
echo "
";
echo $conn->getAttribute(PDO::ATTR_SERVER_VERSION);
echo "

";

This should produce something like this, with the database type and your server and client versions.:

mysql
5.1.44
5.1.44

And now came the moment of truth. With this PDO instance making its own connection, and a persistant one at that, can MODx still access the database? A quick API function to fetch the value of a given TV will put that to the test.

// can MODx itself still access the database?
$tvPr = $modx->getTemplateVarOutput(array("loginName"),$docid='1',$published=1);
echo $tvPr['loginName'];

And it did in fact work as hoped; I got "Login" displayed on the screen. The loginName TV is installed as part of the MODx demo site installation; I used it simply because I happened to know it was available.

So now I know that PDO can work comfortably with MODx, with snippets ducking in and out of making PDO queries and using shortcut MODx API functions. The next test will be to see how this sort of thing impacts performance; I'll be testing a standard MODx DBAPI insert functions in a loop for processing form input, and PDO prepared statements to do the same thing. Then I'll be playing with stored procedures. I'll get back and let you know how it turns out.

sottwell.com

Using PDO with MODx

Articles