• 1

Read this first!

We do not monitor these forums. The forum is provided to exchange information and experience with other users ONLY. Forum responses are not guaranteed.

However, please submit a ticket if you have an active subscription and wish to receive support. Our ticketing system is the only way of getting in touch with RSJoomla! and receiving the official RSJoomla! Customer Support.

For more information, the Support Policy is located here.

Thank you!

TOPIC: OPERATING ON FORM DATA AND ADDING A NEW ROW IN DB

OPERATING ON FORM DATA AND ADDING A NEW ROW IN DB 12 years 4 months ago #15871

  • learnthrusong
  • learnthrusong's Avatar
  • OFFLINE
  • Junior Boarder
  • Posts: 26
  • Thank you received: 3
RSFormsPro is so powerful, you can use it to totally customise Joomla to make your site and components work better for your workflows, business and site. Here, I'm going to describe how, with a little bit of scripting, you can quickly and automatically initialise a set of variables for every one of your form fields with which to operate on. This will save you loads of time if youhave loads of fields you need to insert into a custom table by giving your variables the same names as your fields.

SCENARIO
You have a form set up and are using mappings to insert a new row of data in a mysql table. Your form and mappings are all working fine but now, you want to do a little more $_POST variable processing to 'automate' a few more tasks. The mappings plugin in excellent for straight inserts where you simply want to take a selection or all of a form's data and INSERT it into a new row of a custom or existing component table.

THE POWER OF THE MAPPING PLUGIN
Using the mappings plugin, you can easily integrate any native/3rd-party components by creating custom 'join' tables. This means that it's a perfect tool to effectively build your own component or add your own custom tables to do anything from extending permission sets, adding group permissions, integrate access to components based on your custom information etc.

For instance, you want to relate your employee/customer information to your forum component & certain sections and categories in content in order to give extra permissions to employees from certain departments to manage a forum/news and articles for every new project he/she creates. Likewise, you want the customer to get read access to these.

You can easily achieve this by creating fields (RSForm components) and populating their default values/options with simple MYSQL in PHP. Using the mapping plugin to point your form elements at the fields/tables in the DB, your inserts are handled completely by the plugin.

The beauty of it is that, whatever your customisations/integrations, they can (and should) remain NON-INVASIVE as long as the tables you use to 'join' with your joomla tables/components are custom tables, leaving your installation and components un-tarnished!
NOTE: ONLY USE MAPPINGS & CUSTOM SCRIPTING TO INSERT/UPDATE YOUR DATABASE
IF YOU KNOW WHAT YOU ARE DOING!!!

WHEN THE MAPPINGS PLUGIN MIGHT NOT SUFFICE
Well, there are lots of scenarios in which you may want to be able to operate on $_POST data and the mappings plugin will either be trickier than need be or not possible. Here are a few examples...
Where you wish to:
1 - Insert some rows/data into other columns and you want to feedback/insert that 'other' row's Primary Key into your initial table as a Foreign Key
2 - Update information in other tables whilst you have the $_POST data available
3 - Do some custom validation, code or statistic generation to insert with your initial table

WHY CUSTOM SCRIPTING? MAPPING WAS SO EASY TO USE
I recently discovered that you can't actually achieve this with the mappings plugin - or, at least not very simply. This is because the 'current' $_POST data from the form won't yet have been inserted by the mappings plugin.

FEAR NOT, THE SOLUTION IS QUICK & SIMPLE
If the mapping plugin isn't going to the job for you then we have to create a variable out of everything in the $_POST variable. If you've got a lot of fields like me, you're not going to want to write out the following for each of them:
$myFieldVar = $_POST['form']['myField'];

So how can we achieve a customised set of inserts and updates with minimal scripting?
We are going to create a variable for every field in your form

First, let's check what's in the $_POST array by adding the following to the script called on form process area:
print_r($_POST['form']);
die();
//This will give you a human readable array of the data in you just posted in your form 
Now you can see the key=>value pairs and arrays contained in $_POST, you'll see that all of the keys are your field names. So now, using the variable variables technique inside a loop, we can;
  1. create a variable for each field (key in the array) named the same as your field
  2. add the field's value(s) to the newly created variable
/*
// loop through the post array //
// $key is the name/id of your field // 
 // $value is the value of your field // 
*/
foreach($_POST['form'] as $key=>$value){ 
	$k = $key; // $k is now =to the id of the field, for example email 
	$$k = $value; // $$k sets your new variable, i.e. $email, to the value of email on the form 
}

This means that now, to refer to the values of your input to the form, you can simply use the dollar sign prepended to the field id. For example:
// Supposing you have a field with id of 'email', you can go ahead and use $email in your script
echo $email; // OR
die($email); // TO CHECK IT'S WORKING
// Now, you're inserts/updates can be written easily and consistently.
DEBUG EASILY - A small if(
OK, so now you've checked everything in the POST variable and you can see all of the $_POST, you wanna check that all of the $elements equivalents exist without echoing them all manually, right? Especially if you have a lot of fields.

Simply REPLACE
// LOOP THE FORM DATA TO SET VARS
foreach($_POST['form'] as $key=>$value){ // loop thru ur post array
	$k = $key; // set a variable to the 'name' of your key - $k now = your field id/name
	$$k = $value; // Yes, 2 dollar signs - this sets a variable with your fields name to its value in the post array
} // END LOOP THROUGH THE FORM KEY=>VALUES
// You might want to add die statement to prevent any emails and other actions being triggered whilst you debug. Uncomment Below
// die();
WITH
// LOOP THE FORM DATA TO SET VARS
foreach($_POST['form'] as $key=>$value){ // loop thru ur post array
	$k = $key; // set a variable to the 'name' of your key - $k now = your field id/name
	$$k = $value; // Yes, 2 dollar signs - this sets a variable with your fields name to its value in the post array
 
	// DEBUGGING - comment this block (down to and including "}// END DEBUG" lines
	//This will echo/print everything to screen so you can check a variable has been  created for all of your fields
	if (!is_array($value)){
		echo "$$k = ";
		//echo just the value
		echo "$value<br>";
	}else{ // Otherwise print the key name like $myVar and print the array
		echo "$".$key." = ";
		print_r($value);
		echo "<br>";
	}// END DEBUG	
} // END LOOP THROUGH THE FORM KEY=>VALUES
// You might want to add die statement to prevent any emails and other actions being triggered whilst you debug. Uncomment Below
// die();
AND comment it out when LIVE like:
// LOOP THE FORM DATA TO SET VARS
foreach($_POST['form'] as $key=>$value){ // loop thru ur post array
	$k = $key; // set a variable to the 'name' of your key - $k now = your field id/name
	$$k = $value; // Yes, 2 dollar signs - this sets a variable with your fields name to its value in the post array
/*
	// DEBUGGING - comment this block (down to and including "}// END DEBUG" lines
	//This will ech/print everything to screen so you can check a variable has been  created for all of your fields
	if (!is_array($value)){
		echo "$$k = ";
		//echo just the value
		echo "$value<br>";
	}else{ // Otherwise print the key name like $myVar and print the array
		echo "$".$key." = ";
		print_r($value);
		echo "<br>";
	}// END DEBUG	
*/
} // END LOOP THROUGH THE FORM KEY=>VALUES
// You might want to add die statement to prevent any emails and other actions being triggered whilst you debug. Uncomment Below
// die();
NOTE
It is not advisable that you use the same field names on your forms as the fields you are inserting them into in the database and you should escape your string data before inserting to tighten up security!
// Use the following MYSQL function around your $variables in any queries like...
'".mysql_real_escape_string($myVar)."'
Naturally, if your forms are accessible to your users, not just your admins, you will also want to do some data type/integrity checks too, e.g.
if (is_numeric($myID)){
         // Then insert it
} else {
         // Throw an error to warn the user that something went wrong like
         die($myErrorMessage); 
}
Anyway,

Hope that this is useful!

Best wishes,

Gez
Last Edit: 12 years 3 months ago by learnthrusong. Reason: UPDATE
The administrator has disabled public write access.
The following user(s) said Thank You: boducoscar

Re: OPERATING ON FORM DATA AND ADDING A NEW ROW IN DB 12 years 4 months ago #15872

  • learnthrusong
  • learnthrusong's Avatar
  • OFFLINE
  • Junior Boarder
  • Posts: 26
  • Thank you received: 3
QUICK ADD
It is also worth noting that if you have any fields on your form that are arrays themselves, for example, a multi-select box or checkbox, you can access internal values using the same convention as grabbing from POST.

Therefore, to get use the values from POST with the new local variable that the script will create, simply adding the array index/key in [square-brackets] like::
//==================================//
// For select lists where the user can only select 1 value/option
//==================================//
$mySelectValue[0]; // Where 'mySelectValue' is the ID of your form field.
For multi-select lists/checkboxes (and if you're assigning radios with a numeric index such as "0|Option\n", "1|Option 1\n"...etc.

If you know what indexes you need to target because your values are NOT DYNAMIC you can target your value using the index of the value
//==================================//
//Where [1] (the second checkbox option) is the one you want to do something with
// remember they start at [0]
//==================================//
if(isset($myCheckbox[1])){ 
    // do something with $myCheckbox[1] (AND/OR any other variables you've access to)
    echo "$myCheckbox[1]";
} 
You could even do a whole bunch of options checks for combinations, i.e. if option [5] exists then 'check all" - set to all options you listed. Similarly, you can target selected options with unique string IDs like:
//==================================//
// Where [membership_package1] (the second checkbox option is the one you want to do something with
// and the value is the name of the package
//==================================//
if(isset($myCheckbox['membership_package1'])){ 
             echo "You've chosen to join the " .$myCheckbox["membership_package1"]. " Members Package"; 
         }
If on the other hand, you have dynamic options populated from a DB query, you're not going to be able to account for every scenario by hard-coding, or at least it'll be far faster and easier to write and process if you use a loop.

Before I cover the loop, here are a couple of things you might like or need to check
// you can check if a var is an ARRAY like
if(is_array($myCheckbox)){
     // do something with $myCheckbox
     print_r($myCheckbox); // This will display a human-readable array on the page
} 
//==================================//
// you can count an ARRAY like
//==================================//
count($myCheckbox);  // OR add it to a $var like:
$chkdCount = count($myCheckbox);
 
//==================================//
// combine a check for ARRAY and count - maybe if you wanted do something else if at least x number of options selected
//==================================//
$x = 1; // this is a numeric value - whatever you want to check against
// $selectedMemberships  was auto-created using the original script/thread from checkbox field called 'selectedMemberships'
if(count($selectedMemberships)==$x){ // if only 1 selected
	foreach($selectedMemberships as $m_id=>$m_name){
	     echo "You chose to join the $m_name mebers plan"
	}// END FOR EACH
}elseif(count($selectedMemberships)==0){ // if there aren't any memberships selected
     echo "You chose not to join at the moment. ";
}else{ // The user selected multiple memberships
     // lets count them
     $chkdCount = count($myCheckbox); // now we can use this to neaten up our dynamic feedback to the user
     // initialise a counter. We'll increment this each time until 1 less than the count in order to place "and" between the
     // last and last but one selected package
     $i = 0;
 
     // now we need to loop through each element of the array to get the values 
     foreach($selectedMemberships as $membership_id=>$membership_name){ 
          // these could be"... as $anything=>$you_like ..."
 
          // increment by 1
          $i++; // same as $i = $i + 1;
 
          // Lets build a string out of the names to tell the user which memberships they chose
          if(($chkdCount-$i) >= 2){ // if total selected - the iteration through loop is greater than/= to 2
               $members_plans .= "$membership_name, "; // add to string with a comma
          }elseif(($chkdCount-$i) == 1){ // if it's 1 - it's the last but 1 
             $members_plans .= "$membership_name and "; // - add and instead of comma
          }else{
             $members_plans .= "$membership_name <br />"; // it's the last, let's add a line break
          }
     }// END FOR EACH LOOP - all our values are now in the string
	echo "You've chosen to join the $members_plans Members Packages";
}// END ELSE 
TIP!!!
The reason that you always reference a select list value from POST with the zero index is because there is ONLY ever going to be 1 option selected.

Whether a single or multi-type field, options are always going to be ARRAYS which, unless created as an associative array (where the key is a unique string identifier), they will always have an auto-incremented index from zero.

A good example of why you might like to do this would be if this was a checkbox of subscriptions AND you want to do an insert say the user id into a subscription (table) then Insert that newly inserted subscription ID as a foregn_key in your initial table.

Hope it helps!!!

Gez
Last Edit: 12 years 3 months ago by learnthrusong. Reason: UPDATE Notes on arrays, checking and feeding back
The administrator has disabled public write access.
The following user(s) said Thank You: webcat-solutions

Re: OPERATING ON FORM DATA AND ADDING A NEW ROW IN DB 12 years 3 months ago #16067

  • rbcarson
  • rbcarson's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 2
I ran across this post while searching for a solution to an issue I have trying to insert record to an external database table with data submitted with an RSForm. Here is the scenario ... using the standard mapping function.

1. I need to capture the current SubmissionId to provide a foreign key in the external table.
2. Given, I have this code in the "Script called on form process" the insert has not taken place, and the SubmissionId is not yet available.
3. So, the SubmissionId written to the external table is always zero.

So, if someone would kindly provide some commentary on the following questions.

1. Is there a place to put the "After Submit" logic to capture the current SubmissionId for the external table?
2. Or, do I need to rethink the logic and capture the unique insert key and store it in the RSForm data?
3. If the answer ends up being number 2, would I then place the insert logic in the "Script called on form process"?

Thanks for any help offered.
The administrator has disabled public write access.

Re: OPERATING ON FORM DATA AND ADDING A NEW ROW IN DB 12 years 3 months ago #16165

  • learnthrusong
  • learnthrusong's Avatar
  • OFFLINE
  • Junior Boarder
  • Posts: 26
  • Thank you received: 3
Hi there,

Everything you need is in this post I believe although it was a while ago I wrote it...

In short, you can't use the mappings plugin in situations where you wish to 'operate on' or change data then do some custom database inserts/updates. Instead, you need to run the inserts yourself in the scripts called on form process area, doing any custom validation etc, then grab the last inserted id from the table using;
// this line should go directly after
// where you execute the insert 
$myNewRow = $db->insertid();

This is because the mappings plugin actually fires after all scripts have run - i.e. form process and thank you scripts.

If you follow my post(s) above, that should get you there relatively easily... The variable variables method described and demonstrated will help you create a $var for every field (rs form component) you have on the form and speed things up for you.

Best wishes,

Gez
The administrator has disabled public write access.

Re: OPERATING ON FORM DATA AND ADDING A NEW ROW IN DB 12 years 2 months ago #16395

  • royce
  • royce's Avatar
  • OFFLINE
  • Senior Boarder
  • Posts: 52
  • Thank you received: 5
Hi there, I am a newbie to RS forms Pro, but I am a very experience Joomla! developer.

I too am trying to get the SubmissionId. My form has a file upload that I need to process after RSFP stores it in the file system. the file name is correctly stored in the DB, but neither the file name or the submissionID is passed in the $_POST, $_FILES, or anywhere else that I can see.

I can successfully call my custom component with 'Script called after form has been processed' but even with using a debugger, neither the final file name or the Submission ID is passed in any super global.

Any help with this is appreciated.

regards,
Royce
Last Edit: 12 years 2 months ago by royce. Reason: typos
The administrator has disabled public write access.
  • 1

Read this first!

We do not monitor these forums. The forum is provided to exchange information and experience with other users ONLY. Forum responses are not guaranteed.

However, please submit a ticket if you have an active subscription and wish to receive support. Our ticketing system is the only way of getting in touch with RSJoomla! and receiving the official RSJoomla! Customer Support.

For more information, the Support Policy is located here.

Thank you!