In some previous articles (1, 2, and 3), we discussed fast ways to get data from the database in MODX Revolution, but what if you want to update the database? Suppose that you also want to bypass MODX to write a raw value directly to the database.
In this article, we’ll look at a fast way to set specific object fields using straight MySQL. In the following article, we’ll look at another way that’s more convenient but not quite as fast.
Why?
One reason for bypassing MODX is to speed things up, but it’s not a very good reason. The time advantage of using this method is pretty small compared to using xPDO’s set() and save().
A better reason is to bypass the processing that MODX does when you use set(). In a number of cases MODX converts the data sent in the set() method before it’s stored in the database (and converts it the other way when you call get() or toArray()). The date fields that you enter for the pub_date and unpub_date fields when editing a Resource are converted into Unix timestamps for storage in the database. The same is true of the createdon, publishedon, editedon, and deletedon fields.
Likewise, the extended field in the user profile, and the properties field for elements and resources is converted from a PHP array into a JSON string when you store it.
For example, suppose you want to write some modified snippet properties to the database. Typically, you would create an array and pass that array to set(). The set() method, seeing that it’s the properties field, will convert that array to a JSON string and store it in the properties field. But what if you already have the properties in the form of a JSON string? It’s silly and wasteful (though not very time-consuming) to convert them to an array just so that MODX can convert them back to the way they are now.
Similarly, when you already have a date field in the form of a unix timestamp, it’s wasteful to have to convert it to a human-readable-date, then have MODX call set() to convert it back to a timestamp.
Here’s a much better example that happened to me a while back. In this case, it was critical to write raw values to the database.
I was moving some users from MODX Evolution to MODX Revolution for a client (this was before the GoRevo extra existed.) The users’ passwords were in the form of a MD5 hash with no salt. If I used $user->set('password', $password), MODX would hash the already hashed password again, and it would never work. I needed to write the password to the password field in the database without going through the user object’s set() method.
The Method
Here’s a simple example that updates the modUser fields of the user with the ID 12:
$password = 'SomePassword';
$userId = 12;
/* Make it run in either MODX 2 or MODX 3 */
$prefix = $modx->getVersionData()['version'] >= 3
? 'MODX\Revolution\\'
: '';
$table = $modx->getTableName($prefix . 'modUser');
$stmt = $modx->prepare("UPDATE " . $table . " SET `password`='" .
$password . "', `hash_class`='hashing.modMD5', `cachepwd`='',
`salt`='' WHERE id=" . $userId);
/* For debugging */
echo $stmt->queryString;
$stmt->execute();
The echo statement above will display something like this (all on one line):
UPDATE `modx_users`
SET `password`='SomePassword',
`hash_class`='hashing.modMD5',
`cachepwd`='', `salt`=''
WHERE id=12
If we used set() for these variables, MODX would hash the password (which is already hashed), and might automatically create values for the salt and cachepwd fields. The user would not be able to log in. Using the code above, we’re able to write the raw values directly to the database.
How it Works
Since our code is ultimately using PDO to execute the query, we can’t just refer to the modUser object directly because PDO doesn’t know about that object. Instead, we need to get the name of the actual table the query will be operating on with $modx->getTableName().
Next, we need to prepare the query with $modx->prepare(), which just calls PDO->prepare(). Note that the prepare() function is actually in the xPDO class, but since $modx extends xPDO, it will work as long as you're inside MODX when the statement executes.
Finally, we execute the prepared statement with $stmt->execute. Because $stmt is an instance of the PDOStatement object, this calls that object’s execute() member (PDOStatement::execute()).
Error Checks
The code above will be fine for development, but before it’s installed on a production site, it’s a good idea to add some error checking to ensure a malformed query doesn’t throw an ugly error. We’ll make sure that prepare() was successful (it returns false on failure), and that $stmt is a valid instance of PDOStatement. We also want to take out the echo statement we used for development. The calls to $modx->log() will write error messages to the MODX Error Log.
We might also add a prefix so the getTableName() call doesn't trigger a deprecation error in MODX 3:
$password = 'SomePassword';
$userId = 12;
/* Make it run in either MODX 2 or MODX 3 */
$prefix = $modx->getVersionData()['version'] >= 3
? 'MODX\Revolution\\'
: '';
$table = $modx->getTableName($prefix . 'modUser');
$stmt = $modx->prepare("UPDATE " . $table . " SET `password`='" .
$password . "', `hash_class`='hashing.modMD5', `cachepwd`='',
`salt`='' WHERE id=" . $userId);
/* echo $stmt->queryString; */
if (is_object($stmt) && $stmt instanceof PDOStatement) {
if (! $stmt->execute()) {
$modx->log(modX::LOG_LEVEL_ERROR, "Error " . $stmt->errorCode() .
" executing statement: \n" . print_r($stmt->errorInfo(), true),
'', __METHOD__, __FILE__, __LINE__);
}
} else {
$modx->log(modX::LOG_LEVEL_ERROR, "Invalid PDOStatement", '', __METHOD__, __FILE__, __LINE__);
}
The error logging above is a little complex. It provides a fair amount of information about the error and where it occurred.
In many cases, you can get by with just two arguments, The first will be the “log level”, which is almost always modX::LOG_LEVEL_ERROR. The second is just a string containing the text of the error message (or a call to $modx->lexicon('key_of_the_error_msg')).
The third argument is the log target, which is usually sent empty, since MODX has set a default log target, which will be the MODX Error Log, unless someone has called $modx->setLogTarget() to change it (say, to another file).
The fourth argument is for including a general clue to where the error occurred. If it’s not empty, MODX will tack “in”, followed by the value onto the end of the error message. We’ve chosen to use the PHP “magic” constant __METHOD__ here, which will show the name of the method of function the error occurred in. We could also have used __CLASS__, __DIR__, __TRAIT__, or __NAMESPACE.
The fifth and sixth arguments are to identify the file, and the line where the error occurred, so we’ve used the “Magic” constants __FILE__ and __LINE__. PHP will automatically replace these with the filename and the line number where the error occurred.
MODX will always put the current date and time at the beginning of the error message. So our final error message in the code above will look something like this:
[2023-06-07 23:36:26] ERROR in (function name here) @ C:\xampp\htdocs\addons\assets\myfile.php : 24)
(Error Message Here)
In our example above, you could use these two calls to the log, just using the first two arguments to $modx->log():
if (is_object($stmt) && $stmt instanceof PDOStatement) {
if (! $stmt->execute()) {
$modx->log(modX::LOG_LEVEL_ERROR, "Error executing statement: " . $stmt->queryString);
}
} else {
$modx->log(modX::LOG_LEVEL_ERROR, "Invalid PDOStatement");
}
For an even briefer version, you can also do this:
if ($stmt && $stmt->execute()) {
/* (optional) Success message here */
} else {
/* Error message code here */
}
Many calls to $modx->log() just use the first two arguments, but in a large program with many files, the longer version can be very helpful, since it will provide the full path to the file and the line number where the error occurred.
In well-behaved MODX extras, the name of the extra is often added to the beginning of the error message, like this:
$modx->log(modX::LOG_LEVEL_ERROR, '[LogPageNotFound] ' . 'Error Message');
It’s frustrating to see an error message in the log, but have no idea what extra triggered it.
In my next article, we’ll look at a convenient way to write values directly to the database with xPDO using its fromArray() method.
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.