如何在php中检查和取消选中复选框时更新mysql表？

Question

Basically, I want to update MySql table corresponding to each checkbox. Here is a schema of my requirement:

I have uploaded files in sql and placed four checkboxes next to each of the files picked up from database[displayed on the web](read, write, download, share). Also, I have four additional columns in database (readFlag, WriteFlag, DownloadFlag, ShareFlag). What I want is, suppose I check the 'read' checkbox next to a particular file, I want to update the readFlag next to that particular file name and not any other file. Similarly for other checkboxes and files as well.

I have written a code for updating the readflag manually, which is working perfect, but it's not working when working with checkboxes. Any suggestions would be of great help. Thanks.

Here is the code I have written so far: I have manually set readFlag to 1 just to check incase in assignMainPage.php.

PHP :

<?php

$host = 'localhost';
$user = '';
$password= '';
$db = 'user_information';

@$conn = mysql_connect($host, $user, $password) or die(mysql_error());
mysql_select_db($db, $conn);

if(!$conn) {
    echo "Connection to the database failed";
}


$result = mysql_query("SELECT * FROM user_files ORDER BY id ASC");   
?>

Jquery :

<script>

$(function() {
    $(".assign").click(function() { 

        var check = $(this).closest('tr').find('input:checkbox');
        var fileID = $(this).closest('tr').find('.file_id').val();

        var element = $(this);
        var assign_id = element.attr("id");
        var assigninfo = 'id=' +assign_id;

        if(confirm("assign")) { 
            $.ajax({
                type: "POST",
                url: "assignMainPage.php",
                data: assigninfo,
                success: function(){

                }
            });

            alert("file id : " + fileID);
        }
    });
});

assignMainPage.php

<?php

$host = 'localhost';
$user = '';
$password= '';
 $db = 'user_information';

@$conn = mysql_connect($host, $user, $password) or die(mysql_error());
mysql_select_db($db, $conn);

if(!$conn) {
     echo "Connection to the database failed";
 }

if($_POST['id']) {

$id = mysql_real_escape_string($_POST['id']);


 $assign = "UPDATE user_files SET readFlag = 1 where id = '$id'";

mysql_query($assign);
}

?>

HTML :

<div class="container">


<?php

while($row = mysql_fetch_array($result)){

    $id1 = $row['id'];
    $name = $row['user_file_name'];
?>

<?php

if(isset($_POST['assign']) && $_POST['assign'] == '$id1') { 
    echo("success");
}

?>

<div class="show">


<table width = "80%"  align = "center">


<tr class="trclass">



<td width= "15%"><span class="name"><?php echo $name; ?> <> <?php echo $id1;?>       </span></td>

<input type="hidden" value="<?php echo $id1; ?>" class="file_id" />



<td width="15%">
    <span class="action"><input type="button" id="<?php echo $id1; ?>" class="delete"  title="Delete" value="Delete <> <?php echo $id1;?>"></span>
</td>



<td width= "10%"><span class="action">
    <input type="checkbox" id="<?php echo $id1; ?>" class="read" title="read">Read <> <?php echo $id1;?></span>
</td>



<td width= "15%"><span class="action"><input type="checkbox" id="<?php echo $id1; ?>" class="write" title="write">Write <> <?php echo $id1;?></span></td>



<td width= "15%"><span class="action"><input type="checkbox" id="<?php echo $id1; ?>" class="download" title="download">Download <> <?php echo $id1;?></span></td>



<td width= "15%"><span class="action"><input type="checkbox" id="<?php echo $id1; ?>" class="share" title="share">Share <> <?php echo $id1;?></span></td>



<td width= "15%">
     <span class="action">
        <input type="submit" id="<?php echo $id1; ?>" name="assign" class="assign" title="assign" value="Assign <> <?php echo $id1; ?>">
     </span>
</td>


</tr>

<table>

</div>


<?php

}

?>

Answer 1

look at: Testing if a checkbox is checked with jQuery

in your ajax call your data doesn't include the checkbox value. also you're not using the ajax data value as an array, which is easier

add something like

$chk_value = check.is(':checked');
$assigninfo = array('id'=>assign_id, 'val'=>$chk_value);

you have several html errors in your code above. most notably you named all your checkboxes with the same ID. and then tried to access them as an array which will either need an $.each construct, or in your case might be simpler to just list the four cases separately. less of an issue <> needs to use < and >.

i've taken a shot at revising to follow the coding style i recommend for similar situations. i haven't had the chance to run it so i apologize if there are minor syntax errors.

a couple of design recommendations - i notate my classes on input elements with what they are for (e.g. chk-read and btn-assign).
- i consider the row to be a big object that you clicked on. so that's the core element. all the stuff you need is then in data attributes on the itself or in class identified tags within the scope. - i use data attributes on the row to provide the information about the record being processed. there is no need to duplicate that on all those item within scope, or to use a hidden input. - not done: in html5 you don't need to use input type=button anymore, you can just use instead.

<div class="container">
    <?php
    while ($row = mysql_fetch_array($result)) {
        $id1  = $row['id'];
        $name = $row['user_file_name'];
        if (isset($_POST['assign']) && $_POST['assign'] == $id1) {
            // this isn't going to work
            echo("success");
        }
    ?>
        <div class="show">
            <table width="80%" align="center">
                <tr class="trclass" id='<?php echo $id1; ?>' data-name='<?php echo $name?>'>
                    <td width= "15%">
                        <span class="name"><?php echo $name; ?> &lt;&gt; <?php echo $id1;?></span>
                    </td>
                    <td width="15%">
                        <span class="action">
                            <input type="button" class="btn-delete" title="Delete" value="Delete &lt;&gt; <?php echo $id1;?>">
                        </span>
                    </td>
                    <td width= "10%"><span class="action">
                        <span class="action">
                            <input type="checkbox" class="chk-read" title="read">Read &lt;&gt; <?php echo $id1;?>
                        </span>
                    </td>
                    <td width= "15%">
                        <span class="action">
                            <input type="checkbox" class="chk-write" title="write">Write &lt;&gt; <?php echo $id1;?>
                        </span>
                    <td width= "15%">
                        <span class="action">
                            <input type="checkbox" class="chk-download" title="download">Download &lt;&gt; <?php echo $id1;?>
                        </span>
                    </td>
                    <td width= "15%">
                        <span class="action">
                            <input type="checkbox" class="chk-share" title="share">Share &lt;&gt; <?php echo $id1;?>
                        </span>                     
                    </td>
                    <td width= "15%">
                         <span class="action">
                            <input type="submit" class="btn-assign" title="assign" value="Assign &lt;&gt; <?php echo $id1; ?>">
                         </span>
                    </td>
                </tr>
            <table>
        </div>
    <?php } ?>
</div>

<script>
$(function() {
    $(".btn-assign").click(function() { 

        var $element    = $(this).closest('tr');
        var data        = {
            id          : $element.attr('id'),
            name        : $element.attr('data-name'),
            read        : $element.find('.chk-read').is(':checked'),
            write       : $element.find('.chk-write').is(':checked'),
            download    : $element.find('.chk-download').is(':checked'),
            share       : $element.find('.chk-share').is(':checked'),
        }

        if (confirm("assign "+data.name)) { 
            $.ajax({
                type: "POST",
                url: "assignMainPage.php",
                data: data,
                success: function() {
                    alert("assignment complete for: file id : " + data.id);
                }
            });

        }
    });
});
</script>