We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 40541
    • 85 Posts
    Hi,

    I apologies in advance for asking something that is probably very straight forward but have hit a bit of a brick wall with it. I have used MIGXdb to create a package and used the reverse engineering script provided here http://rtfm.modx.com/display/revolution20/Reverse+Engineer+xPDO+Classes+from+Existing+Database+Table against my new DB. It seems to have gone well as all the classes and xml files seem to be there.

    I now wanted to be able to access the data. I have used the script below but it is not outputting anything, yet printing the $my_xpdo and $my_items varibles everything seems to be there.

    $my_xpdo= new xPDO('mysql:host=localhost;dbname=mydomainname','username','password','');
    if(!$my_xpdo->addPackage('ttdny','/home/sites/mydomain/public_html/core/components/ttdny/model/','ttdny_')) {
        return 'There was a problem adding your package!  Check the logs for more info!';
    }
    
    $my_items = $my_xpdo->getCollection('Towns');
    
    $output .= '<p>Total: '. count($TownID) . '</p>';
    
    foreach($my_items as $my_item) {
    
        $output .= '<p>Town: ' . $my_item->get('town');
        $output .= '<br />Area: ' . $my_item->get('area');
        $output .= '<br />Postcode: ' . $my_item->get('postcode') . '<br /></p>';
    }
    
    return $output;


    Taking a small extract from the $my_items varible you can see the classes listed below that shows the Towns table I'm interested in, so not sure why it is returning empty?


    [classMap] => Array
            (
                [xPDOObject] => Array
                    (
                        [0] => xPDOSimpleObject
                        [1] => AffiliateJoinSubcat
                        [2] => FlScheduler
                        [3] => FlgJoinListings
                        [4] => FlgLogs
                        [5] => Keywords
                        [6] => ListingCategories
                        [7] => ListingDates
                        [8] => ListingDetails
                        [9] => ListingHits
                        [10] => ListingHitsShort
                        [11] => ListingJoinFacility
                        [12] => ListingJoinImages
                        [13] => ListingJoinKeywords
                        [14] => Listingratings
                        [15] => Listings
                        [16] => Listingstatus
                        [17] => Listingtypes
                        [18] => Logs
                        [19] => NoticeboardForums
                        [20] => NoticeboardMessages
                        [21] => NoticeboardPosts
                        [22] => NoticeboardTopics
                        [23] => Postcodes
                        [24] => SiteContent
                        [25] => Subcategories
                        [26] => Sysconstraints
                        [27] => Syssegments
                        [28] => Towns
                        [29] => Transactions
                        [30] => TtdnyAccountActivity
                        [31] => TtdnyAffiliateJoin
                        [32] => TtdnyAffiliates
                        [33] => TtdnyAreas
                        [34] => TtdnyCategories
                        [35] => TtdnyFacilities
                        [36] => TtdnyFlGroups
                        [37] => UserJoinListings
                        [38] => Userimages
                        [39] => Users
                        [40] => Userscalendar
                        [41] => Userstatus
                        [42] => Usertypes
                    )
    
            )
    • When creating a separate xPDO instance, use setPackage() vs. addPackage() on the main package you want to work with.
        • 40541
        • 85 Posts
        Thanks very much for responding.

        Sorry if I am missing something - I changed the addPackage() to setPackage() and it made no difference?

        I am basically copying the code under 'Accessing your Data' on the 'http://rtfm.modx.com/display/revolution20/Reverse+Engineer+xPDO+Classes+from+Existing+Database+Table' the only thing that I haven't done yet is define the foreign key relationships. It uses addPackage() ?
          • 40541
          • 85 Posts
          Some more information that my help?

          Extract from the package scheme for towns under ttdny/model/scheme/tdny.mysql.schema.xml:

          	<object class="Towns" table="towns" extends="xPDOObject">
          		<field key="TownID" dbtype="int" precision="11" phptype="integer" null="true" />
          		<field key="Town" dbtype="varchar" precision="255" phptype="string" null="true" />
          		<field key="Area" dbtype="varchar" precision="255" phptype="string" null="true" />
          		<field key="Postcode" dbtype="varchar" precision="255" phptype="string" null="true" />
          	</object>


          Under ttdny/model/ttdny/towns.class.php just has the following

          class Towns extends xPDOObject {}


          Under ttdny/mode/ttdny/mysql/towns.class.php

          require_once (dirname(dirname(__FILE__)) . '/towns.class.php');
          class Towns_mysql extends Towns {}


          and under ttdny/mode/ttdny/mysql/towns.map.inc.php

          $xpdo_meta_map['Towns']= array (
            'package' => 'ttdny',
            'version' => '1.1',
            'table' => 'towns',
            'extends' => 'xPDOObject',
            'fields' => 
            array (
              'TownID' => NULL,
              'Town' => NULL,
              'Area' => NULL,
              'Postcode' => NULL,
            ),
            'fieldMeta' => 
            array (
              'TownID' => 
              array (
                'dbtype' => 'int',
                'precision' => '11',
                'phptype' => 'integer',
                'null' => true,
              ),
              'Town' => 
              array (
                'dbtype' => 'varchar',
                'precision' => '255',
                'phptype' => 'string',
                'null' => true,
              ),
              'Area' => 
              array (
                'dbtype' => 'varchar',
                'precision' => '255',
                'phptype' => 'string',
                'null' => true,
              ),
              'Postcode' => 
              array (
                'dbtype' => 'varchar',
                'precision' => '255',
                'phptype' => 'string',
                'null' => true,
              ),
            ),
          );
          


          Thanks in advance.
            • 40541
            • 85 Posts
            Ok, so I have played around with this loads now after reading as much as can around xPDO and various posts on the forum and am really close to resolving this.

            My Snippet now looks like the following:

            // Criteria for foreign Database
            $host = 'IPAddress';
            $username = 'username;
            $password = 'password';
            $dbname = 'databaseName';
            $port = 3306;
            $charset = 'utf-8';
             
            $dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
            $xpdo = new xPDO($dsn, $username, $password);
             
            // Test your connection
            echo $o = ($xpdo->connect()) ? 'Connected </br>' : 'Not Connected';
            
            $xpdo->addPackage('ttdny','/home/sites/domainName/public_html/core/components/ttdny/model/');
            $xpdo->setDebug(true);
            $xpdo->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML');
             
            $members = $xpdo->getCollection('Towns');
            echo 'Total: '.count($TownID);


            When I run this I get the following:

            Connected
            [2012-12-15 22:34:28] (DEBUG @ /index.php)

            Returning table class: Towns for class: Towns
            [2012-12-15 22:34:28] (DEBUG @ /index.php)

            Returning table name: `towns` for class: Towns
            [2012-12-15 22:34:28] (DEBUG @ /index.php)

            Returning ancestry for Towns: Array
            (
            [0] => Towns
            [1] => xPDOObject
            )
            [2012-12-15 22:34:28] (DEBUG @ /index.php)

            Returning ancestry for Towns: Array
            (
            [0] => xPDOObject
            )
            [2012-12-15 22:34:28] (DEBUG @ /index.php)

            Attempting to execute query using PDO statement object: SELECT `Towns`.`TownID` AS `Towns_TownID`, `Towns`.`Town` AS `Towns_Town`, `Towns`.`Area` AS `Towns_Area`, `Towns`.`Postcode` AS `Towns_Postcode` FROM `towns` AS `Towns` Array
            (
            )
            [2012-12-15 22:34:28] (ERROR @ /index.php)

            Error 42S02 executing statement:
            Array
            (
            [0] => 42S02
            [1] => 1146
            [2] => Table 'databaseName.towns' doesn't exist
            )
            Total: 0

            So I see that it is complaining about databaseName.towns not existing, which is true because it has a prefix of ttdny_ so I change a line of code as per below:

            $xpdo->addPackage('ttdny','/home/sites/thingstodonearyou.co.uk/public_html/core/components/ttdny/model/','ttdny_');


            I then get a 500 Server error

            I try taking out the prefix from the towns table and change the line of code back to how it was and I get the same error. Have also tried both examples using setPackage() instead of addPackage() - still the same problem.

            So I feel I am really close but just can not tell whats going wrong.

            NOTE: Just found out that I can get past the 500 Server Error by commenting out the debugging lines but the page just shows connected and does not echo the count of TownID.
            $xpdo->setDebug(true);
            $xpdo->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML')
            ;
            • You do not have a primary key defined for your tables since they all extend xPDOObject and not xPDOSimpleObject (which defines an id autoincrement primary key field). You need to go back into the schema and fix this and regenerate the classes.
                • 40541
                • 85 Posts
                Well I wouldn't like to admit how much time I spent on trying to get this to work, and probably got myself in a muddle doing it, but the flip side is I learnt a lot about it.

                It must of been the primary key feild in the schema as you advised as now things are working!

                My towns schema now looks like the following:

                <object class="Towns" table="towns" extends="xPDOObject">
                <field key="TownID" dbtype="int" precision="11" phptype="integer" null="false" default="0" index="pk" />
                <field key="Town" dbtype="varchar" precision="255" phptype="string" null="true" />
                <field key="Area" dbtype="varchar" precision="255" phptype="string" null="true" />
                <field key="Postcode" dbtype="varchar" precision="255" phptype="string" null="true" />

                <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
                <column key="TownID" length="" collation="A" null="false" />
                </index>
                </object>

                And my snippet looks like the following:

                <?php
                $host = 'IPAdress';
                $username = 'DBUsername';
                $password = 'DBPassword';
                $dbname = 'DBName';
                $port = 3306;
                $charset = 'utf-8';
                  
                $dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
                $xpdo = new xPDO($dsn, $username, $password);
                  
                echo $o = ($xpdo->connect()) ? 'Connected </br>' : 'Not Connected';
                 
                if (!$xpdo->addPackage('ttdny','/home/sites/domainname/public_html/core/components/ttdny/model/','ttdny_')) {
                    print 'There was a problem adding your package.';
                };
                
                $xpdo->setDebug(true);
                $xpdo->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML');
                
                $results = $xpdo->getCollection('Towns',array('TownID' => 27));
                //$results = $xpdo->getCollection('Towns',27));
                
                $output = '';
                
                if ($results) {
                    foreach($results as $result) {
                      $output .= "<p>Town: " . $result->get('Town');
                      $output .= '<br />Area: ' . $result->get('Area');
                      $output .= '<br />Postcode: ' . $result->get('Postcode') . '<br /></p>';
                  }
                }
                else {
                    return 'No items found.';
                }
                
                echo $output;
                
                


                Really appreciate the help, many thanks.