Mysql查询的案例条件

Question

I am facing a problem. I could not find the error. I have a database name test. There are some field on that like Id, cl1, cl2,cl3....... My expectation is when I select(using checkbox) some id database table field(cl1) will updated by 1 and rest of updated by 3. I think it can make some idea of my job. Here is the screenshot of my database. https://www.dropbox.com/s/meijmf69dkkb5o2/db.JPG?dl=0 Here is the screenshot of my Interface. https://www.dropbox.com/s/fc1b186ly0mda8u/interface.JPG?dl=0 When I select id 1001,1003. It will show different result Here is my code: I am trying it for long time.

if (isset($_POST["sub"])) {
 $values = $_POST['check'];
 $max=sizeof($values);
 $valuesArr = array();
 foreach ($values as $a){
 $valuesArr[] = mysql_real_escape_string($a);
                      }
 for ($i=0; $i <$max ; $i++) { 
  $query2="UPDATE test SET cl1 = CASE WHEN Id = '$valuesArr[$i]' THEN 1 ELSE 3 END";
 $result_upp=mysqli_query($con,$query2);

     }

}

In HTML part

   while($row = mysqli_fetch_array($result2))
       {
        ?>
       <tr class="success">
          <td> <?php echo $row['Id'] ?> </td>
         <td >&nbsp&nbsp<input type="checkbox" name="check[]" class="chk_val" value="<?php echo $row['Id']?>"/></td>

        <td style="background-color: <?php echo ($row['cl1'] == "1") ? "green": "#FF4500" ?>; color:<?php echo ($row['cl1'] == "1") ? "green": "#FF4500" ?>; "> 
        <?php echo $row['cl1'] ?> 
         </td>
        <td style="background-color: <?php if($row['cl2'] == "1")echo "green"; if($row['cl2'] == "2")echo "gainsboro"; if($row['cl2'] == "3")echo "yellow" ?>; color:<?php if($row['cl2'] == "1")echo "green"; if($row['cl2'] == "2")echo "gainsboro"; if($row['cl2'] == "3")echo "yellow" ?>; ">  
         <?php echo $row['cl2'] ?> 
          </td>
        <td style="background-color: <?php echo ($row['cl3'] == "1") ? "green": "#FF4500" ?>; color:<?php echo ($row['cl3'] == "1") ? "green": "#FF4500" ?>; ">  
        <?php echo $row['cl3'] ?> 
        </td>
       <td style="background-color: <?php echo ($row['cl4'] == "1") ? "green": "#FF4500" ?>; color:<?php echo ($row['cl4'] == "1") ? "green": "#FF4500" ?>; ">  
        <?php echo $row['cl4'] ?> 
       </td>
      <td style="background-color: <?php echo ($row['cl5'] == "1") ? "green": "#FF4500" ?>; color:<?php echo ($row['cl5'] == "1") ? "green": "#FF4500" ?>; ">  
      <?php echo $row['cl5'] ?> 
         </td>

       <?php         
           }
        ?>
    </tr>
   </tbody>            
   </table>
   <div class="pull-right">
   <input type="submit" name="sub" class="btn btn-info" value="Submit Attendance">
     </div>
     </form>

Answer 1

In you loop for ($i=0; $i <$max ; $i++), it looks like you are trying to update the rows one at a time to either 1 or 3.

But what your query is ACTUALLY doing, is updating all the rows in your database on every iteration of your loop.

In the last iteration of your loop, the corresponding row will be updated to have cl1 = 1, and EVERY other cl1 = 3, overiding any/all previous updates.

Try this instead:

if (isset($_POST["sub"])) {
    $values = $_POST['check'];
    $max=sizeof($values);
    $valuesArr = array();

    foreach ($values as $a){
        $valuesArr[] = "'".mysql_real_escape_string($a)."'";}  //I added single quotes, because mysql_real_escape_string() only works on string

    $query2 = "UPDATE test SET cl1 = CASE WHEN Id in (".implode(',', $valuesArr).") THEN 1 ELSE 3 END";
    $result_upp=mysqli_query($con,$query2);  //run this query once to update all rows to the correct value
}