elliottdan elliottdan - 1 month ago 12
Javascript Question

Insert Multiple Records Into MySQL from a multi line table PHP Javascrip

Im having an issue adding multiple Records into MySQL. I know i am missing something simple but i cant figure it out. hence this post...Every thing is working expect for the issue when the user clicks submit. only the last row of the table is being recognized.

Example...First 2 rows have Unit Number 1234-01,1234-02 and the last row has Unit Number 1234-03. when submit is clicked it is skipping over the 01 and 02 and only entering the 03 into the database.

Thank you for any help....My head is starting to hurt from banging it on my desk.

<h2>Please fill in the information below</h2>
<form action="pmUnitCreate.php" method="post">
<p>Click the Add button to add a new row. Click the Delete button to Delete last row.</p>

<table id="myTable">
<th>Unit Number</th>
<th>Model Number</th>
<th>Serial Number</th>
<th>Scope</th>
<th><input type="button" id="btnAdd" class="button-add" onClick="insertRow()" value="Add"></input></th>
<th><input type="button" id="btnDelete" class="button-delete" onClick="deleteRow(-1)" value="Delete"></input></th>
<tr>
<td><input type="text" name="UnitID" /></td>
<td><input type="text" name="ModelNumber" /></td>
<td><input type="text" name="SerialNumber" /></td>
<td><select name="Scope">

<option value="BTank">BTank</option>
<option value="WSEcon">WSEcon</option>
<option value="NetPkg">NetPkg</option>
<option value="CstmCabinet">CstmCabinet</option>
<option value="CstmCtrl">CstmCtrl</option>
<option value="CstmRef">CstmRef</option>
<option value="CstmSM">CstmSM</option>
<option value="CstmHV">CstmHV</option>
<option value="CPCtrl">CPCtrl</option>
<option value="DesiHW">DesiHW</option>
<option value="DigScroll">DigScroll</option>
<option value="DFGas">DFGas</option>
<option value="DWall">DWall</option>
<option value="MZ-DD">MZ-DD</option>
<option value="DPP">DPP</option>
<option value="Encl">Encl</option>
<option value="PlateHX">PlateHX</option>
<option value="ERW">ERW</option>

</select>
</td>



</tr>

<br>

<script>

var index = 1;
function insertRow(){
var table=document.getElementById("myTable");
var row=table.insertRow(table.rows.length);
var cell1=row.insertCell(0);
var t1=document.createElement("input");
t1.name = "UnitID";
cell1.appendChild(t1);
var cell2=row.insertCell(1);
var t2=document.createElement("input");
t2.id = "ModelNumber";
cell2.appendChild(t2);
var cell3=row.insertCell(2);
var t3=document.createElement("input");
t3.id = "SerialNumber";
cell3.appendChild(t3);
var cell4=row.insertCell(3);
var element2 = document.createElement("select");
element2.id="Scope";
//element2.type = "select";
var option1 = document.createElement("option");
option1.innerHTML = "BTank";
option1.value = "1";
element2.add(option1, null);
var option2 = document.createElement("option");
option2.innerHTML = "WSEcon";
option2.value = "2";
element2.add(option2, null);
var option3 = document.createElement("option");
option3.innerHTML = "NetPkg";
option3.value = "3";
element2.add(option3, null);
var option4 = document.createElement("option");
option4.innerHTML = "CstmCtrl";
option4.value = "4";
element2.add(option4, null);
var option5 = document.createElement("option");
option5.innerHTML = "CstmRef";
option5.value = "5";
element2.add(option5, null);
var option6 = document.createElement("option");
option6.innerHTML = "CstmSM";
option6.value = "6";
element2.add(option6, null);
var option7 = document.createElement("option");
option7.innerHTML = "CstmHV";
option7.value = "7";
element2.add(option7, null);


cell4.appendChild(element2);

index++;

}

function deleteRow(){
document.getElementById("myTable").deleteRow(-1);

}






Here is the SQL code

<?php
//initialize and read the variables from the posting
//include('Mail.php');
$UnitID=$_POST['UnitID'];
$UnitID="'" . $UnitID . "'"; //Adds the required quotation marks
$ModelNumber=$_POST['ModelNumber'];
$ModelNumber="'" . $ModelNumber . "'"; //Adds the required quotation marks
$SerialNumber=$_POST['SerialNumber'];
$SerialNumber="'" . $SerialNumber . "'"; //Adds the required quotation marks
$Scope=$_POST['Scope'];
$Scope="'" . $Scope . "'"; //Adds the required quotation marks

echo $UnitID;
echo $ModelNumber;
echo $SerialNumber;
echo $Scope;

echo $UnitID;
echo $ModelNumber;
echo $SerialNumber;
echo $Scope;

echo $UnitID;
echo $ModelNumber;
echo $SerialNumber;
echo $Scope;



//Connect to the database
include('pmconnect.php');

$sql = "INSERT INTO `tblUnitMaster` (`UnitID`, `ModelNumber`, 'SerialNumber`, `Scope`) VALUES ($UnitID, $ModelNumber, $SerialNumber, $Scope )";
$sql = "INSERT INTO `tblUnitMaster` (`UnitID`, `ModelNumber`, `SerialNumber`, `Scope`) VALUES ($UnitID, $ModelNumber, $SerialNumber, $Scope )";
$sql = "INSERT INTO `tblUnitMaster` (`UnitID`, `ModelNumber`, `SerialNumber`, `Scope`) VALUES ($UnitID, $ModelNumber, $SerialNumber, $Scope )";



if ($conn->multi_query($sql) === TRUE)

echo "<h3>Record Created Successfully.</h3><br>";

?>enter code here

Answer

You are overwriting $sql (and other variables, are you performing a loop?) and so it only inserts the last one. A little concatenation and making sure each query has its own semi-colon will fix things up:

//Connect to the database
include('pmconnect.php');

$sql = "INSERT INTO `tblUnitMaster` (`UnitID`, `ModelNumber`,  'SerialNumber`, `Scope`) VALUES ($UnitID, $ModelNumber, $SerialNumber, $Scope );";
$sql .= "INSERT INTO `tblUnitMaster` (`UnitID`, `ModelNumber`, `SerialNumber`, `Scope`) VALUES ($UnitID, $ModelNumber, $SerialNumber, $Scope );";
$sql .= "INSERT INTO `tblUnitMaster` (`UnitID`, `ModelNumber`, `SerialNumber`, `Scope`) VALUES ($UnitID, $ModelNumber, $SerialNumber, $Scope )";