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