Updating Multiple SQL rows from HTML Table/Form with PHP

Question

I have a table of data, which consists of 4 columns of figures, over time these figures need to be updated, so I would like to do this with a HTML form so I have added some columns to the table with inputs for the new values (I have also added these into my database), but I am really becoming stuck with whats wrong as my form inst updating any fields in my database.

Here is my table/form:

<form method="post" action="test.php" id="price-increase">
<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>Item Name</th>
                    <th>Brand Owner</th>
                    <th>Sales Group</th>
                    <th>Sales Sub Group</th>
                    <th>Current Net</th>
                    <th>Current Matrix</th>
                    <th>Current Band A</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>
                    <th>Processed</th>
                </tr>
            </thead>
            <tbody>
                <?php while($res = sqlsrv_fetch_array($def, SQLSRV_FETCH_ASSOC)) : ?>
                    <tr>
                        <td>
                            <input type="text" name="ItemCode" id="ItemCode" class="form-control" value="<?php if(!empty($res['ItemCode'])){echo $res['ItemCode'];}?>" />
                        </td>
                        <td><?php echo $res['ItemName'];?></td>
                        <td><?php echo $res['BrandOwner'];?></td>
                        <td><?php echo $res['SalesGroup'];?></td>
                        <td><?php echo $res['SalesSubGroup'];?></td>
                        <td><?php echo $res['CurrentNet'];?></td>
                        <td><?php echo $res['CurrentMX'];?></td>
                        <td><?php echo $res['CurrentBandA'];?></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" class="form-control " required="required" type="text" value="<?php if(!empty($res['IncreaseDate'])){echo $res['IncreaseDate'];}?>" />
                        </td>
                        <td><?php echo $res['Processed'];?></td>
                    </tr>
                <?php endwhile; ?>
            </tbody> 
        </table>    

        <button type="submit" id="submit" name="submit" class="btn btn-success pull-right" value="submit">Save</button>
    </div>
</div>

And this is my PHP:

<?php

if(isset($_POST['submit'])){

            $fields = array('ItemCode', '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';
        }
    }

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

When I click submit, nothing happens. Any Ideas?

I get no errors, but this may be because the page isn't actually doing anything, so it may not be even submitting to get the errors if that makes sense. — PHPNewbie
– PHPNewbie, Commented Dec 13, 2016 at 11:25
okay, i added novalidate to the form and now its submitting but i get this error: PHP Notice: Array to string conversion. Its complaining about this: SET '.implode(', ',$setFields).' WHERE ItemCode = ?"; — PHPNewbie
– PHPNewbie, Commented Dec 13, 2016 at 11:35

ImClarky · Accepted Answer · 2016-12-15 10:45:00Z

3

There are a few things wrong with your code. Firstly in the HTML, as you are echoing out several rows and therefore multiple instances of the form fields, you need to make a few changes with your inputs; for example:

<input type="text" name="ItemCode[]" class="..." />

What's happened here is I've added the square brackets [] to the name of the input. This means that if there are multiple inputs of the same name in a form (which in this case there is because each input is "duplicated" for each row) then it creates an array of those values in the post data. I've also removed the id attribute as IDs should be unique.

In the PHP, there are a few problems.

Problem number one, is that you have fewer values in your $params array than placeholders (?) in your $query. $params contains the values of all the fields that you are changing the value of, but you have forgotten to add the value for the WHERE clause of your query. This then leads to problem two...

You don't pass any reference in your form to the old ItemCode. For example if I had a row in the table that has id of 123ABC and I wanted to change that to 456DEF, using the only ItemCode currently available in the post data, the query produced in PHP would look something like:

UPDATE po_SupplierPriceIncrease SET ItemCode = '456DEF', [...] WHERE ItemCode = '456DEF'

If there is not currently a row with ID 456DEF then it is not going to update anything. Or, worse, if there is an ID of 456DEF, then it will overwrite that data.

Problem three... you only execute the query once. If you are wanting to execute the update query for each row, you are going to need to stick it into a loop.

To solve these problems I would do the following:

Firstly to solve problem two, I would add a hidden input field to each row, which contains the current ItemCode to use as a reference when updating the row in the database.

<td>
    <input type="hidden" name="curItemCode[]" value="<?php echo $res['ItemCode']; ?>" />
    <input type="text" name="ItemCode[]" id="ItemCode" class="form-control" value="<?php if(!empty($res['ItemCode'])){echo $res['ItemCode'];}?>" />
</td>

To solve problems one and three, I would do it this way.

if (isset($_POST['submit'])) {
    $fields = ["CustomerIncrease", "NewInvoice", "NewNet", "NewMX", "NewBandA", "IncreaseDate"];

    foreach ($_POST['curItemCode'] as $k => $val) {
        $params = [];
        $setFields = [];

        foreach ($fields as $field) {
            if (isset($_POST[$field][$k]) && !empty($_POST[$field][$k])) {
                $params[] = $_POST[$field][$k];
                $setFields[$field] = $_POST[$field][$k];
            } else {
                $setFields[$field] = null;
            }
        }

        $params[] = $val;

        $query = "UPDATE po_SupplierPriceIncrease SET " . getSetFields($setFields) . " WHERE ItemCode = ?";
        $stmt = sqlsrv_prepare($sapconn2, $query, $params);

        if (sqlsrv_execute($stmt) === false) {
            die(print_r(sqlsrv_errors(), true));
        }
    }
}

function getSetFields($fields) {
    $s = "";
    foreach ($fields as $field => $value) {
        if(!is_null($value)){
            $s .= ($field !== "IncreaseDate") ? "$field = CAST(? as numeric(9,2))," : "$field = ?,";
        } else {
            $s .= "$field = NULL,";
        }
    }
    return rtrim($s, ",");
}

In theory the above should work, though I haven't been able to test it properly.

edited Dec 15, 2016 at 10:45

answered Dec 13, 2016 at 12:43

ImClarky

1,9632 gold badges27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

PHPNewbie Over a year ago

Thanks for this i really appreciate the knowledge and time you have spent. but Its not inserting still and i get this error: Array ( [0] => Array ( [0] => 22007 [SQLSTATE] => 22007 [1] => 242 [code] => 242 [2] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. [message] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. )

ImClarky Over a year ago

@PHPNewbie - How is IncreaseDate formatted ie dd-mm-yyyy mm/dd/yyyy etc?

PHPNewbie Over a year ago

mm/dd/yyyy, but it is set to datetime in the database will that be affecting it?

ImClarky Over a year ago

@PHPNewbie - okay, i've updated the code so that it creates a DateTime object in php for the variable $id. The problem being that the input was delivering a date as a string when, as you pointed out, the database expects a datetime.

PHPNewbie Over a year ago

Let us continue this discussion in chat.

|

Collectives™ on Stack Overflow

Updating Multiple SQL rows from HTML Table/Form with PHP

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related