Get last record ID and store it in a field

[lucfit]

Hello,

I have a form that collects data that are inserted in a list (A).
Upon submission, the same data are added to another list (B) via the Upsert plugin.

After submission, I would like to read the rowid of the last generated record in the list (B) and update a 'lista___listblastrecordid' field in the list (A).

I've tried using the PHP Plugin (End of form submission) with this code but it doesn't work:

$db =&JFactory::getDBO();
$db->setQuery("SELECT `rowid` FROM `listb` ORDER BY `rowid` DESC LIMIT 1");
$row = $db->LoadRow();
return $row;
$formModel->updateFormData('lista___lastblastrecordid', $row);

Thanks in advance for your help

 

[troester]

https://docs.joomla.org/Selecting_data_using_JDatabase#loadRow.28.29
http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#select

Usually the primary key in Fabrik is "id" not "rowid".

php running "End of form submission/onAfterProcess" can't use updateFormData, it's running after the form data was already saved into the database.
You must do a database INSERT or UPDATE yourself.

 

[lucfit]

Thanks for the reply.

So I think what I need can't be done with fabrik.

When a first form has been filled, I would like to save a record, containing the name and email of who fills out the form in a list (A) thanks to the upsert plugin.

At the same time I would like to send an email to those who have filled out a form with a link to a new form that is automatically filled in with their data (email plugin + autofill plugin).

One of these data must be the rowid generated in the list (A).

How can I pass this value (rowid)?

 

[troester]

But you can.
You can't use updateFormData but you can update your lastblast_recordid field via DB query.
http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#update

 

[troester]

BTW:
Depending on your PK/FK settings in the upsert plugin the PK (id) of the upserted record is stored in your form automatically.
See tooltips and upsert WIKI.

 

[lucfit]

Hello, thank you for your reply

I found this thread in which it seems to me it is exactly my problem (save the records on a database, then read the last rowid and save it in a new element possibly to be used to create a new list):
http://fabrikar.com/forums/index.ph...rms-field-with-the-records-primary-key.46267/

I tested this script (onBeforeProcces) but without success (the records are not saved on the external db) nor the extracted row id is stored in the field (indeed it seems that the form is not even processed)

$option = array(); //prevent problems
$option['driver'] = 'mysql';
$option['host'] = 'localhost'; // Database host name
$option['user'] = 'database_user'; // User for database authentication
$option['password'] = 'mypassword'; // Password for database authentication
$option['database'] = ' database_name'; // Database name
$mydb = JDatabaseDriver::getInstance( $option );
$myQuery = $myDb->getQuery(true);
$myQuery->insert('destination_tablename')
->set('field1= ' . $myDb->quote('{form___field1}'))
->set('fieldn = ' . $myDb->quote('{form___fieldn}'))
$myDb->setQuery($myQuery);
$myDb->execute();
$myId = $myDb->insertid();
// Update form data with insert id
$formModel->updateFormData('form___rowidextractedfromdatabase', $myId);

I am not a php expert but it seems to me that the dynamics are really identical ...
Where can the problem be?

Thanks!

 

[troester]

Obviously in the other thread the code was working.
So
Use this for the connection http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#use-alternate-fabrik-connection
Check for typos
Use var_dump(...);exit; for debugging etc.

 

[lucfit]

Hello Troester,

I solved this way:

Upon insertion of the module (list A), I do an upsert in an external database (list B). One of the upsert fields is the module ID (list A).

I then used the redirect plugin to pass the generated ID to a new module {listA_id}

In this 2nd module, I called back the #id by a query inside a field (PHP eval) of this type:

$myDb = FabrikWorker::getDbo(false, 2);
$myQuery = $myDb->getQuery(true);
$myQuery
->select('2nddatabase_listB_id')
->from('2nddatabase_listB')
->where('2nddatabase_listB_id_of_listA_module_lastinsertion = ' . $myDb->quote('{listA_id}'));
$myDb->setQuery($myQuery);
$fieldA = $myDb->loadResult();
return $fieldA;

The problem was that I was not creating a second connection as you specified:

Use this for the connection http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#use-alternate-fabrik-connection

Thanks for your help!