MODX Community

Formit2db Not Saving to DB & Makes form Fail

I have a functioning formit form. Now I want to add Formit2db to update one field on an existing database table.

However I cannot get the Fortmit2db to work properly with the existing form.
Below is my functioning Formit snippet without the added Formit2db code

[[!FormIt?
&hooks=`FormItSaveForm,email,redirect`
&emailTpl=`MyEmailChunk`
&emailSubject=`TSSR Approved`
&emailTo=`####@website.com,[[+email]]`
&emailCC=`####@website2.com`
&redirectTo=`https://website.com/tssrapprovednotice.html`
&validate=`name:required,email:required`
&validationErrorMessage=`Please correct the following errors:[[+errors]]`
 ]]

And here is the code with the Formit2db call added in:

[[!FormIt?
&hooks=`FormItSaveForm,formit2db,email,redirect`
&emailTpl=`MyEmailChunk`
&preHooks=`db2formit`
&prefix=`modx8_`
&packagename=`tfsurvey`
&tablename=`tfsurvey`
&classname=`TfSurvey`
&where=`id`
&fieldname=`tssrApproved`
&emailSubject=`TSSR Approved`
&emailFrom=`[[+email]]`
&emailTo=`###@website.com,[[+email]]`
&emailCC=`###@website2.com`
&redirectTo=`https://website.com/tssrapprovednotice.html`
&validate=`name:required,email:required`
&validationErrorMessage=`Please correct the following errors:[[+errors]]`
  ]]

The prefix is correct as are the package, table & classnames all correct.

The field I am trying to update is “tssrApproved”

In the form I have added the following field:

<label for="tssrApproved">
    <br />TSSR Approved:
    <span class="error">[[!+fi.error.tssrApproved]]</span>
</label>
<input type="text" name="tssrApproved" id="tssrApproved" value="[[$tssrApproved]]" />

Any suggestions what I am doing wrong?

Is this an easy fix from what I’ve done so far?

Below the full Formit Form:

<h2>TSSR Approval</h2>
[[!+fi.validation_error_message:notempty=`<p>[[!+fi.validation_error_message]]</p>`]]
<form action="[[~[[*id]]]]" method="post" class="form">
<input type="hidden" name="nospam" value="" />
<label for="name">
    Contact:
    <span class="error">[[!+fi.error.name]]</span>
</label>
<input type="text" name="name" id="name" value="[[$siteContractor]]" />
<label for="email">
    <br />Email:
    <span class="error">[[!+fi.error.email]]</span>
</label>
<input type="text" name="email" id="email" value="[[$siteEmail]]" />
<label for="subject">
    <br />Site B:
    <span class="error">[[!+fi.error.subject]]</span>
</label>
<input type="text" name="subject" id="subject" value="[[$siteShellLrd]]" />
<label for="fesite">
    <br />Site A:
    <span class="error">[[!+fi.error.fesite]]</span>
</label>
<input type="text" name="fesite" id="fesite" value="[[$siteMaxisLrd]]" />
<label for="tssrApproved">
TSSR Approved:
<span class="error">[[!+fi.error.tssrApproved]]</span>


If you want to update a row in a database table, you need to provide the id-value of that row.

&where=`{"id":[[!+a_placeholder_with_the_row_id]]}`
  • &fieldname (in combination with &paramname) is an alternative way to create a where-condition, so you won’t need this property.
  • You also don’t need the &tablename property.
  • You only need the preHook 'db2formit' if you want to fill the form with data from the database table when it is shown. Not sure if this is necessary to your case.

Hi halftrainedharry,

Thank you for taking the time to look at my issue.

I made the changes but the form did not send so no emails were triggered and the database was not updated.
Here’s the new code:

[[!FormIt?
&hooks=`FormItSaveForm,formit2db,email,redirect`
&emailTpl=`MyEmailChunk`
&prefix=`modx8_`
&packagename=`tfsurvey`
&classname=`TfSurvey`
&where=`{"id":[[!+tssrApproved]]}`
&emailSubject=`TSSR Approved`
&emailTo=`###@website.com,[[+email]]`
&emailCC=`###@website2.com`
&redirectTo=`https://website.com/tssrapprovednotice.html`
&validate=`name:required,email:required,tssrApproved:required`
&validationErrorMessage=`Please correct the following errors:[[+errors]]`
]]

It is probably important to know that this is a generic form that is called to a dynamic page that shows on the front end data relating to one specific row of the database. There are 100’s of these rows and one dynamically created web page for each row.

It is this one field called “tssrApproved” that needs to be updated on the row of each page.

I always use migxDb for updating databases and never have an issue, but in this case I have to make one update from the front end, hence I’m trying to get formit2db working.

So I’m a bit confused about your setup.
To update a row you need two different values. One to select the right row and one with the new value to set.

$row_id = 10; //to select the row
$tssrApproved = 'xy'; //to set the new value
$obj = $modx->getObject('TfSurvey', array('id'=>$row_id)); //select the row
$obj->set('tssrApproved', $tssrApproved); //update the row
$obj->save();

//or maybe this is what you want to do
$tssrApproved_oldValue = 'xx';
$tssrApproved_newValue = 'yy';
$obj = $modx->getObject('TfSurvey', array('tssrApproved'=>$tssrApproved_oldValue));
$obj->set('tssrApproved', $tssrApproved_newValue);
$obj->save();

I think at the moment you are just providing one value and that would create code like this, that doesn’t make any sense.

$tssrApproved = 'xy'; //one value to select and set
$obj = $modx->getObject('TfSurvey', array('id'=>$tssrApproved));
$obj->set('tssrApproved', $tssrApproved);
$obj->save();

Each page is generated dynamically, then using migxLoopCollection I can fill the page with the data from the one row that is related to that page.

The form is a generic form that is called via a chunk to each page, so since every page relates to a different row on the database I cannot specifically name the row in the formit snippet.

What I am doing is pre-populating the (other) fields of the formit form, again using migxLoopCollection. This is specific information relating to the row associated with each dynamically created web page.

The only field that requires input is tssrApproved (value = yes/no) and then click submit. (Without Formit2db this is all fully functionally).

I can easily create and pre-populate a new field on the form with the row id number for that particular page, if I was to do this would there be any way I could use this form field for updating the database field for the field tssrApproved?

To make your call to migxLoopCollection you need the id too.

[[migxLoopCollection?&packageName=`tfsurvey`&classname=`TfSurvey`&where=`{"id":"[[+row_id]]"}`&tpl=`...`]]

Can’t you just use the same where-clause you use in the migxLoopCollection-call and put it in the &where parameter of the FormIt-call?

[[!FormIt?&where=`{"id":"[[+row_id]]"}` ...

Btw, if you only want to update the tssrApproved field, you should define the removeFields property with the names of all the other form fields to exclude them.

Hi halftrainedharry,

Thanks for the response. The where-clause is a bit different for migxLoopCollection, but yes it makes sense to try.

I will give it a go later tonight and update the result on here

Hi halftrainedharry,

I have tried a number of combinations of the “where-clause” I used in the migxLoopCollection but none (I have tried so far) have worked in my formit2db added code.

This is the migxLoopCollection I’m using to create the dynamic data in each page:

[[!migxLoopCollection?
&packageName=`tfsurvey`
&classname=`TfSurvey`
&selectfieldsXXX=`id,siteLrd,name,latitude,longitude,fesite,,state,area,tssrApproved`
&tpl=`siteSurveyTpl`
&sortConfig=`[{"sortby":"pos"}]`
[[!prepare_survey_where]]
]]

“prepare_survey_where” points to a plug-in that creates each page depending on its “siteLrd”. (btw … I use this same code format to populate the Formit form fields, I just create a different TPL and the formit form fields fill with the correct data for that particular page)

I think I am straying from the initial direction of this post.

Going back to the first response from @halftrainedharry, it was mentioned that:

&fieldname in combination with &paramname is an alternative way to create a where-condition

Maybe this is solution for what I want to do?

The only thing is, I do not know what information (text) to put into:

&paramname and &fieldname,
or in which order they go.

Appreciate any input …

I thought I knew what the snippet was doing, but now I did some tests and the &where property behaves strangely. (Seems to work with fixed strings and in the preHook but loses its value in the hook). Sorry for that!

So as you said, it is probably better to focus on the &paramname-property.
(I looked at the code and the &fieldname has no purpose at all. Not sure what it is used for!)

You could try this. Delete the &where-property, then add the &paramname and a new input field with a corresponding name. The name has to be the name of the primary-key-column of your table.
(If the name is id you probably need to use friendly urls to avoid having two parameters with the same name.)

[[!FormIt?
...
&paramname=`id`
]]
<form method="post" action="[[~[[*id]]]]"> 
  ...
  <input type="hidden" name="id" value="[[!+fi.id:empty=`[[$whatever_you_do_here_to_get_the_row_id]]`]]"/>
</form>

For testing purposes it is probably better to change the type from hidden to text so you can see if the id is correct and doesn’t lose its value if the form validation fails.

If this doesn’t work either I will write you a custom hook, which is probably better suited for your purpose. (For example: The FormIt2db-snippet automatically adds a new line to the table if it doesn’t find a row with the given id. You most likely don’t want that).

Hi halftrainedharry

Thanks again for taking the time to assist me solve this issue. Alas it is to no avail.
I posted the following code but all it did was reset the page and posted to the 4 fields in the form all the corresponding data read straight from their fieldnames in the database.

Perhaps I did something wrong?

[[!FormIt?
&hooks=`FormItSaveForm,formit2db,email,redirect`
&emailTpl=`MyEmailChunk`
&prefix=`modx8_` 
&packagename=`tfsurvey`
&classname=`TfSurvey`
&paramname=`id`
&emailSubject=`TSSR Approved`
&emailTo=`###@website.com,[[+email]]`
&emailCC=`###@website2.com`
&redirectTo=`https://website.com/tssrapprovednotice.html`
&validate=`name:required,email:required,tssrApproved:required`
&validationErrorMessage=`Please correct the following errors:[[+errors]]`
]]

<h2>TSSR Approval</h2>
[[!+fi.validation_error_message:notempty=`<p>[[!+fi.validation_error_message]]</p>`]]
<form action="[[~[[*id]]]]" method="post" class="form">
<input type="hidden" name="id" value="[[!+fi.id:empty=`[[$prepare_survey_where]]`]]"/>
<input type="hidden" name="nospam" value="" />
<label for="name">
Contact:
<span class="error">[[!+fi.error.name]]</span>
</label>
<input type="text" name="name" id="name" value="[[$siteContractor]]" />
<label for="email">
<br />Email:
<span class="error">[[!+fi.error.email]]</span>
</label>
<input type="text" name="email" id="email" value="[[$siteEmail]]" />
<label for="subject">
<br />Site B:
<span class="error">[[!+fi.error.subject]]</span>
</label>
<input type="text" name="subject" id="subject" value="[[$siteShellLrd]]" />
<label for="fesite">
<br />Site A:
<span class="error">[[!+fi.error.fesite]]</span>
</label>
<input type="text" name="fesite" id="fesite" value="[[$siteMaxisLrd]]" />
<label for="tssrApproved">
<br />TSSR Approved:
<span class="error">[[!+fi.error.tssrApproved]]</span>
</label>
<input type="text" name="tssrApproved" id="tssrApproved" value="[[!+fi.tssrApproved]]" 
/>
<br class="clear" />
<div class="form-buttons"><br />
    <input type="submit" value="Send Approve TSSR Notification" />
</div>
</form>

Does your database table tfsurvey have a column with the name id?
Does the chunk [[$prepare_survey_where]] return the right value of this column id for the current row?

The snippet formit2db should create an entry in the error log if something fails. Can you check if something gets added to the error log when you try to send the form?
Can you check if a new row gets added to your database table when you try to send the form?

Maybe you could share the schema for the table tfsurvey or the code for [[$prepare_survey_where]] or [[!prepare_survey_where]] if all the things above are of no avail.

It did, it pointed to the &prefix, so I removed that line and tried again.
Then the form sent but it left me with some unintended consequences :sweat_smile:

Formit2db created another row in the datebase called 0 (zero). Oddly enough every field in the row ‘0’ had a 0 in it apart from the field “tssrApproved” which contained the text I inserted in the form.

Meaning your code worked for creating a new row, but did not input the text into the row of the actual webpage.

The code [[$prepare_survey_where]] points to a snippet that is part of creating new webpages on the fly. However I am also using it with migxLoopCollection to pre-populate 4 form fields of the Formit form, this works well.

However it has also created a new webpage called “0”. It’s no issue because I can just delete that row from the database.

So you can definitely claim partial success if not an answer to my problem, which is how to get formit2db to populate 1 field in an existing database row from a dynamic webpage.

I don’t know how to go about this for a generic form that is called to many different webpages whose URL is read from the database.

Schema for tfsurvey:

<?xml version="1.0" encoding="UTF-8"?>
<model package="tfsurvey" baseClass="xPDOObject" platform="mysql" 
defaultEngine="MyISAM" version="1.1">
<object class="TfSurvey" table="tfsurvey" extends="xPDOSimpleObject">
	<field key="contractor" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="mailadd" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="tssrApproved" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
  	<field key="region" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="state" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="area" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="neSiteId" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="shellLrd" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="neSiteName" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="latitude" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="longitude" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="siteAddress" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
    <field key="feMaxisLrd" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>		
	<field key="latitude2" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="longitude2" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="feSiteAddress" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0"/>
	<field key="resource_id" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
    <field key="resource_ids" dbtype="text" phptype="string" null="false" default="0" />
    <field key="files" dbtype="text" phptype="string" null="false" default="" />
	<field key="images" dbtype="text" phptype="string" null="false" default="" />
	<field key="drawings" dbtype="text" phptype="string" null="false" default="" />
	<field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
    <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
    <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
    <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
	<field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
	<field key="deletedon" dbtype="datetime" phptype="datetime" null="true" />
	<field key="deletedby" dbtype="int" precision="10" phptype="integer" null="true" default="0" />
    <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />  
	<field key="publishedon" dbtype="datetime" phptype="datetime" null="true" />
	<field key="publishedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
    <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
    <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>
</object>
</model>

the snippet prepare_survey_where

$output = '';
if (isset($_REQUEST['object_id'])){
$output = '&where=`{"id":"' . $_REQUEST['object_id'] . '"}`';
}
return $output;

I removed most of the rows from the schema for the sake of brevity (there’s a lot)

Try this. Create a new snippet getObjectId with the code

<?php
$output = '';
if (isset($_REQUEST['object_id'])){
  $output = intval($_REQUEST['object_id']);
}
return $output;

then remove the &paramname from the FormIt-call and add this instead

[[!FormIt?
...
&where=`{"id":"[[!getObjectId]]"}`
]]

and in the form replace the <input name="id"> with this input field

<form method="post" action="[[~[[*id]]]]"> 
  ...
  <input name="object_id" type="hidden" value="[[!getObjectId]]"/>
</form>

I am not sure how the request-parameter object_id is set when you first call the page with the form. But if it is somehow set on every subsequent call too (meaning when you send the form), you may not need the input-field.

YES, YES, YES, YES & YES IT WORKED :smiley: :smiley: :smiley: :smiley: :smiley:

Thank you so much halftrainedharry

I think you have surpassed the modest message of your moniker and you are a fullytrainedharry really :smiley:

Actually you went above and beyond the help a lot of forum regulars give their visitors so I salute you sir :clap: :clap: :clap: :clap: :clap:

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.