Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: use for loop while saving data to database

    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 Code:
    <?php                                                                           
    $con 
    mysql_connect('localhost''root','');


    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'], 06);
                
    $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'], 06);
                
    $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";
    }

    </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' ></td>
            <td>&nbsp;</td>
            <td>&nbsp;</td> 
            <td><center><input type='text' name='DemandedQty' id='DemandedQty[]' value=''></center></td>
            
            </tr>"
    ;       
         
    $sql "SELECT Items, SubItems, ItemCode, UoM, Class, Description, BINLocation FROM bom_subitems WHERE Items = '$Items' ORDER BY Items";

    $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'></td>
            <td>&nbsp;<input type='text' name='ItemCode[]' value='
    $ItemCode' id='ItemCode'></td>
            <td>&nbsp;</td>
            <td>&nbsp;<input type='text' name='UoM[]' value='
    $UoM' id='UoM' ></td>
            <td>&nbsp;<input type='text' name='Class[]' value='
    $Class' id='Class' ></td>
            <td>&nbsp;<input type='text' name='Description[]' value='
    $Description' id='Description'></td>
            <td>&nbsp;<input type='text' name='BINLocation[]' value='
    $BINLocation' id='BINLocation'></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()" 
    </div>
    </form>
    </body>
    </html>
    and here is the save code:
    PHP 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
    Attached Thumbnails Attached Thumbnails database.jpg   sr_v01.jpg  

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Check your form code:

    Code:
            <td><center><input type='text' name='DemandedQty' id='DemandedQty[]' value=''></center></td>
    Should read

    Code:
            <td><center><input type='text' name='DemandedQty[]' id='DemandedQty' value=''></center></td>
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Thank you for your suggestion.

    Now, I encountered error:

    Column count doesn't match value count at row 1

    but when I count my row it was match:

    Code:
    for($i = 0; $i < count($Items1); $i++) 
    { 
    
        if ( $DemandedQty[$i] != "" )
        {
        $sql = "INSERT INTO stock_requisition 
        (sr_date, sr_number, Items, SubItems, ItemCode, SubQty, UoM, Class, Description, BINLocation) 
        VALUES 
        ('$sr_date', '$sr_number', '$Items1[$i]', '$SubItems[$i]',  '$ItemCode[$i]', '$SubQty[$i]' '$UoM[$i]', '$Class[$i]', '$Description[$i]', 
        '$BINLocation[$i]') 
        ";   
        $result = mysql_query($sql, $con) or die(mysql_error()); 
        
        
        }      
    }
    Thank you

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    I found my error: i forgot ',' between $SubQty[$i] and $UoM[$i]

    Thank you

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Good catch. I didn't check the actual code just the logic that was being used.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by newphpcoder View Post
    I found my error: i forgot ',' between $SubQty[$i] and $UoM[$i]

    Thank you
    so the lesson from this is to examine the actual SQL you are sending to the engine

    you could do that via an "or die" instruction
    eg temporarily display the SQL whilst in development
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •