Issues connecting to external DB

78 Posts

rjohnson375 Reply #1, 9 years ago

I almost had my site finished, but now they want to use an outside database server, and I am running into issues getting my data to display. I am trying to follow the guide on BobRay's site, but I think I am missing something.

At the top of my snippet I have the following code:

<?php
$path = MODX_CORE_PATH . 'components/webservice/';

$database_user = 'mel';
$database_password = '12345';
$table_prefix = 'LuggageCombo_';
$xpdo = new xPDO('mysql:host=localhost;dbname=webservice',$database_user,$database_password,$table_prefix);

$result = $modx->addPackage('webservice',$path .
    'model/','LuggageCombo_');

if (! $result) {
    return 'failed to add package';
} else {

$query = $xpdo->newQuery('TableSuffix');
$query->where(array(
  SortID => 1
));

$packages = $xpdo->getCollection('TableSuffix', $query);

foreach($packages as $package) {
    $fields = $package->toArray();
    $output .= $modx->getChunk('packages_chunk', $fields);
};
return $output;
}

EDIT: I ran the CreateXpdoClasses snippet from the tutorial, and I noticed that it is not creating a schema with my new table suffixes (I am using the same table prefix). Also, it is creating models with the old suffix. For example, when I run the CreateXpdoClasses snippet the output in my components folder is:

model/name/tm.class.php
schema/name.mysql.schema.xml (in here it is displaying all my old table information)

it should be:

model/name/tmpa.class.php
schema/name.mysql.schema.xml (with updated table information)

Is there anything extra I have to do that I am missing from http://bobsguides.com/custom-db-tables.html?

This question has been answered by rjohnson375. See the first response.

[ed. note: rjohnson375 last edited this post 9 years ago.]

24,544 Posts

BobRay Reply #2, 9 years ago

The stuff a Bob's Guides is mainly for creating custom tables *within* the MODX database. Is it possible for you to move the tables into the MODX DB? That would make life a lot easier.

If not, there's some useful info here: http://rtfm.modx.com/xpdo/2.x/getting-started/using-your-xpdo-model/database-connections-and-xpdo

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

78 Posts

rjohnson375 Reply #3, 9 years ago

I really wish we could keep them inside the MODX database, it was working great there. The issue is that we have a staging server, and we need to have 6 tables outside of our database that will be updated every morning to push to the live server without pushing the entire MODX site (in case we have content changes that aren't ready to go live yet). I was able to output an array of my entire table, but I would like to use something similar to your demo with getCollection and getChunk to output my data so that it is readable. Currently I'm stuck at that point.

discuss.answer

78 Posts

rjohnson375 Reply #4, 9 years ago

Thank you BobRay, that link sent me down a trail that helped me solve my problem. I can't explain why what I did works, but I got it working. For anyone who stumbles upon this post, my basic issue was that I needed to pull data from an external database and use it to populate values in a chunk using a snippet. Some of these steps may be unnecessary, and I'm sure there is a better way to do it, but this is what I did:

First, I went to http://bobsguides.com/custom-db-tables.html, and used the CreateXpdoClasses snippet from the example. That created a folder in my core/components directory with a model and database schema, I used this as a placeholder for my real data.

Then, I created another snippet with the following code called CreateXpdoWebservice:

<?php
define('MODX_CORE_PATH', '/var/www/whatever-your-path-is/core/');
define('MODX_CONFIG_KEY','config');
require_once MODX_CORE_PATH . 'model/modx/modx.class.php';

// Criteria for foreign Database
$host = 'localhost';
$username = 'mel';
$password = '12345';
$dbname = 'LugageCombo';
$port = 3306;
$charset = 'utf-8';
 
$dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
$xpdo = new xPDO($dsn, $username, $password);

$manager= $xpdo->getManager();
$generator= $manager->getGenerator();
 
$xml= $generator->writeSchema('/var/www/whatever-your-path-is/core/components/webservices/schema/webservices.mysql.schema.xml','webservices', 'xPDOObject','TABLEPREFIX_');

$xpdo->setLogLevel(xPDO::LOG_LEVEL_INFO);
$xpdo->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML');

$schema = '/var/www/whatever-your-path-is/core/components/webservices/schema/webservices.mysql.schema.xml';
$target = '/var/www/whatever-your-path-is/core/components/webservices/model/';
$generator->parseSchema($schema,$target);

When I ran that from a new resource, it populated my schema and model folders with the information from my external database.

Then, for the snippet I wanted to populate the chunks on my page I used the following code:

<?php
define('MODX_CORE_PATH', '/var/www/whatever-your-path-is/core/');
define('MODX_CONFIG_KEY','config');
require_once MODX_CORE_PATH . 'model/modx/modx.class.php';

// Criteria for foreign Database
$host = 'localhost';
$username = 'mel';
$password = '12345';
$dbname = 'LugageCombo';
$port = 3306;
$charset = 'utf-8';
 
$dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
$xpdo = new xPDO($dsn, $username, $password);

$path = MODX_CORE_PATH . 'components/webservices/';

$result = $modx->addPackage('webservices',$path .
    'model/','TABLEPREFIX_');

if (! $result) {
    return 'failed to add package';
} else {
 
switch ($filter) {
    
    case "some-criteria":
    
    $rows = array();
    $stmt = $xpdo->query("SELECT * FROM TABLEPREFIX_TABLESUFFIX WHERE Whatever LIKE 'some-value'");
    if ($stmt) {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            array_push($rows, $row);
            $output .= $modx->getChunk('chunk_to_output_data_formatted', $row);
        }
    }
    return $output;
    break;
    }

}

My snippet code on the final landing page looks like [[snippetName? &filter=`some-criteria`]]

I'm sure there are a dozen better ways to do this, and some of my steps or code may not be necessary, but this is what worked for me.

24,544 Posts

BobRay Reply #5, 9 years ago

Looks good. I think I would use a different name for the xPDO variable since MODX uses that variable name more-or-less globally. It may not be necessary, but I think not doing so could affect plugins that might run during that request.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

78 Posts

rjohnson375 Reply #6, 9 years ago

Quote from: BobRay at Apr 10, 2015, 05:14 PM

Looks good. I think I would use a different name for the xPDO variable since MODX uses that variable name more-or-less globally. It may not be necessary, but I think not doing so could affect plugins that might run during that request.

Do you mean change:

$xpdo = new xPDO($dsn, $username, $password);
.
.
$stmt = $xpdo->query("SELECT * FROM TABLEPREFIX_TABLESUFFIX WHERE Whatever LIKE 'some-value'");

to something like:

$dbconnection = new xPDO($dsn, $username, $password);
.
.
$stmt = $dbconnection->query("SELECT * FROM TABLEPREFIX_TABLESUFFIX WHERE Whatever LIKE 'some-value'");

Thank you very much.

24,544 Posts

BobRay Reply #7, 9 years ago

Yes, exactly.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

Answered Issues connecting to external DB