Forums

Hi all,

I tried several methods but just cant get the desired result...

I have a registration form that is somewhat simple, but, in preparation for a more comprehensive "Profile" screen I wanted to create several almost blank records in a separate table.

I have a table "user_limits".

The structure is...
ul_index = autoincrement
ul_cf_user_id_link = to hold cf_user_id (from users.id)
ul_order = to order records sequentially always 1-4
ul_limit_min = min dollars
ul_limit_max = max dollars

The idea is to create 4 records at the same time as the registration is submitted so that the 4 records had this data...
(assume that the newly created user's id = 60)

ul_index= 1000(auto increment on insert) ul_cf_user_id =  60 ul_order 1 other fields = NULL
ul_index= 1001(auto increment on insert) ul_cf_user_id =  60 ul_order 2 other fields = NULL
ul_index= 1002(auto increment on insert) ul_cf_user_id =  60 ul_order 3 other fields = NULL
ul_index= 1003(auto increment on insert) ul_cf_user_id =  60 ul_order 4 other fields = NULL

I tried custom code..
I tried custom code (for the UL_ORDER field changing the value before each save to 1, then2, then 3, then 4) each followed by a db save. Like
custom code UL_ORDER =1
DBsave
custom code UL_ORDER =2
DBsave
custom code UL_ORDER =3
DBsave
custom code UL_ORDER =4
DBsave
I end up with only 1 record in the user_limits table???? So It is updating instead of inserting.

Please give me a hint or a push in the right direction.

The end result desired is to offer a table on the profile screen like this
+------+------+
|--Min-|--Max-|
|-0.00-|-0.00-| where this would be UL_ORDER = 1
|-0.00-|-0.00-| where this would be UL_ORDER = 2
|-0.00-|-0.00-| where this would be UL_ORDER = 3
|-0.00-|-0.00-| where this would be UL_ORDER = 4
+------+------+

Where the user can establish his own Min and Max values..

I see another question with this...
and that is once in the profile screen,
when the user enters the Max of (for example $50.00) the Min on the next line should calculate to $50.01. I think I can use the java together with the dom to do this, but... How would I display this (which event would accommodate this result?)

I know this is complicated so please answer any parts you can help me with.

Thanks in advance for your help.
MrFitz

Hi the_fitz,

You probably could do this with four DB Save actions but it looks to me like a case for hand-coding the MySQL query in a Custom Code action.

<?php
$db =& JFactory::getDBO();
$query = "
  INSERT INTO `user_limits` (`ul_cf_user_id_link`, `ul_order`, `ul_limit_min`, `ul_limit_max` )
    VALUES ( '{$cf_user_id}', 1, '', '' ),
      ( '{$cf_user_id}', 2, '', '' ),
      ( '{$cf_user_id}', 3, '', '' ),
      ( '{$cf_user_id}', 4, '', '' ) ;
";
$db->setQuery($query);
$db->query();
?>

Bob

Bob

Hey Bob, that worked great!

Now to the "Profile" page where the user must input that data...
I have the form events styled like this...

BUT it displays like this....


Notice the "Update" button, it is shown BEFORE the MultiRecord Loader
AND
It does not allow me to edit the fields in the table.

I'm sure that I am doing this incorrectly (or it would work) so what do you suggest I try?

Thanks in advance for your time.
MrFitz

Hi the_fitz,

That looks correct if you are using the DB Multi Record Loader Data Displayer. If you want to use a custom listing then use the DB Multi Record Loader to get the data and a Custom Element element in the Preview box to build a table to display it. That gives you control over the layout and the ability to add links, etc.

Bob

Hi Bob,

That worked like a champ. Thanks.
MrFitz

Hi Bob!
Here:
http://www.chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=26&t=63499&start=15#p317531
I've asked you about checkbox group.
Now I need to put records separately depending on number of checked values.
For example now checking some values gives me such result:

Age       Theme
37        Theme1,Theme2,Theme7
25        Theme2,Theme1
33        Theme2

But I'm seeking this result:

37   Theme1
37   Theme2
37   Theme7
25   Theme2
25   Theme1
33   Theme2

There could be from 1 to 8 checked options; so in DB must be so much separate records as it was checked.
How it could be done?
Thank you very much!

Hi 01,

I think that you will need to process the results in a Custom Code action to separate out the results that you need into separate values in the $form->data array.

You haven't shown exactly where this data comes from so its hard to be more specific.

Bob

Hi Bob!
Thank you for answer. Yes, I forgot to post an example.
The output data shown by debugger looks like this:

Array
(
    [option] => com_chronoforms
    [tmpl] => component
    [chronoform] => Survey
    [event] => submit
    [cf_sid] => 6c9002f98cb25b5d61b4410cd9ac5880
    [input_checkbox_group_14] => 0,3,6
    [input_textarea_19] => Comment1
    [input_textarea_20] => Comment2
    [input_textarea_21] => 
    [chrono_verification] => g3a7K
    [input_submit_22] => Send
    [518793005cc74ca3a6ccc80cfb43e477] => 1
    [_FORM_FILES_] => 
    [input_text_2] => Tom
    [input_text_4] => 35
    [input_text_6] => 
    [input_submit_7] => Next step
    [check_group_общение между мужчиной + женщиной] => value one
    [check_group_разные бытовые ситуации общения] => value two
    [check_group_общение с представителем другой культуры] => value three
)

The last three line are done by custom code:

..    $form->data['check_group_'.$k] = $v;
...

Hi 01,

Hmmm you will get into all kinds of problems with input names like this : check_group_общение между мужчиной + женщиной

Input names are used as PHP variable names and MySQL column names; they must only contain a-zA-Z0-9 and '_' as characters (no spaces or other special characters).

The code you've used was intended to give names like check_group_1, check_group_2, . . .

I still don't see anything like this in the Debugger output: output 37 Theme1,Theme2,Theme7 Where does that come from?

Bob

Understood! Thank you for your advice! Will fix it.
Sorry, I've wrote with mistakes (something has been changed in code). It should be like this in this case:

  
...
[input_text_4] => 37  
...
[check_group_общение между мужчиной + женщиной] => Theme1
[check_group_разные бытовые ситуации общения] => Theme2
[check_group_общение с представителем другой культуры] => Theme7

Hi 01,

I've tried to write some sample code to do this but it just doesn't make sense to me, sorry :-(

There is something odd about the way the form is constructed and I just can't see how it is supposed to work.

Bob

Shall I attach a form example? Or it won't make any sense?

Hi 01,

A full debug listing should be enough once you have sorted out the checkbox group names.

Bob

Hi Bob!
I was too long in off-line.
I tried to make some changes and got a working code but not till the end. Firstly the debug info:

Array
(
    [option] => com_chronoforms
    [chronoform] => Survey
    [event] => submit
    [cf_sid] => eeb477923c6fba2cc32c97e43ad0d231
    [themes] => 0,4,5
    [theme_1] => as
    [theme_2] => ds
    [theme_3] => 432
    [chrono_verification] => ybt2z
    [input_submit_22] => Send
    [629f4f19a67cb2973aede4e6e9cb195c] => 1
    [_FORM_FILES_] => 
    [name] => Test
    [age] => 45
    [activity] => 
    [input_submit_7] => Next step
)

And custom php code:

$age = JRequest::getVar('age', array(), 'post', 'array');
$themes = JRequest::getVar('themes', array(), 'post', 'array');
$db =& JFactory::getDBO();
$query = "INSERT INTO `#__chronoforms_data_survey3` (`age`, `theme`) VALUES ";
$values = array();
foreach($theme in $themes) {
    $value = "( '{$age}', '{$theme}' )";
	array_push($values, $value);
}
$query .= implode(",", $values);
$db->setQuery($query);
$db->query();

As [themes] array has 3 values(selected values in check group) the result
must be a 3 separate rows in DB table. The demo as it should be:
[attachment=0]test2.jpg[/attachment]
But I've got only one record😟
The result:
[attachment=1]test1.jpg[/attachment]
Can you suggest me something?
Thank you!

Hi 01,

Well done :-)

Bob

HI,
Your code below seems wrong!!!
I'm not exactly sure what you are trying to do but I see clearly that the

$query = "INSERT INTO `#__chronoforms_data_survey3` (`age`, `theme`) VALUES ";

and the

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

Should be inside the "foreach" so that it runs an insert operation once for each iteration.
Also..
There are no variables stated as the VALUES in the $query.
it should be more like ...

$query = "INSERT INTO `#__chronoforms_data_survey3` (`age`, `theme`) VALUES ($age,$theme)";

Hope that helps....
Tom

And custom php code:

$age = JRequest::getVar('age', array(), 'post', 'array');
$themes = JRequest::getVar('themes', array(), 'post', 'array');
$db =& JFactory::getDBO();
$query = "INSERT INTO `#__chronoforms_data_survey3` (`age`, `theme`) VALUES ";
$values = array();
foreach($theme in $themes) {
    $value = "( '{$age}', '{$theme}' )";
	array_push($values, $value);
}
$query .= implode(",", $values);
$db->setQuery($query);
$db->query();

As [themes] array has 3 values(selected values in check group) the result
must be a 3 separate rows in DB table. The demo as it should be:
[attachment=0]test2.jpg[/attachment]
But I've got only one record😟
The result:
[attachment=1]test1.jpg[/attachment]
Can you suggest me something?
Thank you![/quote]

Hi 01,

Sorry, I misunderstood your post - and I did read it more than once. The Fitz is right - your MySQL will create multiple records, one for each set of values.

Bob

Hi all!
Thank you for you replies!
I've reconstructed the SQL and without Joomla and Chronoforms it should work, but now it still works in ordinary way(inserting only one string)
Am I missing something? For example cf_uid or something else?

The code:

$age = JRequest::getVar('age', array(), 'post', 'array');
$themes = JRequest::getVar('themes', array(), 'post', 'array');
$db =& JFactory::getDBO();
$query = "INSERT INTO `#__chronoforms_data_survey3` ( `age`, `themes`) VALUES ";
$values = array();
$themes = explode(",", $themes);
foreach($themes as $theme) {
    $value = "( '{$age}', '{$theme}' )";
	array_push($values, $value);
}
$query .= implode(",", $values);
$db->setQuery($query);
$db->query();

And constructed SQl looks like :

INSERT INTO `gniyr_chronoforms_data_survey3` (`age`, `themes`) VALUES ( '58', '0' ),( '58', '3' ),( '58', '4' ),( '58', '7' ) 

Assuming as debug info looks like this:

Array
(
    [option] => com_chronoforms
    [chronoform] => Survey
    [event] => submit
    [cf_sid] => 367b35af7289c5100609389db7210d7f
    [themes] => 0,3,4
    [theme_1] => 
    [theme_2] => 
    [theme_3] => 
    [chrono_verification] => vqzz5
    [input_submit_22] => Отправить
    [7e007d019339f4f1bd1c81556a62498d] => 1
    [_FORM_FILES_] => 
    [name] => Tom
    [age] => 58
    [activity] => 
    [input_submit_7] => Next step
)

P.S. I've left these columns in DB:
"cf_id", "cf_created" and "cf_ipaddress" but haven't found anything in forum how to insert values in them.

I just have opened the Joomla's debug and found that the SQL query isn't changed by the
custom code 😲

INSERT INTO `gniyr_chronoforms_data_survey4`
(`cf_created`,`cf_created_by`,`cf_ipaddress`,`name`,`age`,`themes`,`theme_0`,`theme_1`,`theme_2`,`theme_3`) 
  VALUES 
('2013-10-24 21:18:51','0','127.0.0.1','Name','58','3,4,7','Other','12','2','3')

Here is the order of events:
[attachment=0]Untitled-1.png[/attachment]

Hi 01,

I'm still pretty confused by this :-(

Your Custom Code will not have any effect on the ChronoForms DB Save. As written the two are completely separate.

If your Custom Code isn't saving the records you need then it needs to be Debugged. I'd start by outputting $query to make sure that it is what you intend.

'cf_id' is an automatically generated primary key
'cf_ud' is a random string
'cf_created_by' is the current User ID
'cf_ip_address' is the users IP address

None of these are essential.

Bob



Bob

Hi!
Thank you for reply!
I had some minor errors and that caused the problem. Now everything works