Forums

I'm having a great time making forms and have a couple working (for which, thanks). The latest challenge is more advanced and I've not encountered a related thread in the forums.

I've got a sort of an event booking form where the user specifies the number of people being registered. The usableforms.js package is used to show/hide sections of the forms according to the number of people selected. So if 3 people are registered at once 3 sections with name/email/phone/etc show up below the main part of the registration info.

What I'm struggling with is how to put each of those three people into separate records in the database (in this case, 1 record for the person doing the registration and [e.g] 2 more records for the individual info from the other people). One form, create three records with non-duplicate information.

One approach I'm studying is to have, in this case, 3 forms on the page that all do a Submit from a single button at the bottom. A second approach is make it all one form and have the code do three post to the database. This last seems most promising. It would involve copying the autogenerated code and making 3 lists of fields; one for each section (person) - each with its own list of field names.

A third approach is to have an array of each field and then, maybe, the existing code will post multiple records to the DB. Thus begging the question, does Chronoforms now deal well with fields that are arrays? That is, if I have fields called name, email & phone does Chronoforms elegantly deal with me having name[1], name[2], name[3], email[1], email[2] and so on?

Which of these approaches (or another) is most promising? Or any leads to existing information?

Thanks... willson (Spain)

Hi willson,

I think that what I would do with this is to abandon the ChronoForms database saving code (the AutoGenerated code tab) and write my own. That gives you complete freedom over the coding and saving.

Yes you can easily collect data in arrays and that makes it convenient to manipulate and save using for loops in the OnSubmit code.

There's an example of a similar kind of event booking form here. The number of 'team members' changes with a dropdown and all the data is collected in arrays with names like name="su_form[1][first_name]"

In this case I sort and pack the data into a couple of database fields, then later unpack it into two pages of an Excel Spreadseet, one for the team and the other for the members.

Hope this helps a bit.

Bob

That example is a good match for what I'm up to. The form has a fixed part and a n * people part. I hadn't thought of putting the name/email/phone/etc into arrays (e.g. first_name[]). That might make things easier for me. Thanks.
I see that a key trick is that each field has an id that is absolutely unique; thus allowing it to easily referenced programmatically; I'll be getting into the habit of doing that consistently in my forms from now on.

I noticed also that your javascript (form.js) gives a good example of setting and resetting the validation states of the fields as they appear or disappear from the form (using the above mentioned id). I'll be filching that model as well.

Normally array variables would end up in the db as comma separated values in a single field - if I understand correctly (that being the default cf array variable handling). Your stated approach of storing in the db as-is and (de)constructing it when extracting to excel sounds interesting. I was going for an approach that would create the "person" records from the get go. Each has its merits.

Would you be inclined to share the excel extract code too?

Meanwhile I'm going to write the form with array variables and see where that gets me.

Hi willson,

I'm slowly getting to learn that unique ids can be really valuable in managing form data - especially when it comes to working with JavaScript. In this case it's pretty simple - the member panels are generated with a php for loop so all the indexes are inserted automatically:

// Create and show the team member fields.
// JavaScript changes the 'display' attribute (and validation classes)
jimport('joomla.presentation.pane');
$pane   =& JPane::getInstance('sliders');
echo "Please click on the title bars below to open the form for each team member";
echo $pane->startPane("members");
$display = 'block';
$class = $required;
$class_2   = '';
$title = "Team Leader";
for ( $i=1; $i <= $team_size_max; $i++ ) {
    echo "<div id='fileDiv_$i' style='display: $display;'>";
    echo $pane->startPanel( $title, "panel$i" );
    echo "
    <fieldset style='border-left:1px solid silver;'>

    <div class='oneField' ><label for='f".$i."_title' class='preField' >
    Title </label>
    <select id='f".$i."_title' name='su_form[".$i."][title]'
    class='optional' style='width:50px;' >
    ".implode(' ', $title_option_array[$i])."
    </select>
    </div>
. . .
    <div class='oneField' ><label for='f".$i."_terms' class='postField' >
    <input type='checkbox' id='f".$i."_terms' name='su_form[".$i."][terms]'
    class='$class inputRadio' value='ok' $check_tc[$i] $readonly />
    Terms and Conditions <span class='reqMark'>*</span></label>
    $hidden_tc[$i]
    <div class='instructions' >To agree with the
    <a id='href_97832983' class=''
    onclick=\"if( !hs.htmlExpand(this,
    { objectType:'iframe', outlineType:'rounded-white', width:800, height:400,
    captiontext:'Terms & Conditions' } )) return false;\"
    href='http://heartsandheroes.co.uk/registration-information/41?tmpl=component'>
    Terms & Conditions</a> please tick here.</div></div>
    </fieldset>";

  if ( $i >= max($team_count, $team_size_min) ) {
      $display = 'none';
      $class = $class_2 = 'optional';
  }
  echo $pane->endPanel();
  echo "</div>";

  $title = "Team Member ".($i+1);
}

I agree with you about the database v's Excel, in this case the end client was only interested in the Excel so the database is used as a temporary (and backup) storage and it was simpler to use two fields in the team record rather than thirty odd. I actually re-packed the form data into these two fields using distinctive seperators:

$member_pack_array = array();
foreach ( $member_array as $k => $member ) {
    foreach ( $member as $km => $vm ) {
        if ( is_array($vm) ) {
            $member[$km] = implode(', ', $vm);
        }
    }
    $member_pack_array[$k] = implode(' | ', $member);
}
$member_pack_array = implode(' ## ', $member_pack_array);
foreach ( $team_array as $k => $v ) {
    if ( is_array($v) ) {
        $team_array[$k] = implode(', ', $v);
    }
}
$team_pack_array = implode(' | ', $team_array);

The Excel download is in a separate form that checks the database for new entries. It unpacks the packed records, writes the team entries to one Excel worksheet and the team member entries to a second sheet. The heavy liftig is done in the OnSubmitAfter code which is below. Note that I use a lot of array manipulation to re-add the field names and titles (which aren't stored in the db) and to assign each tem amamber a unique id to cross-reference between the two Excel sheets.

<?php
/* ensure that this file is called by another file */
defined('_JEXEC') or die('Restricted access');

?>
<?php
global $mainframe;
$user = & JFactory::getUser();
// Check the user is authorised (superadmin, admin or manager)
if ( ! in_array($user->gid, array('23' , '24' , '25')) ) {
    // $mainframe->redirect("index.php");
}
// Check the user is the same user as submitted the form
if ( !$user->id == JRequest::getVar('user_id','', 'post', 'string', '' ) ) {
    // $mainframe->redirect("index.php");
}
// get data from the post array
$table      = JRequest::getVar('table','', 'post', 'string', '' );
$cf_id_list = JRequest::getVar('cf_id_list','', 'post', 'string', '' );
$submit     = JRequest::getVar('submit','', 'post', 'string', '' );

// Check which submit button was used - Backup or Delete
if ( $submit == 'Backup' ) {
    // backup the selected records to an Excel spreadsheet
    $result = $database->getTableFields($table);
    // $table_fields = array_keys($result[$table]);
    // echo "<div class='debug'>result: ".print_r($result, true)."</div>";
    $team_field_array = array('team_id' => 'Team ID', 'team_name' => 'Name', 'team_size' => 'Size',
        'org_name' => 'Organisation', 'org_type' => 'Org Type' , 'category' => 'Categories',
        'walk_run' => 'Walk/Run', 'support' => 'Support Team', 'distance' => 'Distance',
        'camping' => 'Camping', 'party' => 'Party', 'medical' => 'Medical',
        'similar' => 'Similar Events', 'attract' => 'Attracted',
        'hear' => 'Heard', 'team_cost' => 'Fee due' );

    $member_field_array = array('team_id' => 'Team ID', 'member_id' => 'Member ID',
        'team_name' => 'Name', 'title' => 'Title', 'first_name' => 'First Name',
        'last_name' => 'Last Name', 'dob' => 'Date of Birth',
        'gender' => 'Gender', 'email' => 'Email', 't-shirt' => 'T-shirt',
        'address1' => 'Address 1', 'address2' => 'Address 2', 'city' => 'City',
        'county' => 'County', 'postcode' => 'PostCode', 'phone_m' => 'Mobile',
        'phone_w' => 'Work phone', 'phone_h' => 'Home phone',
        'data_prot' => 'Data Protection', 'terms' => 'Terms' );
    $sql = "
        SELECT *
            FROM $table
            WHERE cf_id IN ($cf_id_list)
                AND backup = 'false';";
    $database->setQuery($sql);
    $datarows = $database->loadObjectList();
    $data_array = array();
    $i = $j = $member_count = 0;
    $member_array = array();
    foreach ( $datarows as $row ) {
        // unpack team info and put into team array
        $team_array[$j] = explode( ' | ', $row->team_array );
        array_unshift($team_array[$j], $row->cf_id);
        $team_array[$j] = array_combine(array_flip($team_field_array), $team_array[$j]);
        // unpack the member info
        $member_pack_array[$j] = explode( ' ## ', $row->member_array );
        // loop through the member info
        foreach ( $member_pack_array[$j] as $team_member ) {
            $member_count++;
            $member_array[$i] = explode(' | ', $team_member);
            // add 'fixed' data to each member
            array_unshift($member_array[$i], $row->team_name);
            // give each member a unique identifier
            array_unshift($member_array[$i], $row->cf_id."-".$member_count);
            array_unshift($member_array[$i], $row->cf_id);
            // set values into array
            // NB array_flip uses the values of the array as keys here
            $member_array[$i] = array_combine(array_flip($member_field_array), $member_array[$i]);
            // capitalise title field
            $member_array[$i]['title'] = ucwords($member_array[$i]['title']);
            $i++;
        }
        $member_count = 0;
        $j++;
    }
    // setup team info for export
    array_unshift($team_array, $team_field_array);
    $sheet_data['teams'] = $team_array;
    // setup member info for export
    array_unshift($member_array, $member_field_array);
    $sheet_data['members'] = $member_array;
    // call export function
    writeSpreadSheet($sheet_data);

} elseif  ( $submit == 'Delete' ){
    // Delete the records from the database
    $sql = "
        UPDATE $table
            SET backup = REPLACE(backup, 'false', 'true')
            WHERE cf_id IN ($cf_id_list);";
    $database->setQuery($sql);
    if (!$database->query()) {
        $mainframe->enqueuemessage($database->getErrorMsg(), 'error');
    } else {
        $mainframe->enqueuemessage("Records were marked as backed up.");
    }
    echo "All done!";
} else {
    $mainframe->redirect("index.php");
}
/**
 * Write the selected records to an Excel spreadsheet
 * This is an edited copy of the function from ChronoForms admin
 *
 * @param array $table_fields the table fields to write
 * @param object list $datarows the data to write
 */
function writeSpreadSheet($sheet_data)
{
    include_once JPATH_BASE.DS.'administrator'.DS.'components'
        .DS.'com_chronocontact'.DS.'excelwriter'.DS.'Writer.php';
    $xls = & new Spreadsheet_Excel_Writer();
    $xls->send("Hearts+Heroes_" . date('j_n_Y') . ".xls");
    $format = & $xls->addFormat();
    $format->setBold();
    $format->setColor("blue");


    $sheet_t = & $xls->addWorksheet('Teams at '.date("m-d-Y"));
    $table_fields = array_shift($sheet_data['teams']);
    $titcol = 0;
    foreach ( $table_fields as $table_field ) {
        $sheet_t->writeString(0, $titcol, $table_field, $format);
        $titcol ++;
    }

    $datacol = 0;
    $rowcount = 1;
    foreach ( $sheet_data['teams'] as $datarow ) {
        //echo "<div class='debug'>datarow: ".print_r($datarow, true)."</div>";
        foreach ( array_keys($table_fields) as $table_field ) {
            //echo "<div class='debug'>datarow[$table_field]: ".print_r($datarow[$table_field], true)."</div>";
            $sheet_t->writeString($rowcount, $datacol, $datarow[$table_field], 0);
            $datacol ++;
        }
        $datacol = 0;
        $rowcount ++;
    }

    $sheet_m = & $xls->addWorksheet('Members at '.date("m-d-Y"));
    $table_fields = array_shift($sheet_data['members']);
    $titcol = 0;
    foreach ( $table_fields as $table_field ) {
        $sheet_m->writeString(0, $titcol, $table_field, $format);
        $titcol ++;
    }

    $datacol = 0;
    $rowcount = 1;
    foreach ( $sheet_data['members'] as $datarow ) {
        //echo "<div class='debug'>datarow: ".print_r($datarow, true)."</div>";
        foreach ( array_keys($table_fields) as $table_field ) {
            //echo "<div class='debug'>datarow[$table_field]: ".print_r($datarow[$table_field], true)."</div>";
            $sheet_m->writeString($rowcount, $datacol, $datarow[$table_field], 0);
            $datacol ++;
        }
        $datacol = 0;
        $rowcount ++;
    }
    $xls->close();
    exit();
}
/**
 * Function to emulate array_combine in PHP4
 *
 * @param array $a keys
 * @param array $b values
 * @return array
 */
function array_combine($keys, $values)
{
   if ( version_compare(PHP_VERSION, '5.0.0', '>=' )) {
       return array_combine($keys, $values);
   }
   if ( !is_array($keys) || !is_array($values) || !count($keys) || !count($values) ) {
      user_error(__FUNCTION__ . ': non-array or empty array supplied as parameter');
      return(FALSE);
   }
   if ( count($keys) != count($values) ) {
      user_error(__FUNCTION__ . ': arrays are not equal in length');
      echo "<div class='debug'>count($keys): ".print_r(count($keys), true)."</div>";
      echo "<div class='debug'>count($values): ".print_r(count($values), true)."</div>";

   }
   $result = array();
   while(($key = each($keys)) && ($val = each($values)))
   {
      $result[$key[1]] = $val[1];
   }
   return($result);
}
?>

Hope this helps!

Bob

Thanks. Y'all are quite generous - even to us "free" users (of which I was one until 10 minutes ago; just sprung for the license).

I noticed a bugfix for multiple arrays mentioned in the Bug forum
http://www.chronoengine.com/forums.html?cont=posts&f=3&t=12242

$('fieldsnames').value = fieldsnames_array.join(',').replace(/\[\]/g,"");
$('fieldstypes').value = fieldstypes_array.join(',').replace(/\[\]/g,"");

replacing near identical lines in admin.chronocontact.html.php

So I'm guessing I ought to implement that too.

Hi willson,

I guess so, the code I posted was written using the old version of ChronoForms - back when you could edit the AutoGenerated code tab. You can't do that in the current version so I guess that I'd turn off the automatic db save and write my own.

Bob

PS You're welcome to the code; and, on Max's behalf, thanks for buying the the license