使用PHP一次更新多个记录

Question

I am trying to update multiple records within an HTML form, but it isn't writing any data back, and I am getting no errors.

The table already exists and has half of the data already in it so I need to update records, not insert.

This is my form:

<form method="post" action="test.php" id="price-increase"></form>
<div class="x_panel">
<div class="x_content">             
    <table id="tablePrice" class="display table table-striped table-bordered dt-responsive">
        <thead>
            <tr>
                <th>Item Code</th>
                <th>Customer Increase</th>
                <th>New Invoice</th>
                <th>New Net</th>
                <th>New Matrix</th>
                <th>New Band A</th>
                <th>Incresed Date</th>
            </tr>
        </thead>
        <tbody>
            <?php while($res = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) : ?>
                <tr>
                    <td><?php echo $res['ItemCode'];?></td>
                    <td>
                        <input type="text" name="customerIncrease" id="customerIncrease" class="form-control" value="<?php if(!empty($res['CustomerIncrease'])){echo $res['CustomerIncrease'];}?>">
                    </td>
                     <td>
                        <input type="text" name="newInvoice" id="newInvoice" class="form-control" value="<?php if(!empty($res['NewInvoice'])){echo $res['NewInvoice'];}?>">
                    </td>
                    <td>
                        <input type="text" name="newNet" id="newNet" class="form-control" value="<?php if(!empty($res['NewNet'])){echo $res['NewNet'];}?>">
                    </td>
                    <td>
                        <input type="text" name="newMX" id="newMX" class="form-control" value="<?php if(!empty($res['NewMX'])){echo $res['NewMX'];}?>">
                    </td>
                    <td><?php echo $res['NewBandA'];?>
                        <input type="text" name="newBandA" id="newBandA" class="form-control" value="<?php if(!empty($res['NewBandA'])){echo $res['NewBandA'];}?>">
                    </td>
                    <td>
                        <input id="increaseDate" name="increaseDate" data-date-format="dd/mm/yyyy" class="form-control col-md-7 col-xs-12" required="required" type="text" value="<?php if(!empty($res['IncreaseDate'])){echo $res['IncreaseDate'];}?>">
                    </td>
                </tr>
            <?php endwhile; ?>
        </tbody>                        
    </table>    
    <a href="test.php">
        <button type="submit" id="submit" name="submit" class="btn btn-success pull-right" value="Submit">Save</button>
    </a>
</div>

This is my PHP:

<?php 
if(isset($_POST['submit'])){
    $itemCode = (isset($_POST['ItemCode']) && !empty($_POST['ItemCode']))?$_POST['ItemCode'] : NULL;
    $customerIncrease = (isset($_POST['CustomerIncrease']) && !empty($_POST['CustomerIncrease']))?$_POST['CustomerIncrease'] : NULL;
    $newInvoice = (isset($_POST['NewInvoice']) && !empty($_POST['NewInvoice']))?$_POST['NewInvoice'] : NULL;
    $newNet = (isset($_POST['NewNet']) && !empty($_POST['NewNet']))?$_POST['NewNet'] : NULL;
    $newMX = (isset($_POST['NewMX']) && !empty($_POST['NewMX']))?$_POST['NewMX'] : NULL;
    $newBandA = (isset($_POST['NewBandA']) && !empty($_POST['NewBandA']))?$_POST['NewBandA'] : NULL;
    $increaseDate = (isset($_POST['IncreaseDate']) && !empty($_POST['IncreaseDate']))?$_POST['IncreaseDate'] : NULL;
    $processed = (isset($_POST['Processed']) && !empty($_POST['Processed']))?$_POST['Processed'] : NULL;

    $query = "  UPDATE po_SupplierPriceIncrease 

                SET CustomerIncrease = '$customerIncrease',
                    NewInvoice = '$newInvoice',
                    NewNet = '$newNet',
                    NewMX = '$newMX',
                    NewBandA = '$newBandA',
                    IncreaseDate = '$increaseDate',
                    Processed = '$processed'

                WHERE ItemCode = '$itemCode';
                    ";
    $stmt = sqlsrv_prepare($sapconn2, $query);
    sqlsrv_execute($stmt);   
    return $stmt;

    }
?>

Like I said, it won't update and it has no errors. Am I doing something wrong here?

Answer 1

There are a couple flaws with the HTML form:

1. The form tag is immediately closed after it is opened - i.e.<form id="tablePrice"...></form> So move the closing tag (i.e. </form>) after any form elements that need to be submitted.
2. The ItemCode is not being submitted with the form. Create an input (perhaps it should be hidden and/or read-only) to submit that value - e.g. <td><input type="Number" name="ItemCode" value="<?php echo $res['ItemCode'];?>" readonly /></td>
3. Because an input element can have no permitted content, they are self closing, so add the (forward) slash (i.e. /) to the end of each input tag -<input type="text" name="NewMX" id="newMX" class="form-control" value="<?php if(!empty($res['NewMX'])){echo $res['NewMX'];}?>" />

As far as being able to Update multiple records at once (per your question title), in order to do that, you would likely need to update your UPDATE SQL query to have some logic based on the ItemCode value (e.g. with CASE statements). And the form fields would need to be in array format (e.g. CustomerIncrease[]) or have unique names (perhaps with the ItemCode value appended -e.g. CustomerIncrease_1) in order to associate various values with the row to update.

And as @Magnus Eriksson suggested, you should use a prepared statement (with bound parameters) to avoid SQL injection attacks. So you could simplify your PHP code like the example below, utilizing the 3rd parameter of sqlsrv_prepare() - an array of parameters.

params:
An array specifying parameter information when executing a parameterized query.

Note: you would need to update the case of the name attributes on the <input> fields to match the names in $fields - e.g. <input type="text" name="NewNet"...>

if(isset($_POST['submit']) && $_POST['ItemCode']) {
    //these fields should match the name attribute of the inputs in the form
    $fields = array('CustomerIncrease','NewInvoice','NewNet','NewMX','NewBandA','IncreaseDate'    );
    $params = array();
    $setFields = array();
    foreach($fields as $field) {
        if (isset($_POST[$field]) && !empty($_POST[$field])) {
            $params[] = &$_POST[$field];
            $setFields[] = $field.' = ?';
        }
        else {
            $setFields[] = $field.' = NULL';
        }
    }

    //optional : add ProcessedDate to setFields with value from GetDate()?
    $params[] = &$_POST['ItemCode'];

    $query = "  UPDATE po_SupplierPriceIncrease 
            SET ".implode(', ',$setFields)."
            WHERE ItemCode = ?";
    $stmt = sqlsrv_prepare($connection, $query,$params);
    sqlsrv_execute($stmt);
}