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.

MODX logo

The Method

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

[code language=”php”]
$stmt = $modx->prepare("MySQL Statement");
$results = array();
if ($stmt->execute()) {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
[/code]

Example

Here’s a concrete example you can try (change 12 to the ID of an actual resource):

[code language=”php”]
$table = $this->modx->getTableName(‘modResource’);
$stmt = $modx->prepare("SELECT id, alias, publishedon FROM " . $table . " WHERE id = 12");
$results = array();
if ($stmt->execute()) {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
echo print_r($results, true);
[/code]

The code above will display something like this:

[code language=”php”]
Array
(
[0] => Array
(
[id] => 12
[alias] => login
[publishedon] => 1321758240
)

)
[/code]

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 move the code to a site with a different database prefix. 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, even if there’s only one result. It will be an array where each member is an array of ‘fieldname’ => ‘value’ pairs representing one row of the table.

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.

With the method described here, you’ll get a Unix timestamp for the date fields and a JSON string for any JSON fields. If you are getting a TV value, you’ll also get the raw, un-rendered value, which might be @INHERIT for example.

Walking

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

[code language=”php”]
foreach($results as $result) {
echo "<br />ID: " . $result[‘id’];
echo "<br />Alias: " . $result[‘alias’];
echo "<br />Published On: " . strftime(‘%A, %B %d %Y’, $result[‘publishedon’]);
}
[/code]

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

[code language=”html”]
ID: 12
alias: login
publishedon: Saturday, November 19 2011
[/code]


For more information on how to use MODX to create a web site, see my web site Bob’s Guides, or better yet, buy my book: MODX: The Official Guide.

Looking for quality MODX Web Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , | Posted under MODX, MODX | RSS 2.0

Author Spotlight

Bob Ray

Bob Ray is the author of MODX: The Official Guide and over 30 MODX add-on components. He hosts Bob's Guides, a source of valuable information for MODX users, and has been very active in the MODX Forums with over 19,000 posts.

Leave a Reply

Your email address will not be published. Required fields are marked *