Using MySQL for MODX Queries

An easy way to use straight MySQL queries to get information from the database in MODX.

By Bob Ray  |  September 6, 2023  |  4 min read
Community MODX CMS Tutorials
Using MySQL for MODX Queries

The xPDO methods built into MODX Revolution are tremendously convenient and intuitive once you get used to them. Sometimes, though, you want to bypass xPDO either for increased speed, or because you want to do something quickly, know the appropriate MySQL, and can’t spare the time to figure out how to do it the “xPDO way”.

In this article, we’ll look at a very fast way to get the results of a MySQL query as a PHP associative array.

The Method

This is the basic form for using MySQL in MODX Revolution:

$stmt = $modx->prepare("MySQL Statement");
$results = array();
if ($stmt->execute()) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

In the code above, the $results variable will hold a regular PHP associative array with the field names on the left and the values on the right.

Example

Here’s a concrete example you can try (change 12 to the ID of an actual Resource). The Resource will not be modified:

$prefix = $modx->getVersionData()['version'] >= 3
    ? 'MODX\Revolution\\'
    : '';

$table = $this->modx->getTableName($prefix . 'modResource');
$stmt = $modx->prepare("SELECT id, alias, publishedon FROM " . $table . " WHERE id = 12");
$results = array();
if ($stmt->execute()) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
return print_r($results, true);

The code above will display something like this:

Array
(
    [0] => Array
        (
            [id] => 12
            [alias] => login
            [publishedon] => 1321758240
        )

)

Considerations

There are several things to be aware of here. First, you need to remember to use getTableName() to get the name of the table, because MySQL doesn’t know anything about the MODX objects—you need the actual name of the table you want to query. In our example, it’s the modx_site_content table, which holds the sites Resources.

If your code won’t be distributed to other MODX sites, you can hard-code the actual table name in the statement, but it’s a good practice to use getTableName(). Otherwise, you might be in for a nasty surprise and some frustration if you decide to change the table prefix of the database. You can find out the table name for a given MODX object here, but remember to change the table prefix if your site uses a different prefix than modx_.

Second, the results will be in a nested array like the one above, even if there’s only one result. The array above has only one inner member, but if you retrieve more than one record, there will be multiple inner arrays. Each member of the inner array will have the field name on the left and its value on the right.

Finally, you need to remember that you will be getting the raw field values because the data is not being passed through the xPDO get() method. In the output above, you can see that the publishedon value is a Unix timestamp rather than a formatted date. The get() method converts date fields to human-readable form. It also converts JSON fields like the modUser object's extended field and the properties field for Resources and Elements into PHP arrays. Since we’re bypassing that process, we’ll always get the raw values you would see if you looked in the database.

With the method described here, you’ll get a Unix timestamp for the date fields and a JSON string for any JSON fields. You will also get no TVs this way. It’s possible to add them in, but it’s fairly complicated because the TV objects and the TV values are stored in separate tables in the database.

The technique will work on any MODX object store in the database.

Walking

Here’s an example showing how to walk through the $results array and convert date fields to a human-readable form:

foreach($results as $result) {
    $output .= "<br />ID: " . $result['id'];
    $output .= "<br />Alias: " . $result['alias'];
    $output .= "<br />Published On: " . strftime('%A, %B %d %Y',
        $result['publishedon']);
}

The output of that code for one Resource would look something like this:

ID: 12
alias: login
publishedon: Saturday, November 19 2011

Multiple Objects

The code doesn’t change when you retrieve multiple objects—there are just more elements in the out array. This query would retrieve multiple Resources, assuming that you have more than one Resource with an alias that starts with the letter “a”.

$stmt = $modx->prepare("SELECT id,alias,publishedon FROM " . $table . " WHERE alias LIKE 'a%'");

$results = array();
if ($stmt->execute()) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $output = "<br>" print_r($results, true);
} else {
    $output = "<br>Query Failed";
}
return $output;

Troubleshooting

If you’re having trouble displaying the results. Try using something like the print_r line in the code above to see what’s been returned.

To see the final SQL query being issued, add this line after the $stmt variable is set:

$output .= "<br>" .  $stmt->toSql() . "<br>";

If you’re getting no results, or an empty array, try your query on the SQL tab of the relevant table in phpMyAdmin. Use the version displayed by toSQL() in the code above.

Bob Ray is the author of the MODX: The Official Guide and dozens of MODX Extras including QuickEmail, NewsPublisher, SiteCheck, GoRevo, Personalize, EZfaq, MyComponent and many more. His website is Bob’s Guides. It not only includes a plethora of MODX tutorials but there are some really great bread recipes there, as well.