Learn VBA & Macros in 1 Week!

PHP - Use For Loop In Inserting Data Into Database

Full Excel VBA Course - Beginner to Expert

Use For Loop In Inserting Data Into Database	View Content

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> <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

Full Excel VBA Course - Beginner to Expert

Inserting Values Into Database Using For Loop?

Similar Tutorials

PHP - Use For Loop In Inserting Data Into Database

Use For Loop In Inserting Data Into Database

Similar Tutorials

Inserting Values Into Database Using For Loop?

Having Issues With Inserting Data Into Array Inside Loop

Inserting Data Into Database

Inserting Array Of Data Into Database

Inserting Multiple Data With A Same Id In Database

Data Not Inserting In Mysql Database

Please Help With Inserting Data Into The Database (using Html Form)

Inserting Array Data Into Multiple Rows Of Database

Insert Data To Database Where Data From While Loop

Adding A <form> In A Loop Duplaicate Data Going Into Database.

Proper Syntax For Inserting An Image In A While Loop?

Php Not Inserting To Database

Not Inserting Into Database

Not Inserting Anything Into My Database.

Inserting Into Online Database

Inserting Into Database Problems

Information Not Inserting Into Database

Inserting Value Into Database - Problem.

Inserting Values Via Php To Sql Database

Help Inserting Database Into Table