We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 44437
    • 74 Posts
    I'm trying to run mysql queries from an external database using xpdo. From what I have been reading, when you have successfully connect to the external DB you will now have to load it as a package.

    In order to load it as a package, I have to create classes & mysql schemas. I am trying to follow Bob's guide https://bobsguides.com/custom-db-tables.html
    but I cannot get pass the first step. I should mention that in this guide, it is assumed that you've imported your custom tables into the MODX database however as was mentioned, my tables are from another database.

    Also, in this database I have prefix my tables as 'modx_'.

    Here are my steps.

    • Copied the Code for CreateXpdoClasses from Bob's guide and create a Create_Xpdo_Classes.php in Modx directory (where assets,core,manager,etc are located).
    • Within the Create_Xpdo_Classes.php, I have successfully connect to the external database by including the config file and setting the database connection variables
    if (!defined('MODX_CORE_PATH')) {
         $outsideModx = true;
        /* put the path to your core in the next line to run
         * outside of MODX */
         
        define('MODX_CORE_PATH', 'the_path_to_my_core');
        define('MODX_CONFIG_KEY', 'config');
        include_once MODX_CORE_PATH . 'model/modx/modx.class.php';
        
        $host = 'hostname';
        $username = 'username ';
        $password = 'password';
        $dbname = 'my_db';
        $port = 3306;
        $table_prefix = 'modx_';
        
        $dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
        $modx = new xPDO($dsn, $username, $password);
        
        $modx->initialize('mgr');
        
    }


    • The php file crash at this line
    $modx->initialize('mgr');


    What am I suppose to do from here? What is the importance of this line?
      • 3749
      • 24,544 Posts
      I don't think the script will work with the data in a remote database (I could be wrong).

      The functions that create the class and map files for xPDO are part of MODX, so you need to instantiate MODX, which is what the code above is for.

      The paths and credentials in these lines have to be for the local MODX database:

          define('MODX_CORE_PATH', 'the_path_to_my_core');  // needs to be changed
          define('MODX_CONFIG_KEY', 'config');
          include_once MODX_CORE_PATH . 'model/modx/modx.class.php';
          $host = 'hostname'; // needs to be changed
          $username = 'username '; // needs to be changed
          $password = 'password'; // needs to be changed
          $dbname = 'my_db'; // needs to be changed
          $port = 3306; // needs to be changed
          $table_prefix = 'modx_';
           
          $dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset"; // needs to be changed 


      The ideal thing would be to export the tables from the remote DB with PhpMyAdmin and import them into your MODX db (I would rename them to have a unique prefix).

      Obviously, that won't work if the remote data is changing over time.

      In that case, it may work to import the export/import the data (or just the table structure) into the local MODX DB just for the purpose of creating the class and map files, and use the script as originally described. Then, I would rename the local tables while getting things working with the remote DB so you're sure they're not being used, and delete them once everything is working.

      I've never done this, so I don't know if it works to use local class and map files with a remote DB, but maybe others here can chime in with tips.




        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
        • 44195
        • 293 Posts
        Here's a script used to generate class files and tables in a foreign database for one of the projects I'm working on. Obviously, you'll need to change the db connection info and the paths.

        <?php
        
        $mtime = microtime();
        $mtime = explode(" ", $mtime);
        $mtime = $mtime[1] + $mtime[0];
        $tstart = $mtime;
        set_time_limit(0);
        
        /* define package name */
        define('PKG_NAME','cwDB');
        define('PKG_NAME_LOWER',strtolower(PKG_NAME));
        
        //Customize this line based on the location of your script
        include_once (dirname(dirname(dirname(dirname(__FILE__)))).'/core/xpdo/xpdo.class.php');
        
        $host = 'localhost';
        $username = 'homestead';
        $password = 'secret';
        $dbname = 'cwdb';
        $port = 3306;
        $charset = 'utf8';
        $tablePrefix = 'cw_';
        
        $dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
        $xpdo = new xPDO($dsn, $username, $password, $tablePrefix);
        $xpdo->setDebug(true);
        
        // Test your connection
        echo $o = ($xpdo->connect()) ? 'Connected' : 'Not Connected';
        echo '<pre>'; /* used for nice formatting of log messages */
        $manager= $xpdo->getManager();
        $generator= $manager->getGenerator();
        
        $generator->classTemplate= <<<EOD
        <?php
        /**
         * [+phpdoc-package+]
         */
        class [+class+] extends [+extends+] {}
        ?>
        EOD;
        $generator->platformTemplate= <<<EOD
        <?php
        /**
         * [+phpdoc-package+]
         */
        require_once (strtr(realpath(dirname(dirname(__FILE__))), '\\\\', '/') . '/[+class-lowercase+].class.php');
        class [+class+]_[+platform+] extends [+class+] {}
        ?>
        EOD;
        $generator->mapHeader= <<<EOD
        <?php
        /**
         * [+phpdoc-package+]
         */
        EOD;
        $xpdo->setPackage('cwdb', dirname(dirname(__FILE__)).'/core/components/cwdb/model/');
        
        $schema = dirname(dirname(__FILE__)).'/core/components/cwdb/model/schema/cwdb.mysql.schema.xml';
        $target = dirname(dirname(__FILE__)).'/core/components/cwdb/model/';
        $generator->parseSchema($schema,$target);
        
        $manager->createObjectContainer('UnconfirmedStudent');
        $manager->createObjectContainer('Student');
        $manager->createObjectContainer('StudentPayment');
        $manager->createObjectContainer('Subject');
        $manager->createObjectContainer('Level');
        $manager->createObjectContainer('Location');
        $manager->createObjectContainer('Enrolment');
        $manager->createObjectContainer('Lesson');
        $manager->createObjectContainer('Term');
        $manager->createObjectContainer('SEBarcode');
        
        
        $xpdo->log(xPDO::LOG_LEVEL_INFO, 'Done!');
        
        
        $mtime= microtime();
        $mtime= explode(" ", $mtime);
        $mtime= $mtime[1] + $mtime[0];
        $tend= $mtime;
        $totalTime= ($tend - $tstart);
        $totalTime= sprintf("%2.4f s", $totalTime);
        
        echo "\nExecution time: {$totalTime}\n";
        
        exit ();
        



        Obviously the above is just a script to connect to the foreign database generate the class files and build the db tables.
        Then to use the database, you would instantiate it in your main service class normally.
        For example here's most of my __construct() function: (roughly copy and pasted)

            public $modx = null;
            public $namespace = 'cwdb';
            public $foreignDB = null;
            public $database_type = 'mysql';
            public $database_connection_charset = 'utf8';
            public $dbase = 'cwdb';
            public $table_prefix = 'cw_';
        
            public function __construct(modX &$modx, array $options = array()) {
                $this->modx =& $modx;
                $this->namespace = $this->getOption('namespace', $options, 'cwdb');
                $this->database_server = 'localhost'; 
                $this->database_user = 'homestead'; 
                $this->database_password = 'secret'; 
        
                $database_dsn = $this->database_type.':host='.$this->database_server.';dbname='.$this->dbase.' charset='.$this->database_connection_charset;
                
                $options = array(
                    xPDO::OPT_HYDRATE_FIELDS => true,
                    xPDO::OPT_HYDRATE_RELATED_OBJECTS => true,
                    xPDO::OPT_HYDRATE_ADHOC_FIELDS => true
                );
                $this->foreignDB = new xPDO($database_dsn, $this->database_user, $this->database_password, $options);
        
                $package_path = $modx->getOption('cwdb.core_path').'model/';
                if ( !$this->foreignDB->addPackage('cwdb', $package_path, $this->table_prefix) ) {
                    return 'Can not load package';
                }
                return true;
        
            }
        


        NOTE: It's very important to include that $options array in your "new xPDO()" call. If you don't hydrate the fields in an external database then xPDO joins will not work!
          I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
          Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com
          • 3749
          • 24,544 Posts
          Great info. Thanks for posting that. smiley

          @random_noob: Note that the script you were trying to use creates the schema file for you based on the structure of the DB table. With Murray's script, you have to create your own schema file on your local machine.
            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
            • 44437
            • 74 Posts
            Hey guys,

            Thanks for the tips. I will explore both options.
              • 44437
              • 74 Posts
              Quote from: BobRay at Aug 21, 2017, 11:50 PM
              Great info. Thanks for posting that. smiley

              @random_noob: Note that the script you were trying to use creates the schema file for you based on the structure of the DB table. With Murray's script, you have to create your own schema file on your local machine.

              Hey, I'm seeing that. I am not familiar with creating a schema file. How should I go about this?
                • 44437
                • 74 Posts
                I have modified Murray's script and added the following in hope that it would create the schema.

                $createSchema = $xpdo->getOption('createSchema',
                    $scriptProperties,true);
                $createClasses = $xpdo->getOption('createClasses',
                    $scriptProperties,true);
                    
                $includeCustomTemplates = empty($includeCustomTemplates) ? false : $includeCustomTemplates;    
                    
                $myPackage = empty($myPackage)? 'mypackage' : $myPackage;
                $myPrefix = empty ($myPrefix)? 'modx_' : $myPrefix;
                $myTables = empty($myTables)? '' : $myTables;
                
                $sources = array(
                    'config' => MODX_CORE_PATH . 'config/config.inc.php',
                    'package' => MODX_CORE_PATH . 'components/' .
                        $myPackage . '/',
                    'model' => MODX_CORE_PATH. 'components/' . $myPackage .
                        '/model/',
                    'schema' => MODX_CORE_PATH . 'components/' .
                        $myPackage . '/schema/',
                );
                
                if (! file_exists($sources['package'])) {
                    mkdir($sources['package'],0777);
                }
                 
                if (! file_exists($sources['model'])) {
                    mkdir($sources['model'],0777);
                }
                if (! file_exists($sources['schema'])) {
                    mkdir($sources['schema'],0777);
                }
                
                $xpdo->setLogLevel(modX::LOG_LEVEL_INFO);
                $xpdo->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML');
                
                echo '<pre>'; /* used for nice formatting of log messages */
                $manager= $xpdo->getManager();
                $generator= $manager->getGenerator();
                
                if ($includeCustomTemplates) {
                    customTemplates($generator);
                }
                
                $file = $sources['schema'] . $myPackage .
                    '.mysql.schema.xml';
                    
                if ($createSchema) {
                    $xml= $generator->writeSchema($file,
                        $myPackage, '',$myPrefix,true,$myTables);
                 
                    if ($xml) {
                        $xpdo->log(modX::LOG_LEVEL_INFO,
                            'Schema file written to ' . $file);
                    } else {
                        $xpdo->log(modX::LOG_LEVEL_INFO,
                            'Error writing schema file');
                    }
                }
                 
                if ($createClasses) {
                    if ($generator->parseSchema($file, $sources['model'])){
                        $xpdo->log(modX::LOG_LEVEL_INFO,
                            'Schema file parsed -- Files written to ' .
                                $sources['model']);
                    } else {
                        $xpdo->log(modX::LOG_LEVEL_INFO,
                            'Error parsing schema file');
                    }
                }
                $xpdo->log(modX::LOG_LEVEL_INFO, 'FINISHED');    
                exit();
                 
                function customTemplates($generator) {
                $generator->classTemplate= <<<EOD
                <?php
                /**
                 * [+phpdoc-package+]
                 * [+phpdoc-subpackage+]
                 */
                class [[+class]] extends [[+extends]] {
                 
                }
                ?>
                EOD;
                $generator->platformTemplate= <<<EOD
                <?php
                /**
                 * [+phpdoc-package+]
                 * [+phpdoc-subpackage+]
                 */
                require_once (dirname(dirname(__FILE__)) .
                    '/[+class-lowercase+].class.php');
                class [+class+]_[+platform+] extends [+class+] {
                }
                ?>
                EOD;
                $generator->mapHeader = <<<EOD
                <?php
                /**
                 * [+phpdoc-package+]
                 * [+phpdoc-subpackage+]
                 */
                EOD;
                $xpdo->setPackage('mypackage', MODX_CORE_PATH.'/components/mypackage/model/');
                 
                $schema = MODX_CORE_PATH.'/components/mypackage/model/schema/mypackage.mysql.schema.xml';
                $target = MODX_CORE_PATH.'/components/mypackage/model/';
                $generator->parseSchema($schema,$target);
                 
                $manager->createObjectContainer('body_type');
                $manager->createObjectContainer('cylinders');
                $manager->createObjectContainer('drivetrain');
                $manager->createObjectContainer('engine_placement');
                $manager->createObjectContainer('engine_type');
                $manager->createObjectContainer('make');
                $manager->createObjectContainer('model');
                $manager->createObjectContainer('model_body');
                $manager->createObjectContainer('model_details');
                $manager->createObjectContainer('model_engine');
                $manager->createObjectContainer('model_name');
                $manager->createObjectContainer('model_performance');
                $manager->createObjectContainer('model_specs');
                 
                 
                $xpdo->log(xPDO::LOG_LEVEL_INFO, 'Done!');
                 
                 
                $mtime= microtime();
                $mtime= explode(" ", $mtime);
                $mtime= $mtime[1] + $mtime[0];
                $tend= $mtime;
                $totalTime= ($tend - $tstart);
                $totalTime= sprintf("%2.4f s", $totalTime);
                 
                echo "\nExecution time: {$totalTime}\n";
                 
                exit ();
                }   


                But I am getting when I run the script is "Connected!"
                  • 3749
                  • 24,544 Posts
                  How are you setting the value of $myPrefix, $myTables, and $myPackage?

                  If you're running this as a snippet, the $xpdo variable would conflict with the MODX $xpdo variable, so you'd have to change its name in the script.








                    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
                    • 44437
                    • 74 Posts
                    Quote from: BobRay at Aug 22, 2017, 10:47 PM
                    How are you setting the value of $myPrefix, $myTables, and $myPackage?

                    If you're running this as a snippet, the $xpdo variable would conflict with the MODX $xpdo variable, so you'd have to change its name in the script.


                    I'm running the script as a standard .php file. I have set $myPrefix, $myTables, and $myPackage to the following:

                    $myPackage = 'shyft2';
                    $myPrefix = 'modx_';
                    $myTables = '';

                    It isn't creating the schema though. It is just outputting "Connected!"
                      • 3749
                      • 24,544 Posts
                      It looks like you haven't named any tables to work on.
                        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