Best way to update a linked record on form submit?

cchiacchierini · Sep 16, 2014

I have a subscribers table with multiple rows per each joomla user (one for each subscription purchased). When updating contact information (address, phone and email) in one record in that table, I would like to update that same contact information in all of the related records for that Joomla user in the same table. I think a php form plugin is the way to go a la http://fabrikar.com/forums/index.php?threads/anatomy-of-a-basic-phptable-plugin.20094/, but I am curious if the upsert plugin might be a better option.

If it is the php plugin, I could use a bit of help on where to start, as I am a novice in this area. While the example in the post above is helpful, I am not sure it covers how to update multiple fields in multiple rows with matching userids.

Thanks.

J3.3.3
F2.2

cheesegrits · Sep 16, 2014

Ideally, you should normalize your tables, so the contact data for the users, so you aren't storing it in the 'subscribers' table, but in a separate 'subscribers_contacts' table, which you join to 'subscribers'. But it's hard to change these things once an app has data, so ...

I'm pretty sure the upsert plugin only handles updating/inserting single rows.

So yeah, a PHP plugin would be your best bet. But the one you referenced is obselete, from an old version of Fabrik. What you would want would be the PHP form submission plugin, set to run 'onAfterProcess', and do something like this:

PHP:

$userid = (int) $formModel->getElementData('userid', true);
if (!empty($userid)) {
  $addr1 = $formModel->getElementData('addr1');
  $addr2 = $formModel->getElementData('addr2');
  // etc etc to grab all your address data
  $db = FabrikHelper::getDbo();
  $query = $db->getQuery(true);
  $query->update('contacts')
    ->set('addr1 = ' . $db->quote($addr1))
    ->set('addr2 = ' . $db->quote($addr2))
    ->where('userid = ' . $db->quote($userid));
  $db->setQuery($query);
  echo $query;exit;
  //$db->execute();
}

You may or may not have to prepend subscriptions___ (or whatever your table name is) so you are using full element names in those getElementData() calls, like $formModel->getElementData('subscriptions___userid', true). And of course you'll need to modify that with your actual table / element names, add getElementData() and ->set() lines for each field of contact data.

NOTE the 'echo' as the second to last line, and the commenting out of the execute(). As this is a potentially destructive operation, its always best to sanity test it before actually running it. That line should abort the actuall execution, and you'll just see the actual query echoed in your page. Sanity check it, make sure it looks right, then either remove (or somment out with //) the 'echo' line, and uncomment (remove the //) on the execute() and re-submit your form.

You might also want to post your PHP here for us to sanity check before running it at all.

-- hugh

cchiacchierini · Sep 17, 2014

Here is the code used by the third party app that I am trying to replicate the updating behavior of via the Fabrik interface:

PHP:

public static function syncronizeProfileData($row, $data)
    {
        $db = JFactory::getDbo();
        $sql = 'SELECT id FROM #__osmembership_subscribers WHERE profile_id='.$row->profile_id.' AND id != '.$row->id;
        $db->setQuery($sql);
        $subscriptionIds = $db->loadColumn();
        if (count($subscriptionIds))
        {
            $rowFields = OSMembershipHelper::getProfileFields(0, false);
            $form = new RADForm($rowFields);
            $form->storeData($row->id, $data);           
            //Store core fields data
            $sql = 'SELECT name FROM #__osmembership_fields WHERE is_core=1 AND published = 1';
            $db->setQuery($sql);
            $coreFields = $db->loadColumn();
            $coreFieldData = array();
            foreach ($coreFields as $field)
            {
                if (isset($data[$field]))
                {
                    $coreFieldData[$field] = $data[$field];
                }
                else
                {
                    $coreFieldData[$field] = '';
                }
            }
            foreach ($subscriptionIds as $subscriptionId)
            {
                $rowSubscription = JTable::getInstance('OsMembership', 'Subscriber');
                $rowSubscription->load($subscriptionId);
                $rowSubscription->bind($coreFieldData);
                $rowSubscription->store();
                $form->storeData($subscriptionId, $data);
            }
        }   
    }

See any problems right off the bat?

cheesegrits · Sep 18, 2014

Looks like you could probably run that as-is, if you set up $row yourself as an object with the correct profile_id and id, and just passed it $formModel->formData as the $data.

But we really are straying in to Pro subscription territory here. Anything involving more than a small handful of lines of custom code consumes more of our time than a Standard sub covers.

-- hugh

cchiacchierini · Sep 18, 2014

Got it. Should I message you re: paying for the custom coding help?

cheesegrits · Sep 18, 2014

Well, you pretty much have everything you need in this thread. I don't mind having a quick stab at helping you get that function going, I'm just letting you know we're kind of surfing the limits of Standard. If you wanted to take out a Pro sub for a month, that'd be great. I don't usually get this crass and commercial, but the last few months have been difficult, so I'm having to shake the trees a little to get some extra "liquidity" while we work through the hump of my having been out of action in surgery for a while, etc. To put it bluntly, I'm broke.

Anyway ... the main difference between what I posted, and that function, is that function looks in another table, osmembershi_fields, to get the (address) field names it then updates in the osmembership_subscribers table. Which I assume means it has some kind of custom field deal on the backend, that lets you define extra fields to use on the basic subscriber profile. Whereas the code I gave you is much simpler, and just relies on "hard wired" knowledge of the fields you want to update.

So the issue is really, are you moving away from using that third party app, or trying to integrate with it? if you are trying to integrate with it, then using that function makes sense. If you are moving away from using it, then you can just use the code I gave you, tweaked with the field names you want to update.

-- hugh

cchiacchierini · Sep 18, 2014

No worries; I just upgraded to pro for a month. Can you tell me if my subscription will revert to standard after that? Also, does this post need to be moved to the other forum, or is it good here?

To answer your issue question, I am trying to integrate Fabrik with this third party subscription component, basically using the Fabrik interface to create a CRM GUI to work with the data already collected via the subscription software along with related data from other native Joomla and custom fabrik tables. I am almost there except for this odd little behavior where every time a user resubscribes or creates a new subscription in the third party subscription software, the contact info they enter updates the data in all of their related subscriber records in the third party subscription software's subscriber table (if they have three subscriptions, the contact info is updated on all three). B/C I want to manage the CRM data from one interface, including the contact info from the third party component subscriber table via Fabrik, I want to recreate this behavior when editing/updating contact data via the Fabrik interface.

The code above is straight from the third party subsciber component. I need help plugging it in correctly (without breaking everything else).

cheesegrits · Sep 19, 2014

Moved to Pro forum.

So, just to be sure ... the submisison plugin will be running when you submit a change to the #__osmembership_subscribers list/form?

Can you tell me if that function you pasted is in the same file as a function called getProfileFields()?

If not, can you locate that function, and paste it here? It'll probably be in a file in ./components/com_osmembership/helpers, or whatever the extension is called.

Although it would be nice to run that sync function directly, by just including their file(s), and it might be OK, it would be duplicating effort ... that function does it's own update of the row we would have just updated via Fabrik, with those RADForm / storeData() lines. Which wouldn't be awful, it would just mean the data got written out twice. So it kind of depends how much of the component we'd need to instantiate to get that going, and how easy . hard that would be.

So it might be easier to just copy the two functions (this one, and the getProfileFields ones), comment out the RADForm save, and use our own verison of that code.

Just FYI, I tried ftp'ing to the site as per youy My Sites so I could take a look for the code, but I get:

Command:USER xxxxxxxxxxx
Response:331 User xxxxxxxxxx OK. Password required
Command

ASS ***************
Response:530 Login authentication failed
Error:Critical error
Error:Could not connect to server

-- hugh

cchiacchierini · Sep 19, 2014

Sorry about the error on logging into my sites. That was an old site. I updated the FTP info.

Not sure how to answer the question about the submission plugin running. I plan to run the php plugin. Everything else in the form will be default, I think.

"getProfileFields()" is not in the helper.php where I pulled the other code from; however, there is related code (pasted below). I also uploaded the helper.php as a .txt file if that is helpful.

PHP:

public static function getProfileFields($planId, $loadCoreFields = true, $language = null)
    {
        $planId = (int) $planId;
        $db = JFactory::getDbo();
        $fieldSuffix = self::getFieldSuffix($language);
        $sql = 'SELECT *, title' . $fieldSuffix . ' AS title, description' . $fieldSuffix . ' AS description, `values' . $fieldSuffix .
            '` AS `values`, default_values' . $fieldSuffix . ' AS default_values, fee_values' . $fieldSuffix .
            ' AS fee_value FROM #__osmembership_fields WHERE published=1 AND (plan_id=0 OR id IN (SELECT field_id FROM #__osmembership_field_plan WHERE plan_id=' .
            $planId . ')) '.(!$loadCoreFields ? ' AND is_core=0 ': '').' ORDER BY ordering';
        $db->setQuery($sql);       
        return $db->loadObjectList();
    }

PHP:

public static function getProfileData($rowProfile, $planId, $rowFields)
    {
        $db = JFactory::getDbo();
        $data = array();   
        $sql = 'SELECT a.name, b.field_value FROM #__osmembership_fields AS a INNER JOIN #__osmembership_field_value AS b ON a.id = b.field_id' .
            ' WHERE b.subscriber_id=' . $rowProfile->id;
        $db->setQuery($sql);
        $fieldValues = $db->loadObjectList('name');                       
        for ($i = 0, $n = count($rowFields); $i < $n; $i++)
        {
            $rowField = $rowFields[$i];
            if ($rowField->is_core)
            {
                $data[$rowField->name] = $rowProfile->{$rowField->name};
            }
            else
            {
                if (isset($fieldValues[$rowField->name]))
                {
                    $data[$rowField->name] = $fieldValues[$rowField->name]->field_value;
                }
            }
        }
       
        return $data;
    }
       
    public static function syncronizeProfileData($row, $data)
    {
        $db = JFactory::getDbo();
        $sql = 'SELECT id FROM #__osmembership_subscribers WHERE profile_id='.$row->profile_id.' AND id != '.$row->id;
        $db->setQuery($sql);
        $subscriptionIds = $db->loadColumn();
        if (count($subscriptionIds))
        {
            $rowFields = OSMembershipHelper::getProfileFields(0, false);
            $form = new RADForm($rowFields);
            $form->storeData($row->id, $data);           
            //Store core fields data
            $sql = 'SELECT name FROM #__osmembership_fields WHERE is_core=1 AND published = 1';
            $db->setQuery($sql);
            $coreFields = $db->loadColumn();
            $coreFieldData = array();
            foreach ($coreFields as $field)
            {
                if (isset($data[$field]))
                {
                    $coreFieldData[$field] = $data[$field];
                }
                else
                {
                    $coreFieldData[$field] = '';
                }
            }
            foreach ($subscriptionIds as $subscriptionId)
            {
                $rowSubscription = JTable::getInstance('OsMembership', 'Subscriber');
                $rowSubscription->load($subscriptionId);
                $rowSubscription->bind($coreFieldData);
                $rowSubscription->store();
                $form->storeData($subscriptionId, $data);
            }
        }   
    }

PHP:

public static function getEmailContent($config, $row, $toAdmin = false)
    {
        $db = JFactory::getDbo();               
        $sql = 'SELECT lifetime_membership, title FROM #__osmembership_plans WHERE id=' . $row->plan_id;
        $db->setQuery($sql);
        $plan = $db->loadObject();
 
        $data = array();
        $data['planTitle'] = $plan->title;
        $data['lifetimeMembership'] = $plan->lifetime_membership;
        $data['config'] = $config;
        $data['row'] = $row;
        $data['toAdmin'] = $toAdmin;       
        if ($row->payment_method == 'os_creditcard')
        {
            $cardNumber = JRequest::getVar('x_card_num', '');
            $last4Digits = substr($cardNumber, strlen($cardNumber) - 4);
            $data['last4Digits'] = $last4Digits;           
        }                                               
        if ($row->user_id)
        {
            $sql = 'SELECT username FROM #__users WHERE id=' . $row->user_id;
            $db->setQuery($sql);
            $username = $db->loadResult();
            $data['username'] = $username;           
        }
       
       
        $rowFields = OSMembershipHelper::getProfileFields($row->plan_id);
        $formData = OSMembershipHelper::getProfileData($row, $row->plan_id, $rowFields);
        $form = new RADForm($rowFields);
        $form->setData($formData)->bindData();
        $data['form'] = $form;
        return OSMembershipHelperHtml::loadCommonLayout(JPATH_ROOT.'/components/com_osmembership/emailtemplates/email.php', $data);                       
    }

Hope this helps. Thanks for your help.

cheesegrits · Sep 19, 2014

Hmmm, yeah, I think I'm going to have to ftp in and take a look.

What component is this? If it's a freebie, it'd probably be easier for me to install it, and lok at the code in Eclipse. I would just log in and take a look, but you haven't filled out the actual site details in My Sites.

-- hugh

cchiacchierini · Sep 19, 2014

It's Membership Pro by OS Solution. I just updated the mysites page with all of the login info.

cheesegrits · Sep 21, 2014

OK, I'll get to it as soon as I can. I'm working on some JED related stuff this weekend, so it'll be Monday.

-- hugh

cchiacchierini · Sep 23, 2014

Thanks, Hugh.

cheesegrits · Sep 24, 2014

The ftp credntials aren't working.

Command:USER MRALxxxxx
Response:331 Password required for MRALxxxxx
Command

ASS **********************
Response:530 Login incorrect.
(I've x'ed out half the username above, but I copied and pasted it from your My Sites, along witht he password, so I'm using the ones you provided)
-- hugh

cchiacchierini · Sep 24, 2014

Ugh. I reversed the username and password when entering them on mysites when I first put them in. I just fixed the error. Sorry about that. Also, you might need to use SFTP. It is a shell account, and I think it requires SFTP.

cchiacchierini · Sep 25, 2014

Friendly bump and a question: were you able to login via FTP?

cchiacchierini · Sep 28, 2014

Moving the site today, so no changes please. I will update the site info when completed.

cchiacchierini · Sep 28, 2014

Site moved. Site info updated.

cheesegrits · Sep 29, 2014

OK, I'll get to it as soon as I can today.

-- hugh

cchiacchierini · Oct 8, 2014

Friendly bump.

Best way to update a linked record on form submit?

Member

Support Gopher

Member

Support Gopher

Member

Support Gopher

Member

Support Gopher

Member

Attachments

Support Gopher

Member

Support Gopher

Member

Support Gopher

Member

Member

Member

Member

Support Gopher

Member

Useful links