Forums

Hi there,

In the form code I added a hidden field with the value "wb0". I want this to be inserted in tables along with the form data. This value however never gets into the database. Why? Simple: because when I look into my browser's source-pane, the default value of the input field has become "". Tried to make it an input type text field, but then I indeed see no default value in the input box in the browser.

How to prevent this value to be reset to null?

Cheers,
Bas

I guess I figured out what the problem is.

The form I use has the Profile Page settings set to a table X. The fields on the form all relate to the columns in that table X. As I want to populate a different table Y for logging purposes, I added a hidden field with an identifier for the form that's getting submitted. This identifier is, together with userid and recordtime stored in that table Y.

But because it is an updateable form, the fields on it first get the values from the database. And as this hidden field has no related column in table Y, I guess chronoforms sets its value to "".

Now I think it might be a solution to have the hidden field's value set by JavaScript.

CU,
Bas

This indeed does what I need. added an onClick at the submit-button.

Hi Bas,

You could set the value after the form is submitted in the OnSubmit Before box if you can access the info. The form name is available as a part of the $_POST array if that helps.

Bob

I guess I need to follow your advice, maybe even further:

Even using my JavaScript solution, which helps to store the hidden value, I don't get things as they should.

People can use a number of forms, say form wb0 and form wb2. When they submit eiter of these forms, I want logged the user and form and recordtime. So in fact, simply a CF-form with additional field "formID". This way I can present on a ChronoConnectivity query which forms they already have processed and which are "to do".

However. I can store that log-record, but appearantly only one per user. When I first submit form wb0, the value wb0 is indeed stored in the row with cf_id = 1. But when I subsequently submit form wb2, this record is updated, but I want a second row in the table. I even added a unique index on the table, hoping this would force the update to fail and continue with the insert, but still this single row keeps being updated.

How to do this? If I should have to add my own UPDATE/INSERT in the onSubmit area, how would that syntax look like? Or can I use some kind of INSERT.... ON DUPLICATE KEY UPDATE....? But again, since I am not a PHP/MySQL guru, how would that syntax look like?


Cheers,
Bas

Hi Grover002 ,

I think that the problem here is that the cf_id column is being used in both tables but with different values. Try changing the column name in one of the tables to say, cf_id_a and see if that makes the difference. You'll need to save both ids in hidden inputs to get the two tables to updae correctly.

Bob

NB You need to refresh the DB Connection after any changes to database column names. In the Form Editor click the DB Connection tab and set the Connection to 'No'. Click the 'Apply icon in the toolbar to save the form, open the DB Connection tab, set the Connection back to 'Yes' and re-save the form. This will refresh the copy of the table information that ChronoForms uses.

Hi Bob,

I followed your suggestion. In a way this is a neat and tidy solution, however.....

Now I am still having an insert on every submit. As mentioned, I want to insert a new row only if the combination userid/formID doesn't already exist in the table, otherwise it should update the row that's already there. Like the Profile Page approach, but this only applies for the main table with all the form data that is presented on screen.

When I added a unique index (userid/formid) to the table, hoping this would force an update after a failed insert, I simply get the SQL error about a duplicate entry.

So I guess I need to emulate the Profile Page approach in the On Submit area, don't I?

Cheers,
Bas

Hi bas,

Ah - OK, two steps forward and one step back. Unfortunately the automatic code depends on an integer primary key (not an index).

I can see two choices:

a) You could use a couple of arrays to construct a primary key value from a combination of the user id and form id - something like 88800999 where 888 is the form id and 00999 is a zero padded user id.

b) You can hand - code the database update to search for existing results and then do the appropriate Update/insert query.

Bob

Yeah, well alright, I was already considering this hand-coded update/insert. But as stated before, could you give me some sample code to include, since I am no php/mysql-guru?

Would be very helpful!

Thanks,
Bas

OK, tried to apply first suggestion, to build a combined primary key from userid and formid. My approach is to let the primary key become cf_user_id * 1000 + formid, since the formid will be somewhere between 0 and 20 for now.

For form # 0, the row for user 62 will therefor get primary key 62000. The form indeed passes this value, as the debug shows $_POST Array: Array ( [cf_id] => 1 [WBUSERID] => 62000 ...... where WBUSERID is indeed the column name of the primary key.

....but....no row in the table....I just don't get it....

Hi Grover002,

Have you turned AutoIncrement off? That could be the problem.

If you temporarily turn Site Debugging on in Site Global Configuration then you should be able to see the MySQL query that is being generated.

Bob

PS You need to refresh the DB Connection after any changes to database column names. In the Form Editor click the DB Connection tab and set the Connection to 'No'. Click the 'Apply icon in the toolbar to save the form, open the DB Connection tab, set the Connection back to 'Yes' and re-save the form. This will refresh the copy of the table information that ChronoForms uses.

Yes autoincrement was switched off. And debug was on. The response I get is:

   1. Form passed first SPAM check OK
   2. Form passed the submissions limit (if enabled) OK
   3. Form passed the Image verification (if enabled) OK
   4. Form passed the server side validation (if enabled) OK
   5. $_POST Array: Array ( [cf_id] => 1 [WBUSERID] => 62000 [Werkblad] => [reden] => Blabla!! [bewijs] => Blabla [resultaat] => Blabla!!? [button_7] => Opslaan [0487ade0291d9c8edeb3133b79b91986] => 1 [1cf1] => ac50f015c36769e0c666279bae79891a [chronoformname] => wb0 )
   6. $_FILES Array: Array ( )
   7. Form passed the plugins step (if enabled) OK
   8. Debug End

So I don't see any SQL statements?

Hold on, in the back end I do see the logged SQL. But the table that stays empty is only queried (select) and no insert or updates are done.

Hi Grover002 ,

I was wanting to see the *Site* debug report. The ChronoForms one doesn't include the MySQL queries, the site one will show you many.

Bob

Ahh...took a while before I found out that way below my normal page, it shows the queries.

For the table at hand, indeed I see no INSERT, only an UPDATE, it shows

UPDATE `IK20_UserWB`
  SET `recordtime`='2010-08-23 - 17:09:38',`cf_user_id`='62',`Werkblad`=''
  WHERE WBUSERID='62000'

But as there is no row with that WBUSERID (it is currently empty) , this wil fail and the table remains empty.

Decided to try a hand-coded insert/update. I added this to the OnSubmit area in de FormCode tab.

$wbuser = JRequest::getVar('WBUSERID', '', 'post');
$recordtime = date("Y-m-d")." - ".date("H:i:s");
$werkblad = "wb0";
$db =& JFactory::getDBO();
$IKinsert = "INSERT INTO IK20_UserWB (WBUSERID, recordtime, cf_user_id, Werkblad) VALUES ($wbuser, '$recordtime', '$user->id', '$werkblad')";
$IKupdate = "UPDATE IK20_UserWB SET recordtime = '$recordtime' WHERE WBUSERID = $wbuser";

$db->setQuery($IKinsert);
if (!$db->query()) {
   $db->setQuery($IKupdate);
   if (!$db->query()) { echo "Bijwerken logfile mislukt"};
}

Unfortunately, no data gets into the table. In the SQL log, I also see no referral to my table IK20_UserWB. By the way, I unselected this table in the DB connection tab.

Why don't I get this thing to work?!?!?

Getting a bit desperate here. I get the feeling that the On Submit part is not executed at all!! Even tried a hard-coded insert now and still that doesn't bring any data to the table. It must be either some setting I am missing or some utterly simple mistake I am making.

So please, what can be wrong with the code in the previous posting, or the hard-coded one below?

<?php 
$db =& JFactory::getDBO();
$IKinsert = "INSERT INTO `IK20_UserWB` (`WBUSERID`, `recordtime`, `cf_user_id`, `Werkblad`) VALUES (65000, '2010-09-09 - 10:11:12', '65', 'wb0')";

$db->setQuery($IKinsert);
$db->query();
?>

Hi Grover002,

The OnSubmit Before box only executes if 'Send Emails' is set to 'yes' on the form General tab (a historic quirk of ChronoForms). The OnSubmit After box will always execute.

Bob

See, I suspected it was something small....and that took me two days.

Thanks Bob, everything is working smoothly now, can't tell how happy I am!!🤣