...

View Full Version : use for loop while saving data to database



newphpcoder
04-16-2012, 04:38 AM
Hi..

I need help in using for loop in saving data from while loop.

Now, I encountered that the Demanded Qty was get only is the last Demanded Qty and save it to all Items.

I want to happen is per Items will save the Demanded Qty.

for example:
Items Demanded Qty
P28 ---1
P28 ---1
P28 ---1
P30 ---2
P30 ---2
P30 ---2
and so on..

here is my code:


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

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

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

$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;
$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;
}

}
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>
<link rel="stylesheet" type="text/css" href="kanban.css">
<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;
var Items = document.getElementById("Items").value;

document.stock_requisition.action="StockRequisitionSave1.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+"&Items="+Items;
document.stock_requisition.submit();
alert("Stock Requisition data save.");
window.location = "StockRequisition1.php";
}

function disp(){
document.stock_requisition.action="StockRequisitionDisplay.php";
document.stock_requisition.submit();
}
</script>
</head>
<body>
<form name="stock_requisition" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<div id="SR_date">
<label>Date :</label>
<input type="text" name="sr_date" value="<?php echo $sr_date; ?>" size="16" readonly="readonly">
</div>
<div id="SR_number">
<label>SR# :</label>
<input type="text" name="sr_number" value="<?php echo $currentSR; ?>" size="10" readonly="readonly">
<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>&nbsp;<input type='name' value='$Items' name='Items[]' id='Items' readonly = 'readonly' size='5'></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><center><input type='text' name='DemandedQty' id='DemandedQty[]' value='' size='7'></center></td>

</tr>";

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

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

while($row_sub = mysql_fetch_assoc($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>&nbsp;<input type='hidden' value='$Items1' id='Items1' name='Items1[]'></td>
<td>&nbsp;<input type='text' name='SubItems[]' value='$SubItems' id='SubItems' readonly='readonly' size='10'></td>
<td>&nbsp;<input type='text' name='ItemCode[]' value='$ItemCode' id='ItemCode' readonly='readonly' size='10'></td>
<td>&nbsp;</td>
<td>&nbsp;<input type='text' name='UoM[]' value='$UoM' id='UoM' readonly='readonly' size='3'></td>
<td>&nbsp;<input type='text' name='Class[]' value='$Class' id='Class' readonly='readonly' size='10'></td>
<td>&nbsp;<input type='text' name='Description[]' value='$Description' id='Description' readonly='readonly' size='10'></td>
<td>&nbsp;<input type='text' name='BINLocation[]' value='$BINLocation' id='BINLocation' readonly='readonly' size='10'></td>
</tr>";

}
}
?>
</table>
</div>
<?php
$RequestedBy = array('AAA', 'BBB');

$ApprovedBy = array('EEE', 'FFF');

$ReceivedBy = array('III', 'JJJ');

$IssuedBy = array('MMM', 'NNN');

?>

<div id='Requested_By'>
<label>Requested By:</label>
<select name="RequestedBy">
<option value="Select">Select</option>
<option value="AAA" <?php if($_POST['RequestedBy'] == 'AAA') echo "selected='selected'"; ?>>AAA</option>
<option value="BBB" <?php if($_POST['RequestedBy'] == 'BBB') echo "selected='selected'"; ?>>BBB</option>
</select>
</div>

<div id='Approved_By'>
<label>Approved By:</label>
<select name="ApprovedBy">
<option name='Select'>Select</option>
<option value="EEE" <?php if($_POST['ApprovedBy'] == 'EEE') echo "selected='selected'"; ?>>EEE</option>
<option value="FFF" <?php if($_POST['ApprovedBy'] == 'FFF') echo "selected='selected'"; ?>>FFF</option>
</select>
</div>

<div id='Received_By'>
<label>Issued By:</label>
<select name="IssuedBy">
<option name='Select'>Select</option>
<option value="III" <?php if($_POST['ReceivedBy'] == 'III') echo "selected='selected'"; ?>>III</option>
<option value="JJJ" <?php if($_POST['ReceivedBy'] == 'JJJ') echo "selected='selected'"; ?>>JJJ</option>
</select>
</div>

<div id='Issued_By'>
<label>Received By:</label>
<select name="ReceivedBy">
<option name='Select'>Select</option>
<option value="MMM" <?php if($_POST['IssuedBy'] == 'MMM') echo "selected='selected'"; ?>>MMM</option>
<option value="NNN" <?php if($_POST['IssuedBy'] == 'NNN') echo "selected='selected'"; ?>>NNN</option>

</select>
</div>

<div id="save_btn">
<input type="button" name="button" value="save" onClick="save_sr()" style="width: 5em;">

<input type="button" name="button" value="display" onclick="disp()">

</div>
</form>
</body>
</html>


and here is the save code:


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

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

mysql_select_db("mes", $con);
$sr_date = $_POST['sr_date'];
$sr_number = $_POST['sr_number'];
$Items1 = $_POST['Items1'];
$SubItems = $_POST['SubItems'];
$ItemCode = $_POST['ItemCode'];
$DemandedQty = $_POST['DemandedQty'];
$UoM = $_POST['UoM'];
$Class = $_POST['Class'];
$Description = $_POST['Description'];
$BINLocation = $_POST['BINLocation'];
$RequestedBy = $_POST['RequestedBy'];
$ApprovedBy = $_POST['ApprovedBy'];
$ReceivedBy = $_POST['ReceivedBy'];
$IssuedBy = $_POST['IssuedBy'];
$Items = $_POST['Items'];

for($i = 0; $i < count($Items1); $i++)
{

if ( $DemandedQty != "" )
{

$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[$i]', '$SubItems[$i]', '$ItemCode[$i]', '$DemandedQty', '$UoM[$i]', '$Class[$i]', '$Description[$i]',
'$BINLocation[$i]', '$RequestedBy', '$ApprovedBy', '$ReceivedBy', '$IssuedBy')
";
$result = mysql_query($sql, $con);

}
}


?>

I attach my sample form and the data save in database.

Thank you so much



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum