Using @SELECT bindings to access other databases (besides the MODx database)

I’m building a site that has a lot of database storage going on, and it all lives in its own database, outside of the modx database (it’s in the same instance of MySQL, it’s just in a different database).

It would be really handy to use the SELECT binding to execute queries against that other database. Can that be done? Then I could use the formatting and widget stuff that is normally available to the SELECT bindings / template variables, e.g. to build a nicely formatted HTML table.

I can, of course, write my own PHP code to execute these queries, and I think I can write them so that I grab input from the template... e.g. "Query = ’SELECT something FROM my_database’ "

Thanks for any insight!

There is another way to do this if you don’t mind patching the MODx code, if you look in the file /manager/tmplvars/commands.inc.php on line 45(note this is 0.9.6.1 here) there is a case entry for the select statement, in function ProcessTVCommand in the replacementVars array add a line at the end something like :-

’MYDBNAME’ => ’Your database name’

You can then simply access this value in your SELECT bindings by using the [+MYDBNAME+] syntax.

Obviously you can extend this to add as many different databases as you like.

Thanks! This gives me something to try. I also found this in the wiki pages: http://wiki.modxcms.com/index.php/Access_another_database_from_MODx

I don’t want to add the tables to the modx database... I see that database as potentially volatile if I do any upgrades, but also from a security standpoint: the MODx app is granted the God user to that database. Any data in there could be read/altered/deleted in ways I didn’t intend. For another form I made for interacting with my other database, I just had a Snippet that used an include(’../lib/my_non-modx_code.php’) to handle interactions with this other database. It’s a bit weird when you start using MODx for *some* but not all of your code... I’ve run into that with CSS files, too... do you create a document for them and [~ref~] them? Or do you put them in the assets folder? I suppose it’s a matter of preference.

I’m hesitant to modify the source code for the same upgrade reasons as indicated before, but this sounds like a worthy trade-off... I wonder if I can get in there to mitigate the "God User" effect? E.g. if I’m only SELECTING data, can I point MODx to use a MySQL user that only has SELECT privileges (and thus reduce the risk of bad things happening due to SQL injection / XSS)?

I’m also guessing that I may have to add a user by the same name/password to my other database, but that wiki link had a way of handling that... it just may not work within the context of the @SELECT binding.

Thanks for all the replies!

Yes, if you add a user to your other database that uses the same username/password as used in your main MODx installation this will work in the SELECT binding as this info(and the table prefix if not otherwise specified by [+PREFIX+]) is picked up from your config.inc file. You can the limit this use to only select privileges.