Bob's Blog

This is a quick tip. It isn't usually an issue with MODX tables, but if you create custom database tables in the MODX database, you may eventually need some code to determine whether a table exists or not.

I discovered a need for this test while creating the ClassExtender extra. ClassExtender allows users to extend either the modUser or the modResource class. ClassExtender will let you create the necessary class and map files by creating a schema for your custom table, or by creating the database table itself with the desired fields. If the user opts to create the table, I generate the schema file from the table, inject a little extra related object code into the schema, and then create the class and map files from the schema. If they select the schema option, I do it directly from the schema.

If user chooses to use a schema file, I want to make sure that file exists before proceeding. Similarly, if the user selects the table option, I want to make sure the database table exists before trying to use it to write the schema file. Checking to see whether the schema exists is easy with PHP's file_exists() function, but I had to dig a little to figure out how to check for the existence of the table. I thought I'd post it here for others who need it.

Here's a quick and easy way to do that in MODX:

$table = "ext_user_data";
$tableExists = gettype($modx->exec("SELECT count(*) FROM $table")) == 'integer';

if ($tableExists) {
    echo "YES";
} else {
    echo "NO";
}

The technique rests on the interesting fact that the $modx variable is also a database handle. This results from the fact that the modX class extends the xPDO class, which maintains a permanent connection to the MODX database.

Note that the $table variable must contain the full name of the actual table (including its prefix), not the class or package name. This technique will work regardless of whether the table is empty. As long as the table exists, the $tableExists variable will be true.

The code has been tested on a MySQL server — it should work on Microsoft's SQLSRV, but I haven't tested that.