How to update a MySQL column based on a checkbox i

2019-08-14 05:10发布

问题:

I am rather a new addition to this forum. and to coding with PHP so as novice I would like to have all your help.

The problem I face is as follows.

I have a MySQL table 'Announce' with the following fields

id
----------
advert
----------
date
----------
file
----------
approv
----------

to which data gets populated from a page - in which all the columns gets its value except approv. To populate the field there is a new PHP page. The field approv gets the value 'approved' based on the tick of the checkbox in it

The problem I face is that I can't read the values of the text box that display the id and get the corresponding checkbox value so that the particular record gets approved and gets updated to the MySQL table.

The code I wrote is given below

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org     
/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Annpouncements | Pending List</title>
<style type="text/css">
.textinput {
height: 20px;
width: 20px;
border-top-width: 0px;
border-right-width: 0px;
border-bottom-width: 0px;
border-left-width: 0px;
border-top-style: none;
border-right-style: none;
border-bottom-style: none;
border-left-style: none;
}
</style>
</head>

<body>

<table width="800" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td height="164"><form id="form1" name="form1" method="post" action="">
      <table width="100%" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td width="11%" height="31" align="center"><label>Id</label></td>
          <td width="15%" height="31" align="center"><label>Date</label></td>
          <td width="52%" align="center"><label>Title</label></td>
          <td width="22%" align="center"><label>Status</label></td>

        </tr>
<?php
//Open the table announce from the database and list date in descending order
$result = mysql_query("SELECT * FROM announce ORDER by date DESC" )or  
die(mysql_error());

//Define a variable to get the rows of the table
  $ann = mysql_fetch_array($result);

//Define a variable to get the no of rows 
  $num = mysql_num_rows($result);
  $i=0;

while($i<$num) {?>
    <?php $approv[$i]= mysql_result($result,$i,"approv"); ?>

        <tr>
            <?php if($approv[$i] !== "approved"){?> 
            <td height="36" align="center"><input name="id" type="text" 
            class="textinput" id="id" value="<?php echo mysql_result($result,$i,"id"); 
            ?>" /></td>
           <?php $ids = mysql_result($result,$i,"id"); 
    //$inp = $_POST["id"][$i];
    //echo 'Input value : ' .$inp. '<br/>' ?>
            <td height="36" align="center"><label>
            <?php echo  mysql_result($result,$i,"date"); ?></label></td>
            <td align="center"><label><?php echo mysql_result($result,$i,"advert"); 
             ?></label></td>

            <td align="center"></label><input type="checkbox" name="approv[]" />
              <label for="approv"></label></td>
             <?php $idan = mysql_query("SELECT * FROM announce WHERE id == $ids"); ?>
             <?php 
               if (isset($_POST['button'])) 
        {
              $apprv = $_POST["approv"];
          //echo 'id = '.$ids.'<br/>';
           $how_many = count($apprv);
         //echo 'Row selected' .$how_many. '<br/>'; 
           foreach ($_POST['approv'] as $apprValue)
           $txtvalue[] = $_POST[$apprValue];
           echo 'txtvalue = ' .$txtvalue. '<br/>';
           mysql_query("UPDATE announce SET approv = 'approved'WHERE id == 
                   $idan ");
                      }
    } 
         ?>
  <?php } ?>

        </tr>

        <?php 
        $i++;
      }
    ?>

        <tr>
          <td height="44">&nbsp;</td>
          <td>&nbsp;</td>
          <td align="center">&nbsp;</td>
          <td align="center"><input type="submit" name="button" id="button" 
           value="Submit" /></td>
        </tr>

      </table>
    </form></td>

             </tr>
           </table></td>
        </tr>
     </table>

So please help me with getting the correct solution for updating the table with the value 'approved' for only those rows that has been checked.

Looking forward for your valuable help ASAP

回答1:

A few issues that I found in your queries

This

SELECT * FROM announce WHERE id == $ids

Should be

SELECT * FROM announce WHERE id = '$ids'

And this

UPDATE announce SET approv = 'approved'WHERE id == $idan

Should be

UPDATE announce SET approv = 'approved' WHERE id = '$idan'

Your checkbox doesn't have a value attribute too

<input type="checkbox" name="approv[]" value="<echo your table row id here>" />

Then use

foreach($_POST['approv'] as $apprValue)
{
    mysql_query(UPDATE announce SET approv = 'approved' WHERE id = '$apprValue');
}

On a completely side note, please don't use mysql_* functions anymore. They are going to be deprecated soon. Better to go for mysqli or PDO



回答2:

Ok, so there are a number of issues here, but I'm going to take a stab at the issue being with the lines of code that are generating your checkboxes. Specifically the following bits:

 <input name="id" type="text" class="textinput" id="id" value="<?php echo mysql_result($result,$i,"id"); ?>" />
 <input type="checkbox" name="approv[]" />

This just isn't a reliable way to create the information that you want, for a number of reasons.

  1. $_POST['id'] will only contain a single value. It will not create a map of ids to approval states that seems to be your intent.
  2. $_POST['approv'] will be a zero-indexed array, leaving you no useful way to correlate a checkbox to an id without keeping a map of id to loop counts. (But if you're going to do that, just use the constructs PHP gives you.)

Ok so, now we know we need to make some changes. First we'll try to get the checkboxes working. But we'll still have the issue of some of your code's structure. Especially since it looks like you don't understand how a request works at all with PHP.

First, we want a way to make sure we know the approval state for each id. That's actually easily remedied. We can just change:

<input type="checkbox" name="approv[]" />

to

<input type="checkbox" name="approv[]" value="<?= $id ?>" />

That way, when you loop over $_POST['approv'], you actually get an id, rather than just an offset. This will allow you to keep the same for loop at the bottom.

BUT THERE ARE STILL ISSUES

There is no reason to be looking at $_POST within your rendering loop (while($i<$num)). Use PHP constructs to your advantage. foreach($result_rows as $row) is much more idiomatic and readable than your code:

//Define a variable to get the rows of the table
  $ann = mysql_fetch_array($result);

//Define a variable to get the no of rows 
  $num = mysql_num_rows($result);
  $i=0;

while($i<$num) {?>
    ... // do work
    ... // Do something with $_POST. <-- wrong!!!
<? } ?>

Should be something more like:

if (empty($_POST)) {
    // Only run the query when we need to display information
    $result = mysql_query("SELECT * FROM announce ORDER by date DESC") or die(mysql_error());

    //Define a variable to get the rows of the table
    $result_rows = mysql_fetch_array($result);
    foreach($result_rows as $row) { ?>
        ... // Display form
    <? }
} else {
    $success = array();
    foreach($_POST['approv'] as $approved_id) {
        // Ideally we would combine all of these updates into one statement
        $stmt = mysqli_prepare($link, "UPDATE announce SET approv = 'approved' WHERE id = ?i");
        mysqli_stmt_bind_params($stmt, $approved_id);
        // You then can look over the $success array to see if any failed.
        $success[$approved_id] = mysqli_stmt_execute($stmt);
    }
}