Creating linked records in two tables

I am trying to write new records to jos_simplelists (id, title, link_type, link) and to jos_simplelists_categories (id, category_id)

I have "enable data storage" pointing to jos_simplelists - so the autogenerated code writes the record to jos_simplelists

Looking through the forum it seems I have to write the jos_simplelists_categories record in "On submit code after sending email" and change the order of processing to after autogenerated.

I have this

<?php
$db =& JFactory::getDBO();
$catid = JRequest::getInt('select_0', '', 'post');
$id = /* ???????? */ ;
$query = "INSERT INTO `jos_simplelists_categories` (id, category_id) VALUES ($id, $catid)";
$db->setQuery($query);
$db->query();
?>

But how do I get the ID of the jos_simplelists record just written?

Thanks
jb

Hi jbudd,

Hmmm slightly tricky.

ChronoForms can link to more than one table and write to both of them by matching input names to column names. I don't think this will help here though.

For the approcah you suggest you can get the data CF has saved from $MyForm->tablerow["jos_table_name"]. The Autogenerated code creates this for each record.

Bob

PS I hadn't seen SimpleLists before, looks neat - you could do the same with ChronoConnectivity I think.

Thanks for that GreyHead.

It now writes both records. Of course there is still a problem.

ChronoForms can link to more than one table and write to both of them by matching input names to column names. I don't think this will help here though.

I dont think so either, because the tables are linked by ID which is generated by the database when the record is stored.

This is my code after email submit

<p><span style="color: #0000ff;"><strong>Your link will be visible on the site immediately</strong></span></p>
<?php
/* populate jos_simplelists: link_type, link, published, created, created_by, alias*/
$_POST['link_type'] = 'custom';
$_POST['published'] = '1';
$_POST['created'] = date("Y-m-d H:i:s");
$user = JFactory::getUser();
$_POST['created_by'] = $user->id;
$_POST['alias'] = JFilterOutput::stringURLSafe($_POST['title']);
?>
<?php /* write jos_simplelists_categories */
$db =& JFactory::getDBO();
$catid = JRequest::getInt('select_0', '', 'post');
$id = $MyForm->tablerow["jos_simplelists"]->id;
$query = "INSERT INTO `jos_simplelists_categories` (id, category_id) VALUES ($id, $catid)";
$db->setQuery($query);
$db->query();
?>

The first block of php has to be executed before the form contents are saved to jos_simplelists because it is populating some of the fields.
The second block has to be executed after jos_simplelists is written because it needs the record ID

Run Order is plugins, autogenerated, onSubmit

I tried moving the first block into "code before sending email" but it doesnt seem to make any difference.

Possible solutions:
1. Copy the autogenerated code into "after sending email" and somehow turn off the autogenerated code (remove jos_simplelists from the db connectivity tab?)

2. Have the form write to jos_simplelists_categories, manually write to jos_simplelists (Should work in this specific case because the only field besides ID is a field on the form. If something goes wrong, could lead to an orphan jos_simplelists_categories record)Doesnt work, the id generator is on the other table

3. Use hidden fields in the form (I dont know what you can do with hidden fields)

Which is the best approach?

jb

Hi John,

For the approcah you suggest you can get the data CF has saved from $MyForm->tablerow["jos_table_name"]. The Autogenerated code creates this for each record.

This should let you get the id to use in the second save.

Bob