Problem updating database through form

[stevet70]

I've a series of PHP pages on a website which allow you to either update your subscription details or unsubscribe.

Firstly you enter your email address and, presuming you enter a valid one, your details are taken from the MySQL database table and listed followed by two buttons - one to update the details and the other to unsubscribe (unsubscribe works fine).

On the page to update your subscription details there are text fields for name and email address which are working fine, then comes a series of check boxes for different types of interests which are giving mixed results.

The check boxes are set up so that they 'remember' if you're currently subscribed to them or not - so any you are will already be checked. You should then be able to uncheck or check as required before hitting Update.

When checking a box that was previously unchecked it updates the database just fine.

If you uncheck one that started as checked it isn't updating the database to reflect this. No idea why.

here's the initial bit of scripting above the doctype that retrieves the persons info based on what's submitted on the previous page to identify them and preps for updating the database

<?php
include('../db_cbo_connect.php');
include('../magic_quotes.php');
// remove backslashes
nukeMagicQuotes();
// initialize flag
$done = false;
// prepare an array of expected items
$expected = array('first', 'last', 'email', 'exhibitions', 'events', 'education', 'newsletter', 'press', 'id');
 // create database connection
$conn = dbConnect('admin');
// get details of selected record
if ($_POST && !$_GET) {
  if (isset($_POST['id']) && is_numeric($_POST['id'])) {
    $id = $_POST['id'];
	}
  else {
    $id = NULL;
	}
  if ($id) {
    $sql = "SELECT * FROM subscribe WHERE id=\"$id\"";
  $result = mysql_query($sql) or die (mysql_error());
  $row = mysql_fetch_assoc($result);
  $exhibitions = $row['exhibitions'];
  $events = $row['events'];
  $education = $row['education'];
  $newsletter = $row['newsletter'];
  $press = $row['press'];
	}
  }
// if form has been submitted, update record
if (array_key_exists('update', $_POST)) {
  // prepare expected items for insertion in to database
  foreach ($_POST as $key => $value) {
    if (in_array($key, $expected)) {
      ${$key} = mysql_real_escape_string($value);
      }
    }
  // abandon the process if primary key invalid
  if (!is_numeric($id)) {
    die('Invalid request');
	}
  // prepare the SQL query
  $sql = "UPDATE subscribe SET first = '$first', last = '$last', email = '$email', exhibitions = '$exhibitions', events = '$events', education = '$education', newsletter = '$newsletter', press = '$press' 
          WHERE id=\"$id\"";
  // submit the query and redirect if successful
  $done = mysql_query($sql) or die(mysql_error());
  }
// redirect page on success or if $id is invalid
  if ($done) {
    header('Location: subscribe_update_thankyou.php');
  exit;
 }
?>

here's the actual form part of the page

<?php if (empty($row)) {
?>
<br />
<strong>Invalid email address</strong><br />
<a href="subscribe_change.php" target="_parent">Click here</a> to enter your email address again.
<?php } 
else {
?>

To update your subscription please amend the details below.</div>

<div id="form">
<form id="form1" method="post" action="" onsubmit="return checkForm(this)">
  <table width="460" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td width="140" height="30" align="left" valign="top">First Name</td>
      <td width="320" height="30" align="left" valign="top"><input name="first" type="text" id="first name" tabindex="1" size="32" value="<?php echo htmlentities($row['first']); ?>"/></td>
    </tr>
    <tr>
      <td width="140" height="30" align="left" valign="top">Last Name</td>
      <td width="320" height="30" align="left" valign="top"><input name="last" type="text" id="last name" tabindex="2" size="32" value="<?php echo htmlentities($row['last']); ?>"/></td>
    </tr>
    <tr>
      <td width="140" height="45" align="left" valign="top">Email Address</td>
      <td width="320" height="45" align="left" valign="top"><input name="email" type="text" id="email" tabindex="3" size="32" value="<?php echo htmlentities($row['email']); ?>"/></td>
    </tr>
    <tr>
      <td width="140" height="30" align="left" valign="top">Interests</td>
      <td width="320" height="30" align="left" valign="top"><input type="checkbox" name="exhibitions" id="exhibitions" value="yes" <?php if ($exhibitions == 'yes') { ?> checked="checked" /> <?php } else { ?> /> <?php } ?>
        Exhibitions</td>
    </tr> 
    <tr>
      <td width="140" height="30" align="left" valign="top">&nbsp;</td>
      <td width="320" height="30" align="left" valign="top"><input type="checkbox" name="events" id="events" value="yes" <?php if ($events == 'yes') { ?> checked="checked" /> <?php } else { ?> /> <?php } ?>
        Events</td>
    </tr>
    <tr>
      <td width="140" height="30" align="left" valign="top">&nbsp;</td>
      <td width="320" height="30" align="left" valign="top"><input type="checkbox" name="education" id="education" value="yes"  <?php if ($education == 'yes') { ?> checked="checked" /> <?php } else { ?> /> <?php } ?>
        Education</td>
    </tr>
    <tr>
      <td width="140" height="30" align="left" valign="top">&nbsp;</td>
      <td width="320" height="30" align="left" valign="top"><input type="checkbox" name="newsletter" id="newsletter" value="yes"  <?php if ($newsletter == 'yes') { ?> checked="checked" /> <?php } else { ?> /> <?php } ?>
        Newsletter</td>
    </tr>
    <tr>
      <td width="140" height="45" align="left" valign="top">&nbsp;</td>
      <td width="320" height="45" align="left" valign="top"><input type="checkbox" name="press" id="press" value="yes"  <?php if ($press == 'yes') { ?> checked="checked" /> <?php } else { ?> /> <?php } ?>
        Press</td>
    </tr>
    <tr>
      <td width="140" height="30" align="left" valign="top"><input name="id" type="hidden" value="<?php echo $row['id']; ?>" /></td>
      <td width="320" height="30" align="left" valign="top"><input type="submit" name="update" value="Update Subscription" /></td>
    </tr>
  </table>
</form>
<?php } ?>
</div>
</div>

</body>
</html>

any ideas about what I'm missing?

many thanks
Steve

[iana02]

I'll give it a try, I haven't tried the code myself though...

The checked = "checked" part is in HTML. It has no idea that it is included right after a PHP code section, so it does not know that it is not supposed to be displayed. What you need to do would be something like this:

<?php

if ($events == 'yes') {
echo 'checked="checked" />';
}
else {
echo '/>'
}
?>

In other words, you have to use "echo" to display your HTML. You can't take the HTML out of the PHP code and expect it to react to what is in PHP. Use your browser to see the source code (there's always an option), you probably would have spotted that the HTML code wasn't right even though it may have looked right on the page.

You may have other errors, but try this first and we'll see what happens next.

[descalzo]

If you uncheck one that started as checked it isn't updating the database to reflect this.

Not updating just that field, or is the entire update failing? Do you check to see if the query updated a row? Are you getting any error/warning messages?

What are the allowed values for the checkbox data fields?

Try printing out the value of

$sql = "UPDATE subscribe SET first = '$first', last = '$last', email = '$email', exhibitions = '$exhibitions', events = '$events', education = '$education', newsletter = '$newsletter', press = '$press'
WHERE id=\"$id\"";

to see what it actually says when you uncheck a field.

[misson]

If you uncheck one that started as checked it isn't updating the database to reflect this. No idea why.
...
any ideas about what I'm missing?

An unchecked checkbox isn't included in the submitted form data. For example, if you submit:

<form>
    <input name="foo" type="checkbox" value="bar" checked />
    <input name="bam" type="checkbox" value="bug-AWWK!" checked />
    <input name="qux" type="checkbox" />
    <input type="submit" />
</form>

the query string will be "foo=bar&bam=bug-AWWK!". Field "qux" isn't included.

Relevant sections of the HTML 4 standard:

• "checkbox" control type
• successful controls

BBCode tip: if you use [php] or [html] tags rather than the generic [code] tag, you'll get a colorized version that is very helpful in finding syntax errors.

<ul>
  <li><a href="foo"/></li>
  <li><a href="bar /></li>
  <li><a href="baz"/></li>
</ul>