[SOLVED] Insert a record in another table and update a form's field with the records primary key

[keianrad]

I need to add a record to a list and do the following,
1. Create a new row in another table
2. Grab the id of the row and parse it in the table of my current list.
I used the code listed below from the Wiki, but it doesn't work.

Error: syntax error, unexpected ';', expecting ',' or ')'

I put the code in a php plugin in the form and set "Start of form submission (onBeforeProcces)" on
Process script.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert('app_clients')->set('type = ' . $db->quote('lead'))
->set('name = ' . $db->quote('{app_assessment___name}');
$db->setQuery($query);
$db->execute();
$id = $db->insertid();

// Update form data with insert id
$formModel->updateFormData('app_assessment___client_id', $id);


Site: SSA
Form Name: Assessment

I need to create a row in "app_clients" table and return id to the current form in "{app_assessment___client_id}" field.

 

[cheesegrits]

Missing a closing paren on the end of the second ->set(). You are only closing the quote().

Suggest you change all occurrences of $db to $myDb, and $query to $myQuery, and $id to $myId. Less chance of stomping on variables of the same name in the scope where the code is run. I know all our earlier examples used $db and $query, but we ran in to some issues in certain places where code is eval'ed.

Let me know where you found that code in the wiki, I'll correct it.

-- hugh

 

[keianrad]

Thank you. It is working perfect.

Final Code:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->insert('app_clients')->set('type = ' . $myDb->quote('lead'))
->set('name = ' . $myDb->quote('{app_assessment___name}'));
$myDb->setQuery($myQuery);
$myDb->execute();
$myId = $myDb->insertid();

// Update form data with insert id
$formModel->updateFormData('app_assessment___client_id', $myId);

This is link in wiki:
http://fabrikar.com/forums/index.ph...e-a-form-s-field-with-the-records-primary-key

 

[cheesegrits]

I've corrected the wiki. Thanks.

-- hugh

 

[keianrad]

I have another requirement for this form. I need to save some values to the other table then I used an Upsert plugin but it doesn't save the client id that we grabbed by the code listed below:

// Update form data with insert id
$formModel->updateFormData('app_assessment___client_id', $myId);

Site: SSA
Form Name: Assessment

 

[cheesegrits]

The upsert works best with the FK on the other table.

So why can't you just extend your existing code?

-- hugh

 

[keianrad]

I used code listed below but I had same result:

PHP plugin
Processes Script: "Start of form submission (onBeforeProcces)"

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->insert('app_clients')->set('type = ' . $myDb->quote('lead'))
->set('name = ' . $myDb->quote('{app_assessment___name}'));
$myDb->setQuery($myQuery);
$myDb->execute();
$myId = $myDb->insertid();

// Update form data with insert id
$formModel->updateFormData('app_assessment___client_id', $myId);

the above code is working only below code doesn't work:

// insert client id in app_contact list
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->insert('app_ccontacts')
->set('client_id = ' . $myDb->quote('{app_assessment___client_id}'));
$myDb->setQuery($myQuery);
$myDb->execute();
$myId = $myDb->insertid();

 

[cheesegrits]

app_ccontacts? With two c's?

-- hugh

 

[keianrad]

It is correct on the main code.

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->insert('app_clients')
->set('date_time = ' . $myDb->quote('{app_assessment___date_time}'))
->set('source = ' . $myDb->quote('{app_assessment___how_to_found_us}'))
->set('type = ' . $myDb->quote('lead'))
->set('creator_id = ' . $myDb->quote('{app_assessment___creator_id}'))
->set('status = ' . $myDb->quote('open'))
->set('service = ' . $myDb->quote('6'))
->set('name = ' . $myDb->quote('{app_assessment___name}'))
->set('destination = ' . $myDb->quote('{app_assessment___destination_country}'));
$myDb->setQuery($myQuery);
$myDb->execute();
$myId = $myDb->insertid();

// Update form data with insert id
$formModel->updateFormData('app_assessment___client_id', $myId);

// insert client id in app_contact list
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->insert('app_contacts')
->set('date_time = ' . $myDb->quote('{app_assessment___date_time}'))
->set('client_id = ' . $myDb->quote('{app_assessment___client_id}'))
->set('name = ' . $myDb->quote('{app_assessment___name}'));
$myDb->setQuery($myQuery);
$myDb->execute();
$myId = $myDb->insertid();

Name and date are saved in app_contacts list but client_id doesn't save!

 

[cheesegrits]

Where you do ...

->set('client_id = ' . $myDb->quote('{app_assessment___client_id}'))

... in the second query, use $myId instead:

->set('client_id = ' . $myDb->quote($myId))

Remember that placeholders are replaced BEFORE the code is run. Fabrik takes your chunk of code, substitutes any placeholders with the corresponding values, then hands the resulting code to PHP to run. And you are setting that element's value inside your code. So at the point the placeholder substitution is done, it has no value. But $myId should work, as that's a runtime variable.

-- hugh

 

[keianrad]

Thank you. It is solved.