Custom Database Interactions

Ok ... I’ve been looking and looking and looking... would someone please help me find some info/instructions (simple examples would be wonderful) on how to connect to my own custom database through Revolution?

What I’m doing isn’t all that complicated (I think). In Evolution I have been using:

• $modx->db->escape(string);
• $modx->db->connect(host, database, user, pass);
• $modx->db->disconnect();
• $modx->db->query(sql);
• $modx->db->getRecordCount(result);
• $modx->db->getRow(result);

It appears as if there are plenty of resources to tell me how to use xpdo to pull data from the modx database, but, again, I need to access some custom databases. Any help getting me in the right direction would be much appreciated!

http://svn.modxcms.com/docs/display/revolution/Using+Custom+Database+Tables+in+your+3rd+Party+Components

Thanks for the quick response... I’ve been to that page, but I don’t think it’s really what I’m looking for...

To quote a couple of spots on the page:

...You’ve got some data that uses a table in your MODx database, but you want a way to use xPDO’s object model to access it....

table - This should point to the actual database table name, minus the prefix.

I have a whole separate database I’m trying to access, plus, these instructions seem like overkill for what I’m doing. Does Revolution have just some equivalents to the old modx->db functions I listed above?

Connecting to two separate databases is a bit more complex; you’ll need to use a new instantiation of xPDO, which is what the modX object extends.

MODx is built on the ORM xPDO:

http://svn.modxcms.com/docs/display/XPDO10/Home

It has much more features than the old DBAPI of Evo. It does require you though to build the schema as outlined in the tutorial I posted above.

You could also use a PDO instance directly to work with your separate database, or even the old mysql extension functions. If it’s custom tables within the MODx database, a separate connection is not required, and you can use the xPDO functions (which simply wrap/extend PDO) to query the database. Becoming familiar with PDO first though would be a good starting point:

http://www.php.net/pdo

The PDO functions worked like a charm! (and I’m up in running in a matter of minutes)! Thanks tons! ... and it looks like it is something I should have been using already anyway.

Ok. Dang. ... the database I’m pulling from is all UTF-8 using non-standard characters (mostly IPA characters). The database hasn’t been changed at all, and it was working with Evolution, but now in Revolution all of those non-standard characters are getting lost. If it helps to know, the rows containing the special characters are all set as binary.

I’m going to take a stab in the dark, and guess that the caching mechanism is storing these files in Western-Roman encoding instead of UTF-8 which is where the special characters are getting dropped. ... but I’m probably wrong.

Am I using PDO wrong? Did I miss some setting in MODx? Any tips?

Are you creating a separate PDO connection? If so, you need to specify the encoding after making the connection using SET CHARACTER SET or SET NAMES, especially if your MySQL configuration is running the default of latin1_swedish_ci. It should have nothing to do with the caching unless you are not using utf8 encoding with your MODx database/tables.

The problem was on my end (of course!)... I just now found the solution that you just provided - you have to specify the charset with PDO before pulling any data! (duh)