• 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: mapping form data into two or more mysql tables

mapping form data into two or more mysql tables 9 years 11 months ago #31184

  • lee3
  • lee3's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 4
I would like to use mapping tool to submit user input form data into more than one mysql table but I need clean the data before it's inserted in each table.

So php code edit:
your form > Properties > PHP Scripts > Script called after form has been processed

I unserialize the mappings like so:
foreach($mappings as $mapping)
{
  $data = unserialize($mapping->data);
  $data["computer"] = strip_tags($_POST['form']['user_computer']); // to table __hardware
  $data["menswear"] = strip_tags($_POST['form']['mens_clothing']); // to table __clothing
  $mapping->data = serialize($data);
}

This issue here is the SQL statement attempts to insert all the VALUES into all the TABLES and I get SQL Insert errors because COLUMN `menswear` does not exist in TABLE hardware just like COLUMN `computer` does not exist in TABLE clothing.

How can I use mappings to different local db tables & still cleanse the data before it's inserted:
$data["computer"] INSERTS INTO TABLE hardware.
$data["menswear"] INSERTS INTO TABLE clothing.
Last Edit: 9 years 11 months ago by lee3.
The administrator has disabled public write access.

mapping form data into two or more mysql tables 9 years 11 months ago #31189

  • lee3
  • lee3's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 4
well there goes 24 hours of my life testing, saving, resubmitting 1000 forms arrg.

Basically I added some debugging into php script area, and noticed that the array containing the user data I wanted to modify was position [0] meaning I can use $mappings[0]->data to target the date.
echo "<pre>";
print_r($mappings);
echo "</pre>";
die();
$data = unserialize($mappings[0]->data);
$morphA = strip_tags($_POST['form']['silent-user-register']);
$morphA = trim(preg_replace("#[^-a-zA-Z0-9]+#", " ", $morphA));
$morphA = ucwords(strtolower($morphA));
$morphB = strtolower($morphA);
$morphC = str_replace(" ", ".", $morphB);
$data["name"] = $morphA; // User enters: bob james jnr or BOB JAMES JNR - I change to; Bob James Jnr
$data["username"] = $morphC; // Bob James Jnr becomes: bob.james.jnr
$data["email"] = $morphC ."@business-email.com"; // email is crafted to: bob.james.jnr@business-emai.com
$mappings[0]->data = serialize($data);
 
// Its almost foolproof, I haven't factored in the potential for duplicated usernames as we only have 150 employees
// This should be enough to capture new employees I I may have overlooked
// $mappings[0] is the first sub-array (stdClass object) in the master $mappings array.

So using the code cleaning and manipulation above I am able to auto-register new users into joomla with a predefined password hash, name, username and email by auto activation by just asking a person the write thier name in my form and submit..

The Mappings arrays
Array
(
    [0] => stdClass Object
        (
            [id] => 4
            [formId] => 3
            [connection] => 0
            [host] => 
            [port] => 3306
            [username] => admin
            [password] => secret
            [database] => 
            [method] => 0
            [table] => joomla_users
            [data] => a:8:{s:2:"id";s:16:"{last_insert_id}";s:4:"name";s:14:"Bob James Jnr";s:8:"username";s:14:"bob.james.jnr";s:5:"email";s:38:"bob.james.jnr@business-name.com";s:8:"password";s:60:"$2y$10$vQMqQAaSm6p17QtLqUMEPu79edGi5GQWr8ezgMyY0ilpUT5nOYPXS";s:12:"registerDate";s:19:"{global:date_added}";s:13:"lastvisitDate";s:19:"0000-00-00 00:00:00";s:13:"lastResetTime";s:19:"0000-00-00 00:00:00";}
            [wheredata] => a:0:{}
            [extra] => a:0:{}
            [andor] => a:0:{}
            [ordering] => 1
        )
 
    [1] => stdClass Object
        (
            [id] => 5
            [formId] => 3
            [connection] => 0
            [host] => 
            [port] => 3306
            [username] => admin
            [password] => secret
            [database] => 
            [method] => 0
            [table] => joomla_user_usergroup_map
            [data] => a:2:{s:7:"user_id";s:16:"{last_insert_id}";s:8:"group_id";s:1:"2";}
            [wheredata] => a:0:{}
            [extra] => a:0:{}
            [andor] => a:0:{}
            [ordering] => 2
        )
 
)

This served my purpose as my site is locked to public and only accessible by people in my business (register) all having a name, user.name and This e-mail address is being protected from spambots. You need JavaScript enabled to view it pattern.

I did play with the registration plugin but my needs are a little different from other so I need a custom solution.
Last Edit: 9 years 11 months ago by lee3.
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!