Run a PHP query and update form field after upsert

[Ecovoyageurs]

Hi again,

On my quotations requests form (quotations_requests table), I use the upsert plugin to create a new customer (customers table) if the customer isn't known (if his email isn't in the customers table yet).

Now, in the same form, I'd like to grab the customer id and update my quotation requests field (dbjoin) to create a relation between the 2 tables.
I don't know when the upsert query is run, but is it possible (during the form validation process) to :
1 - run the upsert query
2 - get the customer id and update the form customer_id field

Currently I created 2 plugins for my form:
- the upsert plugin and it works great as defined above (set as 1st plugin on my form)
- the PHP plugin with the following code

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$email = "{quotations_requests___email}";
$query->clear();
$sql = 'SELECT id FROM customers WHERE email = "' . $email . '"';
$db->setQuery($sql);
$client_id = $db->loadResult();
$formModel->updateFormData('quotations_requests', $client_id);

The query seems to run (no error), but the client_id field isn't filled... that's why I wonder if it's possible to run a PHP query after the upsert plugin.

Thanks for any help !

 

[cheesegrits]

The upsert is run onAfterProcess, meaning after the main form's data has been written to the database. That's because we don't want to modify the upsert'ed table if the main table didn't get stored.

So you can't do what you need with updateFormData(), because that only modifies the submitted form data in memory, so only gets written to the form if you do it onBeforeProcess, before the data is written to the table.

So you are on the right track, but you'll need to manually update the table for you main form. So replace the updateFormData with something like ...

$query = $db->getQuery(true);
$query->update('quotation_requests')->set('client_id = ' . $db->quote($client_id))->where('id = ' . $formModel->getRowId());
$db->setQuery($query);
$db->execute();

Replace the table / field names in the query building as necessary.

-- hugh

 

[cheesegrits]

You should probably rewrite your code to use the query builder as well ... and use something other than $db and $query, to avoid stomping on any variables Fabrik may have of the same name ... so it'd be ...

$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->select('id')->from('customers')->where('email = ' . $myDb->quote('{quotations_requests___email}'));
$myDb->setQuery($myQuery);
$client_id = $myDb->loadResult();
if (!empty($client_id)) {
  $myQuery->clear()->update('quotation_requests')->set('client_id = ' . $myDb->quote($client_id))->where('id = ' . $formModel->getRowId());
  $myDb->setQuery($myQuery);
  $myDb->execute();
}

-- hugh

 

[Ecovoyageurs]

Thanks for your answer.
It works great if I edit an existing quotation request, but I get an error for new quotations requests :
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 SQL=UPDATE quotations_requests SET client_id = '994' WHERE id =

It seems that $formModel->getRowId() returns nothing...

 

[cheesegrits]

OK, try $formModel->formData['yourtable___id'], or whatever your pk element name is.


Sent from my HTC One using Tapatalk

 

[Ecovoyageurs]

Hehe, perfect, thanks !
The more I use Fabrik, the more I like it !