How to update each row of a dynamic PHP table

Question

I am trying to change the quantity of each row based on the 'Amt' that is inputted in the last column of that row. It's a search table that's based off the Area #. Essentially I want the user to be able to input a location and move bulk items from the current area to the new area that was inputted. If the Amt would equal zero that row would be skipped and no update would take place. Otherwise it would create a new row in the database with the new data.

Link to screenshot

https://photos.google.com/photo/AF1QipP2HMqNFmv208VOOl2DvppPGiZkv7f_keD_f8tj

This is my php table code:

The values for country, region, location and placeid are stored in the dropdown.

<?php

if (isset($_GET['Placeid'])) {
    $moveplace = $_GET['Placeid'];
    $sql = "SELECT *
        FROM Parts p, Locations l
        WHERE Placeid = '$moveplace' and p.locationid = l.locationid";

    $result = mysqli_query($conn, $sql);
    $queryResult = mysqli_num_rows($result);

    if ($queryResult > 0) {
        $i = 1;
        while ($row = mysqli_fetch_assoc($result)) {
            if ($i % 2 == 0) {
                $bgcolor = "rgba(199, 199, 199, 0.3)";
            } else {
                $bgcolor = "rgba(199, 199, 199, 0.8)";
            }

            echo "<div>
            <input type='hidden' value='".$row['id']."' name='hiddensearchid'>
            <input type='hidden' value='".$row['PartDescription']."' name='movedesc'>
            <input type='hidden' value='".$row['BrandName']."' name='moveBN'>
            <input type='hidden' value='".$row['CategoryName']."' name='moveCN'>
            <input type='hidden' value='".$row['NSN_number']."' name='moveNSN'>
            <input type='hidden' value='".$row['Image']."' name='moveimage'>
                <table class=searcht style='background-color:$bgcolor'>
                    <tbody>
                        <tr>
                            <td value='".$row['PartNum']."' name='movepart'>".$row['PartNum']."</td>
                            <td value='".$row['ModelNum']."' name='movemodelnum'>".$row['ModelNum']."</td>
                            <td>".$row['Country']."</td>
                            <td>".$row['Region']."</td>
                            <td>".$row['Location']."</td>
                            <td>".$row['Placeid']."</td>
                            <td style='width:100px' value='".$row['UnitNum']."' name='moveunitnum'>".$row['UnitNum']."</td>
                            <td style='width:50px;' value='".$row['QTY']."' name='moveqty'>".$row['QTY']."</td>
                            <th style='width:50px; border-right:none;'><input style='width:20px; text-align:center;' value='0' type=text name='moveamt'></th>
                        </tr>
                    </tbody>
                </table>
            </div>";
            $i++;
        }
        echo "<tr><td></td><td><input class='submit' type='submit' value='Confirm' name='submitPlacemove' onclick=\"return confirm ('Are you sure you want to submit?')\"></td></tr></form>";
    }
}

I figure I need to use some sort of JavaScript but I'm new to it. Any help is appreciated.

The search is actually based off of a link from another table. Im not sure if that matters, but otherwise if someone is inputting a search field i'm using mysqli_real_escape_string — egar, Jun 07 '19 at 16:06
Please don't. `mysqli_real_escape_string` is a legacy, and should not be used in the new code. Use prepared statements. — Dharman, Jun 07 '19 at 16:08

cssyphus · Accepted Answer · 2019-06-07T16:45:26.483

I assume there is a Done button that the user will press when ready to scan the table for changes and update the database.

read and store the current values of the table (before any user changes)
When Done button clicked, scan table row-by-row and compare stored value with current value
When find a change, send the data over to a PHP file whose job is to update the back-end table with the new data (Note: you will need to send both the new data AND an item id, so it knows where to put the new data)

Here is a very rough, untested, simple example of what the code might look like:

var arr_old = [];
var arr_new = [];
$.each( $('table tr'), function(i, v){
  if (i==0) return true; //ignore header row (return true === continue)
  let currval = $(this).find('td:nth-child(3) index').val();
  arr_old.push(currval);
});

$('#btnDone').click(function(){
  $.each( $('table tr'), function(i, v){
    if (i==0) return true; //ignore header row (return true === continue)
    let row_id = $(this).find('td:nth-child(1)').text(); //1 or 2 or ...
    let newval = $(this).find('td:nth-child(3) index').val();
    if ( newval !== arr_old[i+1] ){
        $.ajax({
          type = 'post',
           url = 'path/to/your/php_file.php',
          data = 'item_id=' +row_id+ '&new_val=' +newval
        }).done(function(recd){
          console.log('Updated row ' + recd);
        });
    }
});

table{border-collapse:collapse;}
th,td{border:1px solid #ccc;}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>

<table>
  <tr><th>ID</th><th>Col 1</th><th>Edit Col</th></tr>
  <tr><td>1</td><td>Summat</td><td><input class="bob" type="text" value="car" /></td></tr>
  <tr><td>2</td><td>Other</td><td><input class="bob" type="text" value="bike" /></td></tr>
  <tr><td>3</td><td>Summat</td><td><input class="bob" type="text" value="foot" /></td></tr>
</table>

<button id="btnDone">Done</button>

If you are new to javascript, I suggest using jQuery for these reasons

References:

update list with jquery & ajax

http://learn.jquery.com/about-jquery/how-jquery-works/

SLAKS jQuery Tutorial - Use right-arrow to display next bit of text

How to update each row of a dynamic PHP table

1 Answers1