We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 24719
    • 194 Posts
    hi opengeek (et al), i’m nearly there. i’ve generated an xml schema for my table (i will have more, but for now i want to get xpdo working). now i’m having problems with the primary key. it isn’t being set.

    i’ve added the following to the xml schema:
    <field key="id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="pk" />
    


    and with debug turned on, i’m getting the following after trying to save the object:
    (DEBUG) No primary key value found for pk definition: id


    since this is the first table i’ve made, there are no relationships to other tables. ultimately it will be in a one->many relationship, but i’m going to have tables that are standalone so it’d be good to know what’s going on.

    i’m creating my object from an array of eform-posted data:
    $myUser = $xpdo->newObject('myUser');
    $myUser->fromArray($fields, '', true);
    


    i’ll keep trying, but help with this would be great.
    thanks.
      • 22303 MODX Staff
      • 10,725 Posts
      Wait, a primary key field typically needs to be an AUTO_INCREMENT column (just add generated="native" to the schema definition for the column), and you don’t specifically set a primary key on new records... am I missing something?
        • 24719
        • 194 Posts
        no, that’s the problem. i shouldn’t have to set it in the first place. it is set to auto_increment in the database.

        i’ve added in generated="native" but i’m still getting the same error:

        i’ve pulled out the sql query from the debug report:

        INSERT INTO `pfp`.`_landlord` (`title`, `first_name`, `last_name`, `email`, `addr1`, `addr2`, `addr3`, `city`, `county`, `country`, `post_code`, `home_phone`, `work_phone`, `mobile`, `fax`, `web_user_id`, `on_mailing_list`) VALUES('title', 'first_name', 'last_name', 'email', 'addr1', 'addr2', 'addr3', 'city', 'county', 'country', 'post_code', 'home_phone', 'work_phone', 'mobile', 'fax', 10, true) 
        


        i tried inserting this using phpmyadmin and had a few problems. it didn’t like
        INSERT INTO `pfp`.`_landlord`
        


        it said: Table ’pfp._landlord’ doesn’t exist
        note it’s now pfp DOT _landlord. i don’t think mysql likes concatenation like this.

        i changed it to
        INSERT INTO `pfp_landlord`
        


        and it worked. i haven’t tried tweaking the xPDO at all to fix this. have you found this problem (presumably not....)
          • 22303 MODX Staff
          • 10,725 Posts
          I’m still not clear on what you are trying to do or say here...what is the actual table name? what is the prefix? what is the column name? Can you post the table structure and the XML schema object for that table?
            • 24719
            • 194 Posts
            ok, what i’m trying to do is store values in the table `pfp_landlord` (schema attached). i’m creating a new Landlord object and.populating it from the eform `fields` array as follows, but i receive an error complaining about the primary key value. i think this error is coming about as a result of concatenating the table prefix (the package name) and the table name.

            <?php
            ... // pre-processing above
               // use xpdo to insert the user's data into the landlord table
                    include_once ($modx->config['base_path']. 'xpdo/xpdo.class.php');
                    $xpdo= new xPDO('mysql:host=localhost;dbname=pfp', "username", "password");
                    $xpdo->setPackage('pfp');
            $xpdo->setDebug(true);
            
                    // untaint the user's data
                    foreach($fields as $fldkey)
                    {
                        $fields[$fldkey] = $modx->db->escape($modx->stripTags($fields[$fldkey]));
                    }
            
                    // create and populate object
                    $landlord = $xpdo->newObject('Landlord');
                    $landlord->fromArray($fields, '', true);
            
                    // set binary mailing list field
                    if ($fields['on_mailing_list'] == 'yes')
                        $landlord->set('on_mailing_list', 1);
                    else
                        $landlord->set('on_mailing_list', 0);
            
                    $landlord->set('web_user_id', $key);            // $key has been set previously
                    $landlord->save();
            ... and finish off...
            ?>
            


            the table structure is:
            CREATE TABLE `pfp_landlord` (
              `id` int(10) unsigned NOT NULL auto_increment,
              `title` varchar(20) default NULL,
              `first_name` varchar(200) NOT NULL,
              `last_name` varchar(200) NOT NULL,
              `email` varchar(200) default NULL,
              `addr1` varchar(255) NOT NULL,
              `addr2` varchar(255) default NULL,
              `addr3` varchar(255) default NULL,
              `city` varchar(255) NOT NULL,
              `county` varchar(255) NOT NULL,
              `country` varchar(255) NOT NULL,
              `post_code` varchar(12) NOT NULL,
              `home_phone` varchar(30) default NULL,
              `work_phone` varchar(30) default NULL,
              `mobile` varchar(30) default NULL,
              `fax` varchar(30) default NULL,
              `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
              `web_user_id` int(10) unsigned NOT NULL,
              `on_mailing_list` binary(1) default 'F',
              PRIMARY KEY  (`id`),
              UNIQUE KEY `web_user_id` (`web_user_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Contains details on landlords.' AUTO_INCREMENT=2 ;
            
              • 22303 MODX Staff
              • 10,725 Posts
              A HA! You’ve just misunderstood the relationship of packages and table prefixes. These are completely different. Think of packages as a sort of namespace that isolates the physical PHP code. And table prefixes are for adding prefixes to the corresponding table names at run-time, which of course is a requirement when building applications like, say, MODx, with xPDO. You have no table prefix specified in your xPDO constructor call, so it’s looking for a table named _landlord in the database named pfp. If ’pfp_’ is your intended runtime prefix for the tables, then I recommend changing the name of the table in the schema to landlord, and simply add one more parameter to the constructor for xPDO...

              $xpdo= new xPDO('mysql:host=localhost;dbname=pfp', "username", "password", "pfp_");
                • 28042 ☆ A M B ☆
                • 24,524 Posts
                You need to keep a strict separation in mind between your php and your sql code. In php, the "." is the concatenation operator. It does not serve that function in sql (or any other language that I know of). Oracle uses ||, and I believe that MySQL uses + (although it can be set to use || ).
                  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
                  • 24719
                  • 194 Posts
                  excellent. thanks a lot. everything works now.
                    • 23491 ☆ A M B ☆
                    • 1,056 Posts
                    Nice thread, all. Some really good info here for xpdo newbie (myself) roadblocks. Thanks!
                      Mike Reid - www.pixelchutes.com
                      MODx Ambassador / Contributor
                      [Module] MultiMedia Manager / [Module] SiteSearch / [Snippet] DocPassword / [Plugin] EditArea / We support FoxyCart
                      ________________________________
                      Where every pixel matters.