updating the mysql database table based on checkboxes values which in array using PHP

Question

I am new to PHP Programming.

I am working on a PHP project, where I am populating a dynamic table from mysql database with a checkbox field with each row.

<?php                                          
 $sql = "select * from caseinstruction where fir_nos='$fir_nos'";
      $result = $con->query($sql);

       if ($result->num_rows > 0) {
      // output data of each row
       $i = 1;
       while($row = $result->fetch_assoc()) {  

          echo "<tr><td class='text-center'>". $i++ ."</td>
              <td><input type='text' value='{$row['Instructions']}' class='form-control input-sm' style='border:none'></td>
              <td class='text-center form-group'><input type='checkbox' name='foo[]' value='1'></td>
          </tr>\n";
        }
       }
?>

Its working perfectly. The problem arises when I want to update the same mysql table with the checked value. Suppose if a user select a check box then it will reflect 1 in the mysql table and if not selected any option then it will reflect 0 in mysql table.

I tied to run the foreach command it populates the value as I expect but when I run the update command it just reflect 0. Please help me.

<?php
require('../php-includes/connect.php');

if(isset($_POST) and $_SERVER['REQUEST_METHOD'] == "POST"){
    $fir_nos = $_POST['fir_nos'];
    $cins = $_POST['foo'];

    echo '<pre>';
    print_r($cins);
    echo '</pre>'

    foreach ($$cins as $value) {
        $sql="update mytable set report02='$value'";
        $result = $con->query($sql);
    }
}

I am missing something on this. Please Help me. thanking you all in advance.

# | Instruction | value |
-------------------------
1 | abc         | 1     | //checked
2 | cdf         | 0     | //unckecked
3 | efg         | 1     | //checked
-------------------------

Michael Krikorev · Accepted Answer · 2017-03-14 22:40:48Z

3

You have a few errors in your code:

First: You need to add an identifier for each checkbox if you want to be able to get which checkboxes are checked. If you for example have a column id in table caseinstruction, you could use:

<input type='checkbox' name='foo[]' value='{$row['id']}'>

Second: There is a typo in your code. Change $$cins to $cins

Third: I do not know what you want to do with your update query. Since your example does not have any WHERE-clause specified, you will only get multiple update queries like update mytable set report02='83' and update mytable set report02='84' etc. which will update all rows in the table with one single value over again in your loop.

Instead you need to specify what row and column you want to update with something like update mytable set report02 = 1 where id = '83'. For example:

<?php
require('../php-includes/connect.php');

if(isset($_POST) and $_SERVER['REQUEST_METHOD'] == "POST"){
    $fir_nos = $_POST['fir_nos'];
    $cins = $_POST['foo'];

    foreach ($cins as $value) {
        $sql="update mytable set report02 = 1 where id = '$value'";
        $result = $con->query($sql);
    }
}

I would recommend to use mysqli_real_escape_string() to prevent sql-injection. And for performance reasons use only one update call instead of one for each checkbox value:

<?php
require('../php-includes/connect.php');

if(isset($_POST) and $_SERVER['REQUEST_METHOD'] == "POST"){
    $fir_nos = $_POST['fir_nos'];
    $cins = $_POST['foo'];

    $value = implode(',',$cins); // JOIN ALL ID INTO ONE COMMA SEPARATED STRING
    $value = mysqli_real_escape_string($con, $value); //PREVENT SQL-INJECTION
    // SET VALUE 1 FOR ALLA CHECKED:
    $con->query("update mytable set report02 = 1 where id IN($value)");         
    // SET VALUE 0 FOR ALL THE REST IN THE TABLE:
    $con->query("update mytable set report02 = 0 where id NOT IN($value)"); 
}

edited Mar 14, 2017 at 22:40

answered Mar 11, 2017 at 14:11

Michael Krikorev

2,1661 gold badge18 silver badges28 bronze badges

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

4 Comments

deepakparam Over a year ago

Thank You so much, this what I exactly needed. thank you

deepakparam Over a year ago

I have one more doubt, that it is updating 1 when the checkbox is checked , but i also need to update 0 for the row which are unchecked, please guided me to achieve this too..

Michael Krikorev Over a year ago

Just update all rows in the database table and set them to 0 update mytable set report02 = 0 before you update with 1 for the ones who are checked...

Michael Krikorev Over a year ago

I have updated my answer to show how you can update also the unchecked. Also optimized the code with one db-call instead of a foreach-loop. Let me know if this works for you.

Sehdev · Accepted Answer · 2017-03-11 09:46:18Z

0

You need to change $$cins to $cins in the following line. You are using double dollar sign.

foreach ($$cins as $value) {

}

answered Mar 11, 2017 at 9:46

Sehdev

5,7123 gold badges14 silver badges34 bronze badges

1 Comment

deepakparam Over a year ago

yes I have changed that. and I followed the Michael solution and It is working correctly.

Collectives™ on Stack Overflow

updating the mysql database table based on checkboxes values which in array using PHP

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related