使用复选框和PHP更新MySQL数据库

Question

I am trying to update a MySQL database with checkboxes and PHP. I have read a lot of code examples online and read many questions on here but I seem to be stuck at the last hurdle.

My code first queries MySQL to bring back a list of users and then a checkbox (which is either 0 or 1 in MySQL) next to each one, indicating whether or not the user is completed.

What I am wanting to do is when the checkbox is checked, for that to update the MySQL database and update the column with 1, or if it is unchecked, for it to change the column to 2.

Here is my code so far:

HTML Snippet (Checkboxes):

        while($row = mysqli_fetch_array($result))
    {
        echo "<tr>";
            echo "<td>" . $row['Firstname'] . "</td>";
            echo "<td>" . $row['Surname'] . "</td>";
            echo "<td>" . $row['Department'] . "</td>";
            echo "<td>" . $row['RequestedBy'] . "</td>";
            echo "<td>" . $row['StartDate'] . "</td>";
            echo "<td class='tbl-chk'> <input type='checkbox' name='WSCompleted' value='"; echo $row['ID'] . "'" ; if ($row['WSCompleted']=='1') { echo "checked='checked'";} echo "/></td>";

Here is my jQuery that successfully retrieves the values and IDs and then posts them:

        $(document).ready(function(){
        $('input[name=WSCompleted]').click(function(){
            var wsCompleted = $(this).is(':checked') ? 1 : 0;
            var wsCompletedID = $(this).attr('value');

            $.ajax({
            type: "POST",
            url: "/usr-handler.php",
            data: {id: wsCompletedID, wsCompleted: wsCompleted},
            success: function(){
                $('div.success').fadeIn();
            }
        });
        return true;

        }); 
    });

And finally here is a snippet of my PHP:

$wsCompleted = $_POST['wsCompleted'];   
$id = $_POST['wsCompletedID'];

$query = "UPDATE newusers SET WSCompleted = '$wsCompleted' WHERE id = '$id'";

mysqli_query($con, $query) or die(mysqli_error());


mysqli_close($con);

I am setting the value of the checkbox to what is actually the row ID in MySQL, then I can use the checkbox value to select the correct row in MySQL, and update it.

The problem I am having is that currently, how the code is, I get the following response from FireBug:

Unidentified index: WSCompletedID

After looking online it was suggested to change:

$id = $_POST['wsCompletedID'];

To:

$id = (ISSET($_POST['wsCompletedID']));

But doing so clears the error message, but then doesn't actually update the value on MySQL.

If I manually set the $id to something, the update works, but obviously that isn't right as it would only ever update the ID I have chosen it to.

I am completely stumped as to what is causing the problem. I have tried finding out online but cannot get anywhere with it.

Any help is greatly appreciated.

Thank you

Answer 1

You are loading data here in your javascript AJAX code

data: {id: wsCompletedID, wsCompleted: wsCompleted},

This will create the following $_POST array

id => whatever was in wsCompletedID
wsCompleted => whatever was in wsCompleted

So your PHP code should be looking for $_POST['id'] and $_POST['wsCompleted'] as these are the names you have given in your data:...

$wsCompleted = $_POST['wsCompleted'];   
$id = $_POST['id'];

$query = "UPDATE newusers SET WSCompleted = '$wsCompleted' WHERE id = '$id'";

mysqli_query($con, $query) or die(mysqli_error());
mysqli_close($con);

HOWEVER: Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared statement and parameterized statements