Need help inserting multiple records to a table

[deeciple]

Hi All,

I have the following javascript code to dynamically create a table and populate it with data. Each time the user presses the "Add Row" button on my form, the addrow function is called and a row containing their data is added to the table. Here is the code:

<SCRIPT language="javascript">   
function addRow(tableID) {
 if (!document.getElementById(tableID)) {

  var body = document.getElementsByTagName("fieldset")[2];
  var tbl = document.createElement("table");
  var tblBody = document.createElement("tbody");
  
  tbl.appendChild(tblBody);
  body.appendChild(tbl);

  tbl.setAttribute("id", "dataTable");
  tbl.setAttribute("border", "0");
  }

 var StartTime = document.getElementById('txtStartTime');
 var EndTime = document.getElementById('txtEndTime');
 var MaterialID = document.getElementById('txtMaterialID');
 var Title = document.getElementById('txtTitle');    
 
 var table = document.getElementById(tableID);    
 
 var rowCount = table.rows.length;    
 var row = table.insertRow(rowCount);
 
 var cell1 = row.insertCell(0);
var element1 = document.createElement("input");
 element1.type = "checkbox";    
 cell1.appendChild(element1);    
 
 var cell2 = row.insertCell(1);
 var element2 = document.createElement("input");
 element2.className="ShortField";    
 element2.type = "text";
 element2.setAttribute("name","StartTime[]");
element2.setAttribute("readonly","true");
 element2.value = StartTime.value;    
 cell2.appendChild(element2);
 StartTime.value="";    
 
 var cell3 = row.insertCell(2);
 var element3 = document.createElement("input");
 element3.className="ShortField";    
 element3.type = "text";
 element3.setAttribute("name","EndTime[]");
element3.setAttribute("readonly","true");
 element3.value = EndTime.value;    
 cell3.appendChild(element3);
 EndTime.value=""; 

 var cell4 = row.insertCell(3);
 var element4 = document.createElement("input");
 element4.className="ShortField";    
 element4.type = "text";
 element4.setAttribute("name","MaterialID[]")
element4.setAttribute("readonly","true");
 element4.value = MaterialID.value;   
 cell4.appendChild(element4);
 MaterialID.value="";    
 
 var cell5 = row.insertCell(4);
 var element5 = document.createElement("input");
 element5.className="LongField";    
 element5.type = "text";
 element5.setAttribute("name","Title[]");
element5.setAttribute("readonly","true");
 element5.value = Title.value; 
 cell5.appendChild(element5);
 Title.value="";
 
var cell6 = row.insertCell(5);
 var element6 = document.createElement("input");
 element6.className="ShortField";    
 element6.type = "hidden";
 element6.setAttribute("name","row[]");
element6.setAttribute("readonly","true")
 element6.value = table.rows.length -1; 
 cell6.appendChild(element6);
}   
function deleteRow(tableID) {
if (document.getElementById(tableID)) {
 try {    
  var table = document.getElementById(tableID);    
  var rowCount = table.rows.length;    
  for(var i=0; i<rowCount; i++) {     
   var row = table.rows[i];     
   var chkbox = row.cells[0].childNodes[0];     
   if(null != chkbox && true == chkbox.checked) {      
    table.deleteRow(i);      
    rowCount--;      
    i--;     
   }    
  }    
 }catch(e) {     
  alert(e);    
 }   
}
}
function updateRow(tableID) {
 if (document.getElementById(tableID)) {

 var StartTime = document.getElementById('txtStartTime');
 var EndTime = document.getElementById('txtEndTime');
 var MaterialID = document.getElementById('txtMaterialID');
 var Title = document.getElementById('txtTitle');
 try {    
  var table = document.getElementById(tableID);    
  var rowCount = table.rows.length;    
  for(var i=0; i<rowCount; i++) {     
   var row = table.rows[i];     
   var chkbox = row.cells[0].childNodes[0];     
   if(null != chkbox && true == chkbox.checked) {      
    //table.deleteRow(i);
    chkbox.checked=false;
    row.cells[1].childNodes[0].value=StartTime.value;
    row.cells[2].childNodes[0].value=EndTime.value;
    row.cells[3].childNodes[0].value=MaterialID.value;
    row.cells[4].childNodes[0].value=Title.value;
   }    
  }
  StartTime.value="";
  EndTime.value="";
  MaterialID.value="";
  Title.value="";    
 }catch(e) {     
  alert(e);    
 }   
}
}  
</SCRIPT>

This is working fine on my form but I am having trouble with the php processing script. I am having trouble with $query2. When I submit the form there should be multiple records being inserted to the table tblAffectedProg but only one is being inserted and the values for each field are the word "Array". Here is my processing script. Please ignore the commented areas as I am trying to troubleshoot so I have commented out much:

<?php require_once("includes/connection.php"); ?>
<?php require_once("includes/functions.php"); ?>
<?php
/*
$errors = array();

// Form Validation
$required_fields = array('List1', 'List2', 'Airdate', 'Description', 'Resolution');
foreach($required_fields as $fieldname) {
if (!isset($_POST[$fieldname]) || empty($_POST[$fieldname])) {
$errors[] = $fieldname;
}
}


$fields_with_lengths = array('menu_name' => 30);
foreach($fields_with_lengths as $fieldname => $maxlength ) {
if (strlen(trim(mysql_prep($_POST[$fieldname]))) > $maxlength) { $errors[] = $fieldname; }
}


if (!empty($errors)) {
redirect_to("errors.html");
}
*/
?>

<?php
$DiscrepType = mysql_prep($_POST['List1']);
$DiscrepDetail = mysql_prep($_POST['List2']);
$Airdate = $_POST['Airdate'];
$Description = mysql_prep($_POST['Description']);
$Resolution = mysql_prep($_POST['Resolution']);
$OnAirVariance = mysql_prep($_POST['OnAirVariance']);
$EquipID = mysql_prep($_POST['EquipID']);
$EquipLoc = mysql_prep($_POST['EquipLoc']);
$StartTime = array($_POST['StartTime']);
$EndTime = array($_POST['EndTime']);
$MaterialID = array($_POST['MaterialID']);
$Title = array($_POST['Title']);
$Row = array($_POST['Row']);

/*
$INISatNSS806 = $_POST['INISatNSS806'];
$LaFamilia = $_POST['LaFamilia'];
$G15Analog = $_POST['G15Analog'];
$G15Digital = $_POST['G15Digital'];
$DirecTV = $_POST['DirecTV'];
$DISH = $_POST['DISH'];
$INSPhits = $_POST['INSPhits'];
$INSPolym = $_POST['INSPolym'];
$HalHDgal15 = $_POST['HalHDgal15'];
$HalGal15 = $_POST['HalGal15'];
$HalOlym = $_POST['HalOlym'];
$HalHits = $_POST['HalHits'];
*/
?>
<?php
$query1 = "INSERT INTO tblonairactivity (
DiscrepType, DiscrepDetail, Airdate, Description, Resolution, OnAirVariance, EquipID, EquipLoc
) VALUES (
'{$DiscrepType}', '{$DiscrepDetail}', '{$Airdate}', '{$Description}', '{$Resolution}', '{$OnAirVariance}', '{$EquipID}', '{$EquipLoc}'
)";

//$lastID = LAST_INSERT_ID()

$pos = 0;
foreach($Row as $index) {

$query2 = "INSERT INTO tblAffectedProg (
tblOnAirActivityID, StartTime, EndTime, MaterialID, Title
  ) VALUES (
  LAST_INSERT_ID(), '{$StartTime[$pos]}', '{$EndTime[$pos]}', '{$MaterialID[$pos]}', '{$Title[$pos]}')";
$pos++;
  } 
/*
$query3 = "INSERT INTO tblsatellites (
INISatNSS806, LaFamilia, G15Analog, G15Digital, DirecTV, DISH, INSPhits, INSPolym, HalHDgal15, HalGal15, HalOlym, HalHits
) VALUES (
{$INISatNSS806}, {$LaFamilia}, {$G15Analog}, {$G15Digital}, {$DirecTV}, {$DISH}, {$INSPhits}, {$INSPolym}, {$HalHDgal15}, {$HalGal15}, {$HalOlym}, {$HalHits}
)";
*/
$result = FALSE;
if (mysql_query('BEGIN')) {
 if (mysql_query($query1) &&
  mysql_query($query2))
  $result = mysql_query('COMMIT'); // both queries looked OK, save
 else
  mysql_query('ROLLBACK'); // problems with queries, no changes.
 }

if ($result) {
// Success!
//redirect_to("success.html");
echo "<pre>" . print_r($_POST,1) . "</pre>"; 

} else {
// Display error message.
echo "<p>Record creation failed.</p>";
echo "<p>" . mysql_error() . "</p>";
}
?>

<?php mysql_close($connection); ?>

I am getting the following error:

Notice: Undefined index: Row in C:\wamp\www\Airtrack-Testing\process.php on line 41

And when I echo back my form's POST this is what I get:

Array
(
[Airdate] => 2012-5-17
[List1] => Equipment issue
[List2] => Digibeta deck
[EquipLoc] => Master Control
[EquipID] => test
[Network_INI] => -1
[OnAirVariance] => -1
[txtStartTime] => 17:00:00
[txtEndTime] => 18:00:00
[txtMaterialID] => test3
[txtTitle] => test3
[StartTime] => Array
(
[0] => 15:00:00
[1] => 16:00:00
)

[EndTime] => Array
(
[0] => 16:00:00
[1] => 17:00:00
)

[MaterialID] => Array
(
[0] => test1
[1] => test2
)

[Title] => Array
(
[0] => test1
[1] => test2
)

[row] => Array
(
[0] => 0
[1] => 1
)

[Description] => test
[Resolution] => test
[SaveRecord] => Save Record
)

I am fairly new to PHP and I am at a loss as to why the values for these arrays are not making it to my table. If anyone can help me out I would be grateful. Thanks in advance for any help.

Kind regards,

Ken

[coopster]

Welcome to WebmasterWorld, deeciple,

Whew, that's a lot of code. You may want to review the PHP Forum Charter as it may help you understand why there hasn't been much response. Still, let's start here:

I am having trouble with $query2. When I submit the form there should be multiple records being inserted to the table tblAffectedProg but only one is being inserted and the values for each field are the word "Array".

The problem is that you are running a foreach loop to build a query into your $query2 variable but what ends up happening is that you are replacing it each and every time rather than executing the INSERT:

foreach($Row as $index) {
$query2 = "INSERT INTO tblAffectedProg (
tblOnAirActivityID, StartTime, EndTime, MaterialID, Title
) VALUES (
LAST_INSERT_ID(), '{$StartTime[$pos]}', '{$EndTime[$pos]}', '{$MaterialID[$pos]}', '{$Title[$pos]}')";
$pos++;
}

[deeciple]

Hi coopster,

Thanks for the reply and for the guideline on posting code. I truly was just trying to provide as much info as possible to be helpful but I can see how that much code would be frustrating to wade through. I will stick to the less is more philosophy going forward :)

I did manage to get it sorted out and it was as you mentioned. I added some code to execute the query and break out of the loop if any instance of the query fails.

Thanks for your guidance.

Kind regards,

Ken

[coopster]

I did manage to get it sorted out and it was as you mentioned. I added some code to execute the query and break out of the loop if any instance of the query fails.

Excellent! Glad you got it sorted.