Each form row into it's own MySQL row?

I created the post in the below forum. The post that Dr John did is what would be our ideal situation.

http://www.sitepoint.com/forums/showthread.php?p=4791068

Regarding each row in the form, can that be set so that each row is dumped into separate rows in the MySQL table - instead of now it dumps into one long row per submission. And have a way to make each submitter's submissions unique, such as have their email in each row, so that scripting could be used to pull all the submitter's submissions into a PHP/HTML table?

Each student will have their own login if that helps.

Thank you for any help or a push in the right direction,
Wesley

Hi Wesley,

I can't understand what you need, can you show me some example data ?

Regards,
Max

Hi Max, below is the current form. The needed end result is the students (who are my client's clients) submit the form, that submission data goes into the database table, and then is pulled into a PHP/HTML table that my client can print out to put in the student's folder.

Our initial thought is the students would keep their college contact tally and at the end submit the form only once, the problem is currently the form submission is dumped into one MySQL table row at a time and a student can have 25 contacts or more so I'm not able to create a form that has 25 rows due to when I try to create a SQL table I get the "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs" error.

The ideal situation is a student can submit the form multiple times instead and that info be able to be pulled into a PHP/HTML table, knowing to pull all the submissions of a particular student.

In the above post at Sitepoint, Dr John had a good idea of having each form row create it's own MySQL table row but I'm not sure how to have each row of my below form go into it's own MySQL table row, and it also have a unique value so that I can then use that unique value to combine a student's submissions into one table.



Please let me know if I'm still not explaining it correctly, thanks!,
Wesley

Hi Wesley,

I think I understand you now, you need 2 tables, a "students" table which would be the "basic" fields like "id" or "cf_id"..etc + any student info like "name" and "email".

then you need a second table called for example "student_data", this 2nd table will have all the fields can be seen in the screenshot you attached, I think 10 fields ? + some primary key field, like "id" or "cf_id" + a field named "student_id" to assign any record to the "students" table, now every student should have 1 record at the students table and may have 0 or more records at the "students_data" table, good ?

here is how to do this, you will need Chronoforms V4 RC1.2 installed and working.

Your form code (according to the screenshot) will have 2 fields for the "name" and "email" (students table), then say 10 fields field1, field2, field3..etc (students_data table), your form fields name will be "name" and "email" + an array of fields for the others, how ? by attaching a "[]" to the field name, so its expected to have 25 fields named "field1[]" and 25 fields named "field2[]"..etc

save your form code in a new form then go to the wizard, drag a "show HTML" action to the "onLoad" event, and in the "on submit" you will drag 2 things, a "DB Save" action, click the "config" icon and select your "students" table, insert the "model id" as "student", then click "apply", then drag a "Custom code" action to the onsubmit event, in the "Custom code", show the "code" box by clicking the "show/hide" link and insert this code:

<?php
//get the student_id
$form->data['student_id'] = $form->data['student_id']; // this depends on the primary key of your students table, if its "id" and the Model id was "student" then it will be like above.
//start the loop
$mydata = $form->data;
foreach($mydata['field1'] as $k => $v){
$form->data = $mydata;
$form->data['field1'] = $mydata['field1'][$k];
$form->data['field2'] = $mydata['field2'][$k];
//then all the fields here
//start the save process
$db_save_details = new stdClass();
$db_save_details->type = 'db_save';
$db_save_details->params = 'table_name=YOUR_TABLE_NAME_HERE'; //insert your table name
$form->runAction($db_save_details);
//end the save process
}
$form->data = $mydata;
?>

That's it, that should save the student name, email in the 1st table and all his details in the 2nd table, I wish that was helpful🙂

Regards,
Max

Thanks Max, this is exactly what I was needing!! The field "[]" is awesome! Pulling up my form now to give it a try.

Thanks again and for your time!,
Wesley

I'm sorry Max, I've been working on this since I last wrote trying to figure it out myself but can't get past an initial error:
Warning: Invalid argument supplied for foreach() in /components/com_chronoforms/form_actions/custom_code/custom_code.php(15) : eval()'d code on line 6

Line 6 starts with the foreach($mydata

Working with Chronoforms V4 RC1.2 I've created a simple form to get it working, and then will apply it to my big form, with the fields (playing off your example script): name, email, and two rows of field1[], field2[] (as below).


I have two MySQL tables:

Table: form_student_info
cf_id (primary)
name
email

Table: form_student_data
cf_id (primary)
field1
field2
student_id

In 'DB Save':
Table: form_student_info
Model ID: student

My current onsubmit Custom Code:

<?php
//get the student_id
$form->data['student_id'] = $form->data['student_cf_id']; // this depends on the primary key of your students table, if its "id" and the Model id was "student" then it will be like above.
//start the loop
$mydata = $form->data;
foreach($mydata['field1'] as $k => $v){
$form->data = $mydata;
$form->data['field1'] = $mydata['field1'][$k];
$form->data['field2'] = $mydata['field2'][$k];
//then all the fields here
//start the save process
$db_save_details = new stdClass();
$db_save_details->type = 'db_save';
$db_save_details->params = 'table_name=form_student_data'; //insert your table name
$form->runAction($db_save_details);
//end the save process
}
$form->data = $mydata;
?>

Could I get a little more help on this please, thank you!!,
Wesley

Hi hominid4,

I have no experience in doing this with CF v4 so can't help much :-(

The error message suggests that $mydata['field1'] isn't an array. I'd add a line of debug code to display the contents of &mydata to see exactly what is there. This ought to work OK:

$mainframe->enqueuemessage('$mydata: '.print_r($mydata, true));

Bob

Hi Bob, I'm sorry, when I insert that code it receives a:

Fatal error: Call to a member function enqueuemessage() on a non-object in /administrator/components/com_chronoforms/form_actions/custom_code/custom_code.php(15) : eval()'d code

I've inserted it in my above PHP code, is that correct?

Thank you!,
Wesley

Hi wesley,

Sorry, you need global $mainframe; on the preceding line to make the method available.

Bob

That's a neat little script, thanks.

This is the results it returned:

$mydata: Array ( [name] => Wesley [email] => [email]wesley@myemail.com[/email] [field1] => Field One A Test, Field One B Test [field2] => Field Two A Test, Field Two B Test [input_submit_3] => Submit [student] => Array ( [name] => Wesley [email] => [email]wesley@myemail.com[/email] [field1] => Field One A Test, Field One B Test [field2] => Field Two A Test, Field Two B Test [input_submit_3] => Submit ) [student_cf_id] => 17 [student_id] => 17 )

Hi hominid4,

Indeed $mydata[field1] is a comma separated list and not an array.

[field1] => Field One A Test, Field One B Test

You can convert it back to an array with

$mydata['field1'] = explode(', ', $mydata['field1']);

Bob

Good deal, that's now allowing the form to get past that error and dump into the 'form_student_data' table, I'm getting closer!🙂 However it's not putting in the inputs into that table, the 'cf_id' is correct, field1 is blank, and then NULLs in field2 and student_id:

cf_id || field1 || field2 || student_id
1 || blank || NULL || NULL

After submitting the form I get no errors, if I turn on 'Error Reporting: Maximum' I get:

Notice: Undefined variable: mydata in /administrator/components/com_chronoforms/form_actions/custom_code/custom_code.php(15) : eval()'d code on line 5

Notice: Undefined index: field2 in /administrator/components/com_chronoforms/form_actions/custom_code/custom_code.php(15) : eval()'d code on line 9

Line 5: $mydata['field1'] = explode(', ', $mydata['field1']);
Line 9: $form->data['field2'] = $mydata['field2'][$k];


Sorry for stretching this thread out!,
Wesley

Hi hominid4,

You need similar code to explode the field2 data back into an array I think.

There is something odd with $mydata though, it appears to have a second copy of the data as a 'student' array inside itself.

$mydata: Array ( 
  [name] => Wesley 
  [email] => wesley@myemail.com 
  [field1] => Field One A Test, Field One B Test 
  [field2] => Field Two A Test, Field Two B Test 
  [input_submit_3] => Submit 
  [student] => Array ( 
    [name] => Wesley 
    [email] => wesley@myemail.com 
    [field1] => Field One A Test, Field One B Test 
    [field2] => Field Two A Test, Field Two B Test 
    [input_submit_3] => Submit 
  ) 
  [student_cf_id] => 17 
  [student_id] => 17 
)

Puzzled . . .

Bob

I'm sorry, wish I could be more help, maybe Max can help elaborate on the $mydata. The idea is for cf_id,name,email dump into table "form_student_info", and cf_id,field1,field2,student_id" dump into table "form_student_data". With the current code it seems to be doing that correctly, the "form_student_info" is populated correctly, just when it gets to the student data table is the error.

My current code:

<?php
//get the student_id
$form->data['student_id'] = $form->data['student_cf_id']; // this depends on the primary key of your students table, if its "id" and the Model id was "student" then it will be like above.
//start the loop
$mydata['field1'] = explode(', ', $mydata['field1']);
foreach($mydata['field1'] as $k => $v){
$form->data = $mydata;
$form->data['field1'] = $mydata['field1'][$k];
$form->data['field2'] = $mydata['field2'][$k];
//then all the fields here
//start the save process
$db_save_details = new stdClass();
$db_save_details->type = 'db_save';
$db_save_details->params = 'table_name=form_student_data'; //insert your table name
$form->runAction($db_save_details);
//end the save process
}
$form->data = $mydata;
?>

Thank you!,
Wesley

Hi Max, would you by chance have a second before the weekend to help with this? My client keeps asking me when the form will be functional and was needing to have it going this weekend.

Thank you!!,
Wesley

Hi Wesley,

Sorry not to be more help. I'm just not familiar enough with the v4 code. Do send Max a message through the Contact Us form here.

Bob

Thanks Bob, I contacted Max and he fixed me right up!! Below is the code that works:

<?php
//get the student_id
$form->data['student_id'] = $form->data['student_cf_id'];
$mydata = $form->data;
foreach($mydata['field1'] as $k => $v){
	$form->data = $mydata;
	$form->data['field1'] = $mydata['field1'][$k];
	$form->data['field2'] = $mydata['field2'][$k];
	//start the save process
	$db_save_details = new stdClass();
	$db_save_details->type = 'db_save';
	$db_save_details->params = 'table_name=form_student_data';
	$form->runAction($db_save_details);
	//end the save process
}
$form->data = $mydata;
?>

Thanks to both of you!!,
Wesley