Nelson Oko-oza Nelson Oko-oza - 4 months ago 14
MySQL Question

How to update html repeated field values to mysql database

I have a html repeated values in array that i need to update to database, i need a help on how to use php script to update to database where staffid mataches . See below my HTML code



<!DOCTYPE html>
<html>

<head lang="en">
<meta chartaxt="UTF-8">
<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet">
<script src="js/jquery.min.js"></script>
<script src="bootstrap/js/bootstrap.min.js"></script>
<title>Sample salary computation</title>

</head>

<body>
<form method="POST" name="regis" id="regis">
<table align="center" border="1" width="200">
<tr>
<th>Staff ID</th>
<th>Salary</th>
<th>Total Tax</th>
<th>Total Net Pay</th>


</tr>

<!--Staff 1-->
<tr data-id="STAFF/2016/001">
<td>
<input type="text" name="staffid[]" class="staffid" value="STAFF/2016/001" readonly="readonly">
</td>
<td>
<input type="text" name="salary[]" class="salary" placeholder="salary" value="5000">
</td>
<td>
<input type="text" name="tax[]" class="tax" placeholder="tax" value="500">
</td>
<td>
<input type="text" name="total[]" class="total" placeholder="total" readonly="readonly" value="4500">
</td>
</tr>

<!--Staff 2-->

<tr data-id="STAFF/2016/002">
<td>
<input type="text" name="staffid[]" class="staffid" value="STAFF/2016/002" readonly="readonly">
</td>
<td>
<input name="salary[]" class="salary" type="text" placeholder="salary" value="10000">
</td>
<td>
<input type="text" placeholder="tax" name="tax[]" class="tax" value="1000">
</td>
<td>
<input type="text" placeholder="total" class="total" name="total[]" readonly="readonly" value="9000">
</td>
</tr>

<!--Staff 3-->

<tr data-id="STAFF/2016/003">
<td>
<input type="text" name="staffid[]" class="staffid" value="STAFF/2016/003" readonly="readonly">
</td>
<td>
<input name="salary[]" class="salary" type="text" placeholder="salary" value="8400">
</td>
<td>
<input type="text" placeholder="tax" name="tax[]" class="tax" value="400">
</td>
<td>
<input type="text" placeholder="total" class="total" name="total[]" readonly="readonly" value="800">
</td>
</tr>

<tr>
<td width="300">

<input type="button" name="update" value="Update Values" class="btn btn-danger">
</td>
</tr>
</table>
</form>
</body>

</html>





Kindly assist. I want the SQL update query to look like

"Update payroll SET salary ='".$_POST['salary']."', tax='".$_POST['tax']."', total ='".$_POST['total']."' WHERE staffid= '".$_POST['staffid']."'"

Answer

I would recommend to rename the input names to create a single array with all the information you need. Use the staff ID as array keys.

<!--Staff 1-->
<tr data-id="STAFF/2016/001">
    <td>
        <input type="text" name="staff[STAFF/2016/001][id]" class="staffid" value="STAFF/2016/001" readonly="readonly">
    </td>
    <td>
        <input type="text" name="staff[STAFF/2016/001][salary]" class="salary" placeholder="salary" value="5000">
    </td>
    <td>
        <input type="text" name="staff[STAFF/2016/001][tax]" class="tax" placeholder="tax" value="500">
    </td>
    <td>
        <input type="text" name="staff[STAFF/2016/001][total]" class="total" placeholder="total" readonly="readonly" value="4500">
    </td>
</tr>

<!--Staff 2-->

<tr data-id="STAFF/2016/002">
    <td>
        <input type="text" name="staff[STAFF/2016/002][id]" class="staffid" value="STAFF/2016/002" readonly="readonly">
    </td>
    <td>
        <input type="text" name="staff[STAFF/2016/002][salary]" class="salary" placeholder="salary" value="10000">
    </td>
    <td>
        <input type="text" name="staff[STAFF/2016/002][tax]" placeholder="tax" class="tax" value="1000">
    </td>
    <td>
        <input type="text" name="staff[STAFF/2016/002][total]" placeholder="total" class="total" readonly="readonly" value="9000">
    </td>
</tr>

In PHP you can then iterate over this single array:

<?php

$db = new PDO('mysql:host=localhost;dbname=database;', 'root', '');

if (!empty($_POST['staff'])) {
    $stmt = $db->prepare('UPDATE payroll SET salary = :salary, tax = :tax, total = :total WHERE staffid = :staffid');

    foreach ((array)$_POST['staff'] as $staffId => $staffInfo) {

        $stmt->bindValue(':salary', $staffInfo['salary']);
        $stmt->bindValue(':tax', $staffInfo['tax']);
        $stmt->bindValue(':total', $staffInfo['total']);
        $stmt->bindValue(':staffid', $staffId); // you can also use $staffInfo['id'] here instead of $staffId

        $stmt->execute();
    }
}

See also:

Comments