Forums

Heya Folks,

Now that I have my (very large!) registration form, I need to be able to submit the data to multiple tables. Ive seen the code elsewhere on this forum to submit to an extra table - so I assume that will work with multiple tables as well? However I am considerig the following scenario. A key point is that I have two user types - dealers and customers, with some shared, and some unique fields.

PHP in relation to onsubmit code ?

example: I have a firstname and surname (in addition to name and username) field. I could add them to the jos_users, however I assume that I dont want to be adding them onto a core table.

If I do not add them to jos_users, then I need to add them to either jos_cse_dealers or jos_cse_customers based on the answer to one of the questions in the form. Is this as simple as an if statment in the on_submit code ? (would a variable set by a radio button be available to php in the onsubmit ?)

Linking customer/dealer records to the ID in jos_user table ?

Does the onsubmit code run after the autogenerated code has finished ? In other words will I be able to retrieve the ID for the new record to use it as the primary key in either my jos_cse_users or my jos_cse_dealers table ? I am going to need to link these together for profile pages etc later. However I dont want to have fields that are potentially editable (such as username) as the index, as if a record changes, then I could lose my link betwen tables!

I can see (I think!) how a post statement can insert a record, but how to insert the userid of the registered user - when this is the registration form being submitted!


Thanks as always,

/Miz

Hi Miz,
Quite a post, lets see if we can shed some light🙂

Submit to multiple tables:
This should work very well, just takes some custom code to send the extra SQL-queries.
The start would be to fetch the active instance of the JDatabase object, build your queries, load the query, and execute:

<?
$db = JFactory::getDBO();
$query = sprintf('INSERT INTO #__cse_dealers (`uid`, `field1`, `field2`) VALUES (%d, "%s", "%s")',
  $MyPlugins->cf_joomla_registration['user']->id,
  $db->Quote(JRequest::getString('field1')),
  $db->Quote(JRequest::getString('field2'))
);
$db->setQuery($query);
$db->query();
?>

You'll have to adjust your query to match your table and data. The $MyPlugins->cf_joomla_registration['user']->id data is retrieved from the 'Joomla Registration plugin', which I assume you are using for your registration. The sprintf command is used to separate the query from the actual values in (mho) a neat fashion. $db->Quote() is used to prevent any SQL injection attacks.

--
"however I assume that I dont want to be adding them onto a core table."
Well, you could, but then you'd have to alter the JTable object used for this table to match at a minimum. Personally, I'd strongly recommend against it.

--
"If I do not add them to jos_users, then I need to add them to either jos_cse_dealers or jos_cse_customers based on the answer to one of the questions in the form. Is this as simple as an if statment in the on_submit code ? (would a variable set by a radio button be available to php in the onsubmit ?)"
Yes. Or a switch.. or any other kind of conditional structure that does the trick. Roughly put, get the value (JRequest::getVar), test the value, if it's "true" - do this query, if not - do that one.

<?
...
if (JRequest::getString('type') == 'dealer')
{
  $query = sprintf('INSERT INTO #__cse_dealers (`uid`, `field1`, `field2`) VALUES (%d, "%s", "%s")',
    $MyPlugins->cf_joomla_registration['user']->id,
    $db->Quote(JRequest::getString('field1')),
    $db->Quote(JRequest::getString('field2'))
  );
} else {
  $query = sprintf('INSERT INTO #__cse_customers (uid, field) VALUES (%i, "%s")',
    $MyPlugins->cf_joomla_registration['user']->id,
    $db->Quote(JRequest::getString('field1'))
  );
}
$db->setQuery($query);
...
?>

--
"Does the onsubmit code run after the autogenerated code has finished?"
That is something you control to some degree!
In your form setup, there's a tab named "RunOrder". Here, you can set the order of the three blocks "Autogenerated", "Plugins", and "OnSubmit". However, if you are using the Joomla registration plugin, the user object (and thus it's id) as used in the code earlier, will only be available after the plugin block has ben run (not the autogenerated).

Thus, make sure "plugin" has a lower number to "OnSubmit", and the code posted above should do the trick with user id's.

/Fredrik

Thanks Hugely Fredrick,

It seems pretty logical, and I am sure you have given m enough to pick through! Its great than I can get the user id - going to make (later) life much easier.

The night is young let the coding commence!

I will post an update later tonight/tommorow, have a great weekend!

ok, firstly some progress, and an issue that I have half pinned down!

Essentially, the onsubmit code is running, with no visable errors, but the database is not being populated.

Heres the code:

<?
$db = JFactory::getDBO();

$db =& JFactory::getDBO();
if ($db->connected()) {
   echo 'you are successfully connected to the Joomla database';
}

echo $MyPlugins->cf_joomla_registration['user']->id;
echo $db->Quote(JRequest::getString('firstname'));

$query = sprintf('INSERT INTO #__cse_extendedusers (`uid`, `firstname`, `surname`, `reg_type`) VALUES ("%d", "%s", "%s", "%s")',
    $MyPlugins->cf_joomla_registration['user']->id,
    $db->Quote(JRequest::getString('firstname')),
    $db->Quote(JRequest::getString('surname')),
    $db->Quote(JRequest::getString('reg_type'))
);
$db->setQuery($query);
$db->query();
?>

During the course of attempting to debug, I have added a check that I am indeed connected to the db - that passes.
I have then tried to look at sprintf more closely, and decided to try to echo the elements. (I assume it will just fail if it cant map a variable).

echo $MyPlugins->cf_joomla_registration['user']->id; -- Appears to fail
echo $db->Quote(JRequest::getString('firstname')); -- Works

next step wa to 'printf($Query)' which gives me two things to look at.

INSERT INTO jos_cse_extendedusers (`uid`, `firstname`, `surname`, `reg_type`) VALUES (0, "'test'", "'test'", "'Customer'") is the output from the print - I dont think that "'somthing'" is correct ?

I also note that uid is 0 - I assume its getting nothing, and then setting it to 0 as its an integer ? (%d)

Off to check syntax first!

Most of it looks correct, however, the id should not be 0.
Could you add some line at the end with print_r($MyPlugins->cf_joomla_registration); to your form? And post the output

/Fredrik

Some more progress 😛

The code now looks like this:

[
<?
$db = JFactory::getDBO();

// Now add fields for all user types
$query = sprintf('INSERT INTO jos_cse_extendedusers (uid, firstname, surname, reg_type) VALUES (%d, %s, %s, %s)',
    $MyPlugins->cf_joomla_registration['user']->id,
    $db->Quote(JRequest::getString('firstname')),
    $db->Quote(JRequest::getString('surname')),
    $db->Quote(JRequest::getString('reg_type'))
);
printf($query);
if (!mysql_query($query))
  {
  die('Error: ' . mysql_error());
  }
print_r($MyPlugins->cf_joomla_registration);
$db->setQuery($query);
$db->query();
?>

By creating an error output for sql I found my biggest issue - a misnamed table.I can now get an insert, albiet with a '0'. I will go back and try to reintroduce the extra ' and " that I removed if they are needed.

I get no output from the print_r, unless I have a syntax issue ?

I am trying to google around the forums to find examples of $MyPlugins being used but havent found too much yet!

I also with above code have every insert happening twice - but thats probably my debug code. the ID of the last user 'ronald mcdonald' was 97, the printf looked like this: INSERT INTO jos_cse_extendedusers (uid, firstname, surname, reg_type) VALUES (0, 'Ronald', 'McDonald', 'Customer')

Thanks in advance!

The syntax for print_r is correct, although I forgot one vital piece of code... My bad!

Try the modification below (adding a definition of $MyPlugins):

<?
$MyPlugins =& CFPlugins::getInstance($MyForm->formrow->id);
$db =& JFactory::getDBO();
...

/Fredrik

aha! Some progress!

Firstline is the printf (showing the query in use), after that the printr.

INSERT INTO jos_cse_extendedusers (`uid`, `firstname`, `surname`, `reg_type`) VALUES (0, "'Zeus'", "'Surnameless'", "'Customer'")

Array ( [errors] => [user] => JUser Object ( [id] => 105 [name] => Zeus [username] => Zeus [email] => [email]z@cs.com[/email] [password] => 1a79c4b13cd05a5bcba9420ce31a8537:qHJZXgbjhcsCc8hFVdzJCp85iBwIPKue [password_clear] => zz [usertype] => [block] => [sendEmail] => 0 [gid] => 18 [registerDate] => 2009-10-10 00:14:08 [lastvisitDate] => [activation] => [params] => [aid] => 0 [guest] => 1 [_params] => JParameter Object ( [_raw] => [_xml] => [_elements] => Array ( ) [_elementPath] => Array ( [0] => /data01/uglybety/public_html/DEV/carsavingexpert_com/libraries/joomla/html/parameter/element ) [_defaultNameSpace] => _default [_registry] => Array ( [_default] => Array ( [data] => stdClass Object ( ) ) ) [_errors] => Array ( ) ) [_errorMsg] => [_errors] => Array ( ) [uname] => Zeus [firstname] => Zeus [surname] => Surnameless [email_1] => [email]z@cs.com[/email] [password_1] => zz [reg_type] => Customer [company_name] => [company_type] => [reg_number] => [vat_number] => [postcode] => [comp_add_organisation] => [comp_add_line1] => [comp_add_line2] => [comp_add_line3] => [comp_add_town] => [comp_add_county] => [comp_add_postcode] => [comp_add_country] => [trade_name] => [sales_name] => [sales_phone] => [sales_email] => [showroom_postcode] => [showroom_add_organisation] => [showroom_add_line1] => [showroom_add_line2] => [showroom_add_line3] => [showroom_add_town] => [showroom_add_county] => [showroom_add_postcode] => [showroom_add_country] => [alt_sales_name] => [alt_sales_phone] => [alt_sales_email] => [twitter_url] => [facebook_url] => [business_logo] => [business_profile] => [register] => Register [c0d323bfe8a9555ba25102efd8edf192] => 1 [1cf1] => 42d097ea38b28452f324a7b8186897fb [chronoformname] => register [password2] => zz ) [complete] => 1 )

The ID is indeed 105, and is now there in the array - however 0 is still inserted in the table it perhaps becuase its a string not an integer being passed from the array ? (skyballing here!)

I also note:

When I include the "" around %s it arrives in the database table quote - i.e. 'Zeus' (not Zeus) - I assume I can just take these quotes out again ?

When I use #__tablename it errors, I have to use jos_tablename

I have also tried ".$db->nameQuote('#__cse_extendedusers')." as per http://docs.joomla.org/How_to_use_the_database_classes_in_your_script with no joy.


I still get two inserts (two rows in the table) for evey submit - need to test taking out the debug code!

Cheers!

?Miz

'k
As such, we do have a valid JUser object in $MyPlugins->cf_joomla_registration['user']. I'm still baffled that the sprintf won't work properly. Just to double-check, the "$MyPlugins =& CFPlugins...." line is above the one where we're creating the query right?

Indeed, you do not need "" around the %s as we are using $db->quote().

Using #__tablename should work with the JDatabase classes, don't know why it does not. It will not, however, work with mysql_query().

The two rows is due to your mysql_query() command.

/Fredrik

Bingo !!

We now have the correct ID appearing in the table 🤣 (it only took me 6 hours or so! 😶 )

I changed the first %d to a %s, using the logic that maybe somthing in the way it was being called didnt recognise an int value - my db table is still an Int so that fine!

I now (hope!) I have done the hardest part, your comments above resolved the other issues.

Heres the exact code as it stands now - it should be easy to expand on this and add the logical conditions - but as its 1.00am that will be a tommorow job!

[php]
<?
$MyPlugins =& CFPlugins::getInstance($MyForm->formrow->id);
$db = JFactory::getDBO();

// Now add fields for all user types to jos_cse_extendedusers
$query = sprintf('INSERT INTO jos_cse_extendedusers (`uid`, `firstname`, `surname`, `reg_type`) VALUES (%s, %s, %s, %s)',
    $db->Quote($MyPlugins->cf_joomla_registration['user']->id),
    $db->Quote(JRequest::getString('firstname')),
    $db->Quote(JRequest::getString('surname')),
    $db->Quote(JRequest::getString('reg_type'))
);
$db->setQuery($query);
$db->query();
?>
[/php]

Thanks again for your time and assistance !!

/Miz

Hi Mizpah,

I'm sure that I posted a reply in here right back at the beginning but it's lost - sometimes I close the browser tab too soon :-(

I suggested the alternative JTable + bind/store method copying the ChronoForms code but this is equally good.

One little note, if you are using Joomla Registration then the new user Object is available after the registration and you can get the id with the usual getUser() + $user->id.

Bob

Bob, that was probably the Forum Gremlin being up to no good as usual😉

Althugh I usually prefer the JTable approach, I've found it causing more trouble than it's worth if you are trying to insert a new row with a pre-defined value for the primary key (in this case, most likely uid). Instead of doing an INSERT, you end up with an UPDATE doing nothing at all. I did a modified version for some fireboard work some time ago, but as I'm currently on the road, I don't have access to it at the moment.

/Fredrik

Fredrick / Bob,

Thanks for your help on this one. heres the final version which people are welcome to use if they can modify it for there own purposes.

[code=php]<span class="syntaxhtml"><br /><span class="syntaxdefault"><?<br />$MyPlugins