Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder dan-dan's Avatar
    Join Date
    Aug 2009
    Location
    England
    Posts
    483
    Thanks
    22
    Thanked 79 Times in 78 Posts

    Multiple row update

    Hey. I thought this was going to be simple, but I can't seem to figure out how to go about updating all rows at once.

    Here is my form
    PHP Code:
    // Search results


    echo "<form method='post' action='multiItem.php'>
          <input type='submit' name='saveChanges' value='Save' />
          
          <table border='1' cellspacing='1' id='searchResults'>
          <th>SKU</th><th>Location</th><th>Backstock</th><th>B/C</th>
          "
    ;

    while(
    $row mysql_fetch_assoc($searchresult)) {

        
        if (
    $row[ItemName] == "") {
            
    $row[ItemName] = "No description available";
        }
        
        echo 
    "
        
        <tr>
        <input type='hidden' name='sku' value='$row[LocalSKU]' />
        <td title='$row[ItemName]' >$row[LocalSKU]</td>
        <td>$row[Location]&nbsp;</td>
        <td><input type='text' size='60%' name='currentlocation' value='$row[Backstock]' /></td>
        <td>$row[Barcode]&nbsp;</td>
        </tr>

    "
    ;
            
    }

    echo 
    "</table>
          <input type='submit' name='saveChanges' value='Save' />
          </form>
          <br />"

    All I want to do is update currentlocation for each row from the users input. I'm guessing I need to use a loop, but can't construct it.
    I have attempted it many times with no luck

    PHP Code:
    if($_POST['saveChanges']) {

     
    $updatedCurrentLocation mysql_real_escape_string(strtoupper(trim(preg_replace'/\s+/'' ', ($_POST['currentlocation'])))));
    $query " ";
     } 

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    You have named *ALL* your fields <input name="currentlocation">

    PHP can't handle multiple fields with the same name unless they are named to look like an array: <input name="currentlocation[]">

    But that's not enough: You need to be able to associate *EACH* of those currentlocation fields with proper SKU.

    You probably *could* do that by also doing <input type='hidden' name='sku[]'...
    and then traversing the two arrays in parallel, but personally I prefer doing something like this:
    Code:
    <input size="60%" name="currentlocation_$row[LocalSKU]" value="$row[Backstock]" />
    (Which also means you can get rid of the hidden fields.)

    Then, in the PHP code that processes the form, you need to traverse all the $_POST values looking for those that have a key of the form "current_location_xxx".

    I am *NOT* a PHP programmer, at all, but I *THINK* it would be something like this:
    Code:
    foreach ( $_POST as $key => $value)
    {
        if ( substring($key, 0, 17) == "current_location_" )
        {
            $sku = mysql_real_escape_string( substring($key,17) ); // the sku found in the key
            $newloc = mysql_real_escape_string($value);
            $sql = "UPDATE yourtablename SET Backstock = '$newloc' WHERE LocalSKU = '$sku'";
            ... execute that sql ...
        }
    }
    I'm assuming that $_POST can be treated the same as other associative arrays. Again, ask a PHP guru for help if that doesn't work.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    dan-dan (11-15-2011)

  • #3
    Regular Coder dan-dan's Avatar
    Join Date
    Aug 2009
    Location
    England
    Posts
    483
    Thanks
    22
    Thanked 79 Times in 78 Posts
    Thanks a lot, it's almost there!

    I had to change a couple of things but here is my code now
    PHP Code:
        // Update location

    if($_POST['saveChanges']){
        
        foreach ( 
    $_POST as $key => $value)
            {
            if (
    substr($key016) == "currentlocation_" ){
                
    $sku mysql_real_escape_string(substr($key,16) ); // the sku found in the key
                
    $updatedCurrentLocation mysql_real_escape_string(strtoupper(trim(preg_replace'/\s+/'' ', ($value)))));
                
    $sql "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
                
    $results mysql_query($sql);
                
                }}
                
                if(!
    $results) { 
                    echo 
    "Error " mysql_error() . "<br />";
                    } 
                    
                    else {
                        echo 
    "<script>
                            window.alert('Update successful');
                            location.reload(true);
                            </script>"
    ;
                            }
                
                } 
    The problem I now get is that my first 5 entries won't update, and I'm assuming that it's because they're beginning numerically (well that's the only thing that stands out anyway).
    examples:
    3.5-3.5
    3.5-EXT-5M

    Any ideas?

    Thanks again, greatly appreciated

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Add some debug:
    Code:
                $sql = "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
                echo "DEBUG: " . $sql . "<br/>\n";
                $results = mysql_query($sql);
    see if that shows the problem.

    And/or show me the <form> as it appears in the browser. That is, bring up the page in the browser, click VIEW menu, click SOURCE (or PAGE SOURCE) menu item. Find those 5 problem children in the HTML and copy/paste them here (along with maybe 2 that don't cause problems).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder dan-dan's Avatar
    Join Date
    Aug 2009
    Location
    England
    Posts
    483
    Thanks
    22
    Thanked 79 Times in 78 Posts
    And the problem shows itself! My dots are being replaced by underscores! Read up on it and some sources say these can't be used in variables, however my sister program - allowing only single row update - has allowed this

    Is there a way to work around this?

    SQL
    Code:
    DEBUG: UPDATE inventory SET Backstock = 'A' WHERE LocalSKU = '3_5-3_5'<br/>
    DEBUG: UPDATE inventory SET Backstock = 'B' WHERE LocalSKU = '3_5-EXT-5M'<br/>
    DEBUG: UPDATE inventory SET Backstock = 'C' WHERE LocalSKU = '3_5-MINI'<br/>
    DEBUG: UPDATE inventory SET Backstock = 'D' WHERE LocalSKU = '3_5-RCA-1M'<br/>
    DEBUG: UPDATE inventory SET Backstock = 'E' WHERE LocalSKU = '3_5-RCA-5M'<br/>
    DEBUG: UPDATE inventory SET Backstock = 'F' WHERE LocalSKU = 'ALLKIT-APPLE'<br/>
    DEBUG: UPDATE inventory SET Backstock = 'G' WHERE LocalSKU = 'APPLE-AV'<br/>
    Form results
    PHP Code:

        
    <tr>
        <
    td title='3.5mm Jack to Jack Stereo Lead - Black 1.2m' >3.5-3.5</td>
        <
    td>P01-070&nbsp;</td>
        <
    td><input type='text' size='60%' name='currentlocation_3.5-3.5' value='1' /></td>
        <
    td>&nbsp;</td>
        </
    tr>


        
        <
    tr>
        <
    td title=' 3.5mm Jack Extension Cable - 5 Metres' >3.5-EXT-5M</td>
        <
    td>B-24&nbsp;</td>
        <
    td><input type='text' size='60%' name='currentlocation_3.5-EXT-5M' value='3' /></td>
        <
    td>&nbsp;</td>
        </
    tr>


        
        <
    tr>
        <
    td title='Mini-USB to Female 3.5 Adaptor' >3.5-MINI</td>
        <
    td>P01-049&nbsp;</td>
        <
    td><input type='text' size='60%' name='currentlocation_3.5-MINI' value='HELLO' /></td>
        <
    td>&nbsp;</td>
        </
    tr>


        
        <
    tr>
        <
    td title='3.5mm Jack to RCA Audio Cable - 1m' >3.5-RCA-1M</td>
        <
    td>C-100&nbsp;</td>
        <
    td><input type='text' size='60%' name='currentlocation_3.5-RCA-1M' value='HELLO' /></td>
        <
    td>&nbsp;</td>
        </
    tr>


        
        <
    tr>
        <
    td title='3.5mm Jack to RCA Audio Cable - 5m' >3.5-RCA-5M</td>
        <
    td>C-106&nbsp;</td>
        <
    td><input type='text' size='60%' name='currentlocation_3.5-RCA-5M' value='HELLO' /></td>
        <
    td>&nbsp;</td>
        </
    tr>


        
        <
    tr>
        <
    td title='No description available' >ALLKIT-APPLE</td>
        <
    td>P01-028&nbsp;</td>
        <
    td><input type='text' size='60%' name='currentlocation_ALLKIT-APPLE' value='F' /></td>
        <
    td>89616775968&nbsp;</td>
        </
    tr>


        <
    tr>
        <
    td title='Apple MB128ZA/B Component AV Cable for iPhone & iPod' >APPLE-AV</td>
        <
    td>&nbsp;</td>
        <
    td><input type='text' size='60%' name='currentlocation_APPLE-AV' value='G' /></td>
        <
    td>5027141582306&nbsp;</td>
        </
    tr
    Last edited by dan-dan; 11-16-2011 at 05:30 PM. Reason: Edit to include form

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Ugh...that's some sort of PHP thing.

    It's not HTML doing the replacement.

    And when I coded up a quicky test in ASP, it worked just fine!

    Okay, so we introduce a hack: Replace the periods with an "escape" mechanism when you create the HTML (something like '$$'?).

    And then replace the escape with a period when processing the <Form>.

    That is:
    Code:
    $fixSKU = str_replace(".","$$",$row[LocalSKU]);
    echo '<input size="60%" name="currentlocation_$fixSKU" value="$row[Backstock]" />\n';
    or equivalent.

    And then:
    Code:
            if (substr($key, 0, 16) == "currentlocation_" ){
                $sku = substr($key,16);
                $sku = str_replace("$$",".",$sku)
                $sku = mysql_real_escape_string($sku);
                $updatedCurrentLocation = mysql_real_escape_string(strtoupper(trim(preg_replace( '/\s+/', ' ', ($value)))));
                $sql = "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
    ...
    If $$ doesn't work, find some weird character combination that does.

    ************

    Thinking about it, this ALMOST makes sense. PHP automatically converts $_POST and $_GET names to variables for you. And so of course illegal variable names have to be converted to legal ones. Okay, that's an excuse. If you don't use the automatic conversions, why would they need to muck with the raw name when you use $_POST?? Oh, well. PHP's method of handling multiply-named fields (requiring [] as part of the field name) is brain dead, so why shouldn't this be, too?
    Last edited by Old Pedant; 11-16-2011 at 08:15 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    dan-dan (11-16-2011)

  • #7
    Regular Coder dan-dan's Avatar
    Join Date
    Aug 2009
    Location
    England
    Posts
    483
    Thanks
    22
    Thanked 79 Times in 78 Posts
    Absolute genius! Works exactly as intended

    I'm pretty new to PHP and MySQL so it would have taken me forever to solve this alone.

    Here's the working code:
    PHP Code:
    // Update location

    if ($_POST['saveChanges']){
        
        foreach (
    $_POST as $key => $value)
            {
            if (
    substr($key016) == "currentlocation_" ){
                
    $sku substr($key,16);
                
    $sku str_replace("$$",".",$sku);
                
    $sku mysql_real_escape_string($sku);
                
    $updatedCurrentLocation mysql_real_escape_string(strtoupper(trim(preg_replace'/\s+/'' ', ($value)))));
                
    $sql "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
                
    $results mysql_query($sql);
                
                }}
                
                if (!
    $results) { 
                    echo 
    "Error " mysql_error() . "<br />";
                    } 
                    
                    else {
                        
    header("Location: success.php");
                            }
                
                } 
    Form:
    PHP Code:
    echo "<form method='post' action='multiItem.php'>
          <input type='submit' name='saveChanges' value='Save' />
          
          <table border='1' cellspacing='1' id='searchResults'>
          <th>SKU</th><th>Location</th><th>Backstock</th><th>B/C</th>
          "
    ;

    while(
    $row mysql_fetch_assoc($searchresult)) {

        if (
    $row[ItemName] == "") {
            
    $row[ItemName] = "No description available";
        }
        
        
    $fixSKU str_replace(".","$$",$row[LocalSKU]);
        
        echo 
    "
        <tr>
        <td title='$row[ItemName]' >$row[LocalSKU]</td>
        <td>$row[Location]&nbsp;</td>
        <td><input type='text' size='60%' name='currentlocation_$fixSKU' value='$row[Backstock]' /></td>
        <td>$row[Barcode]&nbsp;</td>
        </tr>

    "
    ;
            
    }

    echo 
    "</table>
          <input type='submit' name='saveChanges' value='Save' />
          </form>
          <br />"

    Thanks again Old Pedant.


  •  

    Posting Permissions

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