www.webdeveloper.com
Results 1 to 10 of 10

Thread: [RESOLVED] Insert data to database where data from while loop

  1. #1
    Join Date
    Oct 2011
    Posts
    350

    resolved [RESOLVED] Insert data to database where data from while loop

    Hi..

    Sorry, if I need to repost my problem in while loop. cause until now I can't resolve my problem in saving all data from while loop.

    I have this code StockRequisition.php where FORM was displayed.
    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 =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 {
    //------------------------------------------------------------------------------------------------------------------
                // Stock Number iteration.... 
                
    $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;
        
        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']; ?>" method="post">
    <div id="SR_date">
    <label>Date :</label>
    <input type="text" name="sr_date" value="<?php echo $sr_date?>" size="16">    
    </div>
    <div id="SR_number">
    <label>SR# :</label>
    <input type="text" name="sr_number" value="<?php echo $currentSR?>" size="10" >
    <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='
    $Items' name='Items' id='Items'></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' size='10'></td>
            <td>&nbsp;<input type='text' name='ItemCode' value='
    $ItemCode' id='ItemCode' size='10'></td>
            <td><center><input type='text' name='DemandedQty' id='DemandedQty' value='' size='7'></center></td>
            <td>&nbsp;<input type='text' name='UoM' value='
    $UoM' id='UoM' readonly='readonly' style='border:none; width:auto' size='3'></td>
            <td>&nbsp;<input type='text' name='Class' value='
    $Class' id='Class' size='10'></td>
            <td>&nbsp;<input type='text' name='Description' value='
    $Description' id='Description' size='10'></td>
            <td>&nbsp;<input type='text' name='BINLocation' value='
    $BINLocation' id='BINLocation' 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()">
    </div>
    </form>
    </body>
    </html>
    and here is StockRequisitionSave.php
    code for saving data to database :

    PHP Code:
    <?php                                                                          
    $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'];

    $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')
    "
    ;  

    $result mysql_query($sql$con);            
    ?>
    the problem is only the last data was save.

    I will attach my form.

    I hope somebody can help me..
    I really don't know how can I save all data from form into my database.

    Thank you so much for your understanding and help.
    Attached Images Attached Images

  2. #2
    Join Date
    Apr 2012
    Posts
    1
    oh, so complicated.

  3. #3
    Join Date
    Apr 2010
    Posts
    227
    Well

    Your issue lies within how your mind is working.

    You have a whole bunch of rows right. One for each product.
    What you have done wrong is given each and every form field the same ID.

    This means when you post your form to the server it contains the last form data that was displayed. (because you pull the data using js getelementbyid effectively over writting the previously display item form/row)

    How can that ever work my friend?

  4. #4
    Join Date
    Apr 2010
    Posts
    227
    Question is:

    Do you need users to update the ENTIRE database of products in one go or just one item at a time?

    There are solutions for both.

  5. #5
    Join Date
    Apr 2010
    Posts
    227
    ps:

    What i like to do. (i have built similar websites)

    is:

    A: I use AJAX
    B: I update the database of items ONE FIELD at a time. eg: Change the item name just updates the name no other field.
    C: You do this by giving each form field a UNIQUE id.

    Easy as pie.
    (now i dont know what your unique primary key is for each item so submit my example with your own.)

    PHP Code:

    echo "<input type='text' name='BINLocation' value='$BINLocation' id='BINLocation_".$itemid."' size='10'>" 
    See whats happening here? Each field has a unique id thats a combination the field id AND primary key for that item.
    When it reaches the php server script you can easily split the POST apart again using explode "_"

    So your script knows that its updating the database for the item $itemid and the field BINLocation.

  6. #6
    Join Date
    Oct 2011
    Posts
    350
    Quote Originally Posted by Belrick View Post
    Well

    Your issue lies within how your mind is working.

    You have a whole bunch of rows right. One for each product.
    What you have done wrong is given each and every form field the same ID.

    This means when you post your form to the server it contains the last form data that was displayed. (because you pull the data using js getelementbyid effectively over writting the previously display item form/row)

    How can that ever work my friend?
    How can I add different ID?or should I need not to use js for saving data?

    Thank you

  7. #7
    Join Date
    Oct 2011
    Posts
    350
    Quote Originally Posted by Belrick View Post
    Question is:

    Do you need users to update the ENTIRE database of products in one go or just one item at a time?

    There are solutions for both.
    I need to add all data from the form to my database once the save button was click.


    Thank you so much..

  8. #8
    Join Date
    Oct 2011
    Posts
    350
    Quote Originally Posted by Belrick View Post
    ps:

    What i like to do. (i have built similar websites)

    is:

    A: I use AJAX
    B: I update the database of items ONE FIELD at a time. eg: Change the item name just updates the name no other field.
    C: You do this by giving each form field a UNIQUE id.

    Easy as pie.
    (now i dont know what your unique primary key is for each item so submit my example with your own.)

    PHP Code:

    echo "<input type='text' name='BINLocation' value='$BINLocation' id='BINLocation_".$itemid."' size='10'>" 
    See whats happening here? Each field has a unique id thats a combination the field id AND primary key for that item.
    When it reaches the php server script you can easily split the POST apart again using explode "_"

    So your script knows that its updating the database for the item $itemid and the field BINLocation.
    where i can declare $itemid and what's data in $itemid.

    I attach my database image.

    for your better understanding
    Attached Images Attached Images

  9. #9
    Join Date
    Oct 2011
    Posts
    350
    I revised some of my code and it resolved my problem.

    In StockRequisition.php, use variable names as array.

    PHP Code:
       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' size='10'></td> 
            <td>&nbsp;<input type='text' name='ItemCode[]' value='
    $ItemCode' id='ItemCode' size='10'></td> 
            <td><center><input type='text' name='DemandedQty[]' id='DemandedQty' value='' size='7'></center></td> 
            <td>&nbsp;<input type='text' name='UoM[]' value='
    $UoM' id='UoM' readonly='readonly' style='border:none; width:auto' size='3'></td> 
            <td>&nbsp;<input type='text' name='Class[]' value='
    $Class' id='Class' size='10'></td> 
            <td>&nbsp;<input type='text' name='Description[]' value='
    $Description' id='Description' size='10'></td> 
            <td>&nbsp;<input type='text' name='BINLocation[]' value='
    $BINLocation' id='BINLocation' size='10'></td>      
            </tr>"

    In StockRequisitionSave.php, read and loop it as array

    PHP Code:
    $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']; 

    for(
    $i 0$i count($Items1); $i++) 

        
    $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[$i]', '$UoM[$i]', '$Class[$i]', '$Description[$i]', 
        '
    $BINLocation[$i]', '$RequestedBy', '$ApprovedBy', '$ReceivedBy', '$IssuedBy') 
        "
    ;   

        
    $result mysql_query($sql$con);          


  10. #10
    Join Date
    Apr 2010
    Posts
    227
    Well done!

    How effeciently does it work for you to POST so much data at one go?

    I assume your data base of items isnt too large then?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles