MODX Revo XPDO querying another MODX Revo Database on same mysql-server with xpdo

72 Posts

Thor Reply #1, 12 years, 9 months ago

I have developed a site for users to log into front-end (called site-1). I have another site that some content editors can log in back-end and add resources to (site-2).

What I want is that site-1 can get the resources in the modResource table from site-2.

$uid = 'root';
$pwd = 'sqlpassword';

$dsn = 'mysql:host=localhost;dbname=site-2;port=3306;charset=utf8';

$xpdo = new xPDO($dsn,$uid,$pwd);

echo $o = ($xpdo->connect()) ? 'Connected' : 'Not Connected';

// Issue queries against the foreign database:
$results = $xpdo->query("SELECT id FROM modx_site_content");
$recordCount = $results->rowCount();
print $recordCount;

This code works from site-1 and I get the resources I want from site-2, but is there a way to use the syntax below to query site-2 from site-1? That would have made the querying faster for me to code and easier to understand. It would also be easier for me to make more complex queries to site-2 in the future.

$c = $xpdo->newQuery('modResource');
$c->setClassAlias('r');
$c->select(array(
	'r.id as pageid'
));
if ($c->prepare() && $c->stmt->execute()) {
    echo '<code>'.print_r($c->toSQL(), 1).'</code>';
    foreach ($c->stmt->fetchAll(PDO::FETCH_ASSOC) as $i => $row) {
        echo '<pre>'.print_r($row, 1).'</pre>';
    }
}

[ed. note: thorjarle last edited this post 12 years, 9 months ago.]

...I believed it was Friday...

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 12 years, 9 months ago

Could you make the connection on site-1 creating a different object?

$uid = 'root';
$pwd = 'sqlpassword';
$dsn = 'mysql:host=localhost;dbname=site-2;port=3306;charset=utf8';
$xpdo2 = new xPDO($dsn,$uid,$pwd);
echo $o = ($xpdo2->connect()) ? 'Connected' : 'Not Connected';

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

72 Posts

Thor Reply #3, 12 years, 9 months ago

I tried this but it didn't do anything more. this works:

$results = $xpdo2->query("SELECT id FROM modx_site_content");
$recordCount = $results->rowCount();
print $recordCount;

but this doesnt work:

$c = $xpdo2->newQuery('modResource');
$c->setClassAlias('r');
$c->select(array(
   'r.id as pageid'
));
if ($c->prepare() && $c->stmt->execute()) {
    echo '<code>'.print_r($c->toSQL(), 1).'</code>';
    foreach ($c->stmt->fetchAll(PDO::FETCH_ASSOC) as $i => $row) {
        echo '<pre>'.print_r($row, 1).'</pre>';
    }
}

This is what I want working

If the latter worked it would be easier to query on multiple tables and so on since I could use xPDO like I can on my other custom tables that is in site-1

...I believed it was Friday...

5,888 Posts

Bruno17 Reply #4, 12 years, 9 months ago

this is working for me:


$packageName = 'mypackage';

$packagepath = $this->modx->getOption('core_path') . 'components/'.$packageName.'/';
$modelpath = $packagepath.'model/';
include($packagepath .'config/config.inc.php');

$dsn = $database_type.':host='.$database_server.';dbname='.$dbase.';charset='.$database_connection_charset;
$options = 
    array (
                    xPDO::OPT_CACHE_KEY => 'default',
                    xPDO::OPT_CACHE_HANDLER => 'xPDOFileCache',
                    xPDO::OPT_HYDRATE_FIELDS => true,
                    xPDO::OPT_HYDRATE_RELATED_OBJECTS => true,
                    xPDO::OPT_HYDRATE_ADHOC_FIELDS => true,
                    xPDO::OPT_LOADER_CLASSES => array('modAccessibleObject'),
                    xPDO::OPT_VALIDATOR_CLASS => 'validation.modValidator',
                    xPDO::OPT_VALIDATE_ON_SAVE => true,
                    'cache_system_settings' => true,
                    'cache_system_settings_key' => 'system_settings'
   );
 
$this->xpdo = new xPDO($dsn,$database_user,$database_password,$options); 
$this->xpdo->addPackage($packageName,$modelpath,$prefix);

may be, you forgot to add the package with $xpdo->addPackage();

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!