PDA

View Full Version : Problem: only first row was save in database from while loop



newphpbees
03 Apr 2012, 09:37 PM
Hi..

I just want to know how can I save to another table all data that I display using while loop.

Now I encountered only one row was save.



<?php
error_reporting(0);
date_default_timezone_set("Asia/Singapore");
$con = mysql_connect('localhost', 'root','');

if (!$con) {
echo 'failed';
die();
}

mysql_select_db("mes", $con);
$sr_date =date('Y-m-d H:i:s');

$sr_date = $_GET['sr_date'];
$sr_number = $_GET['sr_number'];
$Items = $_GET['Items'];
$SubItems = $_GET['SubItems'];
$ItemCode = $_GET['ItemCode'];
$DemandedQty = $_GET['DemandedQty'];
$UoM = $_GET['UoM'];
$Class = $_GET['Class'];
$Description = $_GET['Description'];
$BINLocation = $_GET['BINLocation'];
$RequestedBy = $_GET['RequestedBy'];
$ApprovedBy = $_GET['ApprovedBy'];
$ReceivedBy = $_GET['ReceivedBy'];
$IssuedBy = $_GET['IssuedBy'];

$sql = "SELECT sr_number FROM stock_requisition ORDER BY sr_date DESC LIMIT 1";
$result = mysql_query($sql, $con);

if (!$result) {
echo 'failed';
die();
}
$total = mysql_num_rows($result);
if ($total <= 0) {
$currentSRNum = 1;
}
else {

$row = mysql_fetch_assoc($result);

$currentSRNum = (int)(substr($row['sr_number'],0,3));

$currentSRYear = (int)(substr($row['sr_number'],2,2));
$currentSRMonth = (int)(substr($row['sr_number'],0,2));
$currentSRNum = (int)(substr($row['sr_number'],6,4));

$currentYear = (int)(date('y'));
$currentMonth = (int)(date('m'));
$currentDay = (int)(date('d'));

$currentSRYMD = substr($row['sr_number'], 0, 6);
$currentYMD = date("ymd");
if ($currentYMD > $currentSRYMD)
{
$currentSRNum = 1;
}
else
{
$currentSRNum += 1;
}
}

$yearMonth = date('ymd');
$currentSR = $currentYMD . sprintf("%04d", $currentSRNum);
?>
<html>
<title>Stock Requisition</title>
<head>


<script type="text/javascript">
function save_sr(){
var sr_date = document.getElementById("sr_date").value;
var sr_number = document.getElementById("sr_number").value;
var Items1 = document.getElementById("Items1").value;
var SubItems = document.getElementById("SubItems").value;
var ItemCode = document.getElementById("ItemCode").value;
var DemandedQty = document.getElementById("DemandedQty").value;
var UoM = document.getElementById("UoM").value;
var Class = document.getElementById("Class").value;
var Description = document.getElementById("Description").value;
var BINLocation = document.getElementById("BINLocation").value;
var RequestedBy = document.getElementById("RequestedBy").value;
var ApprovedBy = document.getElementById("ApprovedBy").value;
var ReceivedBy = document.getElementById("ReceivedBy").value;
var IssuedBy = document.getElementById("IssuedBy").value;

document.stock_requisition.action="StockRequisitionSave.php?sr_date="+sr_date+"&sr_number="+sr_number+"&Items1="+Items1+
"&SubItems="+SubItems+"&ItemCode="+ItemCode+"&DemandedQty="+DemandedQty+"&UoM="+UoM+"&Class="+Class+"&Description="+
Description+"&BINLocation="+BINLocation+"&RequestedBy="+RequestedBy+"&ApprovedBy="+ApprovedBy+"&ReceivedBy="+ReceivedBy+
"&IssuedBy="+IssuedBy;
document.stock_requisition.submit();

alert("Stock Requisition data save.");
window.location = "StockRequisition.php";


}
</script>
</head>
<body>
<form name="stock_requisition" action="<?php echo $_SERVER['PHP_SELF']; ?>" >
<div id="ddcolortabs">
<ul>
<li> <a href="ParameterSettings.php" title="Parameter Settings"><span>Parameter Settings</span></a></li>
<li style="margin-left: 1px"><a href="kanban_report.php" title="WIP Report"><span>Wip Report</span></a></li>
<li id="current"><a href="StockRequisition.php" title="WMS RM"><span>WMS RM</span></a></li>
</ul>
</div>

<div id="ddcolortabs1">
<ul>
<li><a href="ReceivingMaterials.php" title="Receiving Materials"><span>Receiving Materials</span></a></li>
<li><a href="Shelving.php" title="Shelving"><span>Shelving</span></a></li>
<li id="current"><a href="StockRequisition.php" title="Stock Requisition"><span>Stock Requisition</span></a></li>
</ul>
</div>

<div id="SR_date">
<label>Date :</label>
<input type="text" name="sr_date" value="<?php echo $sr_date; ?>" size="16" readonly="readonly" style="border: none;">
</div>
<div id="SR_number">
<label>SR# :</label>
<input type="text" name="sr_number" value="<?php echo $currentSR; ?>" size="10" readonly="readonly" style="font-weight: bold; border: none;">
<br/>
</div>
<div>
<table>
<thead>
<th>Items</th>
<th>Sub Items</th>
<th>Item Code</th>
<th>Demanded Qty</th>
<th>UoM</th>
<th>Class</th>
<th>Description</th>
<th>BIN Location</th>
</thead>
<?php

$sql = "SELECT DISTINCT Items FROM bom_subitems ORDER BY Items";
$res_bom = mysql_query($sql, $con);

while($row = mysql_fetch_assoc($res_bom)){

$Items = $row['Items'];
echo "<tr>
<td style='border: none;font-weight: bold;'>&nbsp;<input type='name' value='$row[Items]' name='Items' id='Items' readonly = 'readonly' style = 'border:none;width:auto;' size='5'></td>
</tr>";


$sql = "SELECT Items, SubItems, ItemCode, UoM, Class, Description, BINLocation FROM bom_subitems WHERE Items = '$row[Items]' ORDER BY Items"or die(mysql_error());

$res_sub = mysql_query($sql, $con);
while($row_sub = mysql_fetch_array($res_sub)){

$Items1 = $row_sub['Items'];
$SubItems = $row_sub['SubItems'];
$ItemCode = $row_sub['ItemCode'];
$UoM = $row_sub['UoM'];
$Class = $row_sub['Class'];
$Description = $row_sub['Description'];
$BINLocation = $row_sub['BINLocation'];


echo "<tr>
<td style='border: none;'>&nbsp;<input type='hidden' value='$Items1' id='Items1' name='Items1[]'></td>
<!--<td style='border: none;'>&nbsp;<input type='hidden' value='$cast[$i]['id']' id='Items1' name='Items1[]'></td> -->
<td style='border: none;'>&nbsp;<input type='text' name='SubItems[]' value='$SubItems' id='SubItems' readonly='readonly' style='border:none; width:auto;' size='10'></td>
<td style='border: none;'>&nbsp;<input type='text' name='ItemCode[]' value='$ItemCode' id='ItemCode' readonly='readonly' style='border:none; width:auto;' size='10'></td>
<td style='border: none;'><center><input type='text' name='DemandedQty' id='DemandedQty' value='' size='7'></center></td>
<td style='border: none;' size='3'>&nbsp;<input type='text' name='UoM[]' value='$UoM' id='UoM' readonly='readonly' style='border:none; width:auto;' size='3'></td>
<td style='border: none;'>&nbsp;<input type='text' name='Class[]' value='$Class' id='Class' readonly='readonly' style='border:none; width:auto;' size='10'></td>
<td style='border: none;'>&nbsp;<input type='text' name='Description[]' value='$Description' id='Description' readonly='readonly' style='border:none; width:auto;' size='10'></td>
<td style='border: none;'>&nbsp;<input type='text' name='BINLocation[]' value='$BINLocation' id='BINLocation' readonly='readonly' style='border:none; width:auto;' size='10'></td>
</tr>";


}
$sql = "INSERT INTO stock_requisition
(sr_date, sr_number, Items, SubItems, ItemCode, DemandedQty, UoM, Class, Description, BINLocation, RequestedBy,
ApprovedBy, ReceivedBy, IssuedBy)
VALUES
('$sr_date', '$sr_number', '$Items1', '$SubItems', '$ItemCode', '$DemandedQty', '$UoM', '$Class', '$Description',
'$BINLocation', '$RequestedBy', '$ApprovedBy', '$ReceivedBy', '$IssuedBy')
ON DUPLICATE KEY UPDATE
sr_date = '$sr_date', sr_number = '$sr_number', Items = '$Items1', SubItems = '$SubItems', ItemCode = '$ItemCode', DemandedQty = '$DemandedQty',
UoM = '$UoM', Class = '$Class', Description = '$Description', BINLocation = '$BINLocation', RequestedBy = '$RequestedBy',
ApprovedBy = '$ApprovedBy', ReceivedBy = '$ReceivedBy', IssuedBy = '$IssuedBy'";

$result = mysql_query($sql, $con);

}

?>
</table>
</div>

<div id='RequestedBy'>
<label>Requested By:</label>
<select name="Requested_By">
<option name='None'>Select</option>
<option name='AAAAAAAAA'>AAAAAAAAA</option>
<option name='BBBBBBBBB'>BBBBBBBBB</option>
<option name='CCCCCCCCCC'>CCCCCCCCCC</option>
</select>
</div>

<div id='ApprovedBy'>
<label>Approved By:</label>
<select name="Approved_By">
<option name='None'>Select</option>
<option name='AAAAAAAAA'>AAAAAAAAA</option>
<option name='BBBBBBBBB'>BBBBBBBBB</option>
<option name='CCCCCCCCCC'>CCCCCCCCCC</option>
</select>
</div>

<div id='ReceivedBy'>
<label>Received By:</label>
<select name="Received_By">
<option name='None'>Select</option>
<option name='AAAAAAAAA'>AAAAAAAAA</option>
<option name='BBBBBBBB'>BBBBBBBB</option>
<option name='CCCCCCCC'>CCCCCCCC</option>
</select>
</div>

<div id='IssuedBy'>
<label>Issued By:</label>
<select name="Issued BY">
<option name='None'>Select</option>
<option name='AAAAAAAAA'>AAAAAAAAA</option>
<option name='BBBBBBBB'>BBBBBBBB</option>
<option name='CCCCCCCC'>CCCCCCCC</option>
</select>
</div>

<div id="save_btn">
<input type="button" name="button" value="save" onclick="save_sr()">
</div>
</body>
</html>



I attach the sample form and output.

Sorry if I repost my previous thread.

I hope somebody can help me.

Thank you