Update database column with checkbox

Question

I am having trouble getting a column to update to yes if checked. I'm not getting any errors though. What am I doing wrong? I've tried a number of methods including leaving the array in and out of quotes. I know how to do this via a form w/ an email address but not checkboxes. I also know how to delete rows from a database using checkboxes. But not update...

  <?php 
      $id = $_GET['id'];
      $select = mysql_query("SELECT * FROM volsMain WHERE ID = '$id'");
      $data = mysql_fetch_array($select);
  ?>

  <?php
      $query="SELECT * FROM volsMain ORDER BY shift_times, position";
      $result = mysql_query($query) or die(mysql_error()); 
      while ($row = mysql_fetch_array($result)){    
  ?>

  <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <input type="hidden" name="id" value="<?php echo $id; ?>" />
    <input type="checkbox" name="checkinsat" value="yes"<?php
            if($data['checkinsat'] == 'yes') echo 'checked'; ?>/>
        <input type="submit" >
  </form>

  <?php
   if ($row['saturday'] === 'A.M.') {
       echo '<span class="amShift">';
   } else if ($row['saturday'] === 'P.M.') {
       echo '<span class="pmShift">';
   } else {
    echo '<span>';
   }
    echo "SATURDAY:" . $row['saturday'] . '</span><br />';

   if ($row['sunday'] === 'A.M.') {
    echo '<span class="amShift">';
   } else if ($row['sunday'] === 'P.M.') {
    echo '<span class="pmShift">';
   } else {
    echo '<span>';
   }

   echo "SUNDAY:". $row['sunday'] . '</span><br />';
   echo "<br />SHIRT SIZE:<h2>" . $row['shirt'] . "</h2>";
   echo "<br />VOLUNTEER NAME:<h2>" . $row['agreeName'] . "</h2>";
   echo "<p>Assigned as a volunteer for:<br />" . $row['position'] . "</p>";
   echo "<p>Shift times are scheduled for:<br />" . $row['shift_times'] . "</p>";
   echo "<p>Shifts have been confirmed:<br />" . $row['confirmed'] . "</p>";
   echo "<p>Checked in Friday:<br />" . $row['checkinfri'] . "</p>";
   echo "<p>Checked in Saturday:<br />" . $row['checkinsat'] . "</p>";
   echo "<p>Checked in Sunday:<br />" . $row['checkinsun'] . "</p>";
}
?>

<?php 
   $checkSAT = isset($_POST['checkinsat']) ? "yes" : "no";
   $updateSat = mysql_query("UPDATE volsMain SET checkinsat = '$checkSAT' WHERE ID = '$id'");
   $query = mysql_query($updateSat); 
   $result = mysql_query($query);
   echo "<p>" . print_r($checkSAT) . "</p>";
?>

Answer 1

Brother !

1. Did you notice that you are putting form tag inside while loop ? Check the view source of this generated web page and see how many html form tags are showing there ? Its a wrong approach to put html form tag inside a loop. You should be using loop on form elements instead.

2. Instead of saving yes/no in DB column, I would recommend to save 1 & 0 values.

Answer 2

Just by quickly looking at it, could it be that your code:

$updateSat = mysql_query("UPDATE volsMain SET checkinsat = '$checkSAT' WHERE ID = '$id'");

...should instead be:

$updateSat = mysql_query('UPDATE volsMain SET checkinsat = ' . $checkSAT . ' WHERE ID = ' . $id . '');

?

Same should go for:

$select = mysql_query("SELECT * FROM volsMain WHERE ID = '$id'");

to:

$select = mysql_query('SELECT * FROM volsMain WHERE ID = ' . $id . '');

Here is something that for sure is sketchy though:

    $updateSat = mysql_query("UPDATE volsMain SET checkinsat = '$checkSAT' WHERE ID =  '$id'");
$query = mysql_query($updateSat); 
$result = mysql_query($query);

$updateSat is not a pure SQL query and should not be called in "mysql_query". If you want it to be named $result, just do:

        $result = mysql_query("UPDATE volsMain SET checkinsat = '$checkSAT' WHERE ID =  '$id'");

Answer 3

SET checkinsat = '$checkSAT' ...

Variable is case-sensitive in PHP.

Answer 4

   $checkSAT = isset($_POST['checkinsat']) ? "yes" : "no";