use for loop in inserting data into database

newphpcoder · April 16, 2012

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

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> <input type='name' value='$Items' name='Items[]' id='Items' readonly = 'readonly' size='5'></td>
        <td> </td>
        <td> </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> <input type='hidden' value='$Items1' id='Items1' name='Items1[]'></td>
        <td> <input type='text' name='SubItems[]' value='$SubItems' id='SubItems' readonly='readonly' size='10'></td>
        <td> <input type='text' name='ItemCode[]' value='$ItemCode' id='ItemCode' readonly='readonly' size='10'></td>
        <td> </td>
        <td> <input type='text' name='UoM[]' value='$UoM' id='UoM' readonly='readonly' size='3'></td>
        <td> <input type='text' name='Class[]' value='$Class' id='Class' readonly='readonly' size='10'></td>
        <td> <input type='text' name='Description[]' value='$Description' id='Description' readonly='readonly' size='10'></td>
        <td> <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

Sign In

use for loop in inserting data into database

Recommended Posts

newphpcoder

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information