Insert array or multiple rows into DB?

This is driving me nuts.....
I am trying to build a form to enter scores for golf tournaments. It is dynamic based off of the event selected, ad the users who have signed up for the event. It is multi page, and I have the queries working perfectly to give me the correct users who joined for the event.

I need each result to be saved into a separate row in the DB. I set up a table with all the fields, connected the form to the table, I have "ChronoForms handle my posted arrays" set to yes, and nothing gets saved.

For simplification purposes, I set up a single page form, with the array set to a single event, to see if it was a multiple page issue. This still does not work.

Below is my code

<?php
// Get a database object
$db = JFactory::getDBO();
$query = "SELECT DISTINCT * FROM #__eventlist_register, #__users WHERE #__eventlist_register.event=9 AND #__users.id=#__eventlist_register.uid ORDER BY #__users.name ";
$db->setQuery($query);
$rows = $db->loadObjectList();
$num_rows = count($rows);
$event=9

?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<tr>
<td align="left"> </td>
<td align="left"> </td>
<td align="left"><strong>Name</strong></td>
<td align="left"><strong>Front 9</strong></td>
<td align="left"><strong>Back 9</strong></td>
<td align="left"><strong>Gross</strong></td>
<td align="left"><strong>Penalty</strong></td>
<td align="left"><strong>Net Score</strong></td>
<td align="left"><strong>Money</strong></td>
</tr>
<?php
foreach ($rows as $row) {
$player_name = $row->name;
echo '<tr>';
echo '<td align="left"><input type="hidden" name="event[]" value='.$event.' readonly></td>';
echo '<td align="left"><input type="hidden" name="id[]" value='.$row->id.' readonly></td>';
echo '<td align="left">'.$row->name.'</td>';
echo '<td><input name="front9[]" type="text" value="0" size="2"></td>';
echo '<td><input name="back9[]" type="text" value="0" size="2"></td>';
echo '<td><input name="gross[]"  value="0" size="3"></td>';
echo '<td><input name="pstroke[]" type="text"  value="0" size="2"></td>';
echo '<td><input name="net[]" type="text" value="0" size="3"></td>';
echo '<td><input name="money[]" type="text" value="0.00" size="4"></td>';
echo '</tr>';
}

?>
</table>

<div class="form_item">
<div class="form_element cf_button">
<input value="Submit" name="button_1" type="submit" />
</div>
<div class="cfclear"> </div>
</div>

How can I get each of the form to save as an individual row to the DB? I have been struggling for days over this.
Thanks in advance.

I have also tried putting the following in the On Submit before email

<?php
$num_queries = count($_POST['event']);
for($i=0;$i<$num_queries;$i++) {
	$field1 = mysql_real_escape_string($_POST['event'][$i]);
	$field2 = mysql_real_escape_string($_POST['id'][$i]);
	$field3 = mysql_real_escape_string($_POST['front9'][$i]);
	$field4 = mysql_real_escape_string($_POST['back9'][$i]);
	$field5 = mysql_real_escape_string($_POST['gross'][$i]);
	$field6 = mysql_real_escape_string($_POST['pstroke'][$i]);
	$field7 = mysql_real_escape_string($_POST['net'][$i]);
	$field8 = mysql_real_escape_string($_POST['money'][$i]);
	$sql ="INSERT INTO `jos_event_results` (`event`, `id` `front9`, `back9`, `gross`,`pstroke`,`net`,`money`) values ('$field1', '$field2', '$field3', '$field4', '$field5', '$field6', '$field7', '$field8')";
	$resultsql = mysql_query($sql);
}
?>

This is frustrating beyond belief. Please help.

Hi robkristie,

I'm not sure what data your form is returning. Please will you turn debug on (for the simple version at the moment) and copy and paste the $_POST array from the debug info you get when you submit the form.

I think it's probably going to be easier to gather the data in an array for each player so the inputs would look like:

echo '<tr>';
echo '<td align="left"><input type="hidden" name="result['.$row->id.'][event]" value='.$event.' readonly></td>';
echo '<td align="left"><input type="hidden" name="result['.$row->id.'][id]" value='.$row->id.' readonly></td>';
echo '<td align="left">'.$row->name.'</td>';
echo '<td><input name="result['.$row->id.'][front9]" type="text" value="0" size="2"></td>';
echo '<td><input name="result['.$row->id.'][back9]" type="text" value="0" size="2"></td>';
echo '<td><input name="result['.$row->id.'][gross]"  value="0" size="3"></td>';
echo '<td><input name="result['.$row->id.'][pstroke]" type="text"  value="0" size="2"></td>';
echo '<td><input name="result['.$row->id.'][net]" type="text" value="0" size="3"></td>';
echo '<td><input name="result['.$row->id.'][money]" type="text" value="0.00" size="4"></td>';
echo '</tr>';

Bob

Sorry, missed this due to the holiday

This is the data being returned

[list]Form passed first SPAM check OK
Form passed the submissions limit (if enabled) OK
Form passed the Image verification (if enabled) OK
Form passed the server side validation (if enabled) OK
$_POST Array: Array ( [chapter] => 6 [title] => 24 [button_1] => Submit [229a3bfec969c7a9b77c6d5443207b5c] => 1 [1cf1] => d68d8181811240f133629f059b8d8402 [chronoformname] => Mother [event] => Array ( [0] => 24 [1] => 24 [2] => 24 ) [id] => Array ( [0] => 170 [1] => 215 [2] => 217 ) [front9] => Array ( [0] => 45 [1] => 45 [2] => 47 ) [back9] => Array ( [0] => 45 [1] => 45 [2] => 47 ) [gross] => Array ( [0] => 90 [1] => 90 [2] => 94 ) [pstroke] => Array ( [0] => 0 [1] => 1 [2] => 0 ) [net] => Array ( [0] => 90 [1] => 91 [2] => 94 ) [money] => Array ( [0] => 1.25 [1] => 0.90 [2] => 0.65 ) [cfformstep] => 1 )
$_FILES Array: Array ( )
Form passed the plugins step (if enabled) OK
Debug End[/list]

Hi robkristie,

So the key data here is this:

[event] => Array ( [0] => 24 [1] => 24 [2] => 24 ) 
[id] => Array ( [0] => 170 [1] => 215 [2] => 217 ) 
[front9] => Array ( [0] => 45 [1] => 45 [2] => 47 ) 
[back9] => Array ( [0] => 45 [1] => 45 [2] => 47 ) 
[gross] => Array ( [0] => 90 [1] => 90 [2] => 94 ) 
[pstroke] => Array ( [0] => 0 [1] => 1 [2] => 0 ) 
[net] => Array ( [0] => 90 [1] => 91 [2] => 94 ) 
[money] => Array ( [0] => 1.25 [1] => 0.90 [2] => 0.65

Here's one version of the code to unpack this:

<?php
$db =& JFactory::getDBO();
$result_array = array('event', 'id', 'front9', 'back9', 'gross', 'pstroke', 'net', 'money');
foreach ( $result_array as $v ) {
  $$v = JRequest::getVar($v, array(), 'post', 'array');
}
$values = array();
foreach ( $id as $k => $v ) {
  $temp = array();
  foreach ( $result_array as $r ) {
    $temp[] = $db_quote($$r[$k]);
  }
  $values[] = "( ".implode(', ', $temp).")";
}
$query = "
  INSERT INTO `jos_event_results` 
    (`event`, `id`, `front9`, `back9`, `gross`, `pstroke`, `net`, `money`) 
    VALUES ".implode(', ', $values)."; 
  ";
$db->setQuery($query);
$db->query();

Not tested and will likely need debugging!!

Bob

Should I make this as a separate file, and then put that url in the submit url, or should I put this in the onSubmit code?

Hi robkristie,

Add it in the OnSubmit Before box.

Note; you need to have 'Send Emails' set to 'Yes' in the form General Tab to make sure this box is executed.

Bob

Yeah, I can't get this to work.
Is there any way I can move this thread to the "paid services" area? I would gladly pay to get this info saved into the DB.
Thanks

Hi robkristie,

You can make a post over there and link it to this thread.

Bob

I actually figured out a solution for this.
For some reason, in the code I had to set the DB connection, but it works.

For anyone else who is trying to so something similar, here is what I put in the OnSubmit code

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$selected = mysql_select_db("mga",$conn) 
  or die("Could not select examples");

$sql_start = 'INSERT INTO `jos_event_results` VALUES '; 
$sql_array = array(); 
foreach ($_POST['id'] as $row=>$name)
{
$id = $name;
$event = $_POST['event'][$row];
$front9 = $_POST['front9'][$row];
$back9 = $_POST['back9'][$row];
$gross = $_POST['gross'][$row];
$pstroke = $_POST['pstroke'][$row];
$net = $_POST['net'][$row];
$money = $_POST['money'][$row];
$sql_array[] = '("' . $id . '", "'.$event.'", "'.$front9.'", "'.$back9.'", "'.$gross.'", "'.$pstroke.'", "'.$net.'", "'.$money.'")'; 
if (count($sql_array) >= $queue_num) {
  $query_single=$sql_start . implode(', ', $sql_array);
  mysql_query($query_single); 
  $sql_array = array(); 
}
}

if (count($sql_array) > 0)  {
  $query = $sql_start . implode(', ', $sql_array);
  mysql_query($query)or die(mysql_error());
}
echo "completed";
?>

Thanks for you help.