save/reset checkbox value in database

[kokkentor]

I am populating/checking a checkbox if a database field value is 1. I want the database field value set to NULL if unchecking the checkbox. This editing works when manipulating the value via a text field, but not when manipulating via a checkbox. (The checkbox displays correctly (i.e. as checked when the database field value is 1, otherwise as unchecked). I have tried with different data types i the DB. NULL is standard value.

 

Any ideas what is causing this? Is is something wrong in the UPDATE statement that doesn't apply to checkboxes? (If that question makes sense...)

 

Update function:

function update_entry ($conn)
{
# Get script parameters; trim whitespace from the ID, but not
# from the password, because the password must match exactly,
# or from the row, because it is an array.

$member_id = trim (script_param ("member_id"));
$password = script_param ("password");
$row = script_param ("row");

$member_id = trim ($member_id);
if (empty ($member_id))
	die ("No member ID was specified");
if (!preg_match ('/^\d+$/', $member_id))	# must look like integer
	die ("Invalid member ID was specified (must be an integer)");
if (!check_pass ($conn, $member_id, $password)
		&& !check_pass ($conn, 0, $password))
	die ("Invalid password");

# Examine the metadata for the member table to determine whether
# each column allows NULL values.

$info =& $conn->tableInfo ("member");
if (DB::isError ($info))
	die ("Cannot get member table metadata");
$nullable = array ();
for ($i = 0; $i < count ($info); $i++)
{
	$col_info = $info[$i];
	$col_name = $col_info["name"];
	$col_flags = explode (" ", $col_info["flags"]);
	$nullable[$col_name] = TRUE;	# TRUE unless we find not_null
	while (list ($key, $val) = each ($col_flags))
	{
		if ($val == "not_null")
		{
			$nullable[$col_name] = FALSE;
			break;
		}
	}
}

# Iterate through each field in the form, using the values to
# construct the UPDATE statement.

$stmt = "UPDATE member ";
$delim = "SET";
reset ($row);
while (list ($col_name, $val) = each ($row))
{
	$stmt .= "$delim $col_name=";
	$delim = ",";
	# if a form value is empty, update the corresponding column value
	# with NULL if the column is nullable.  This prevents trying to
	# put an empty string into the expiration date column when it
	# should be NULL, for example.
	$val = trim ($val);
	if (empty ($val))
	{
		if ($nullable[$col_name])
			$stmt .= "NULL";	# enter NULL
		else
			$stmt .= "''";		# enter empty string
	}
	else
		$stmt .= $conn->quoteSmart ($val);
}
$stmt .= sprintf (" WHERE member_id = %s", $conn->quoteSmart ($member_id));
$result =& $conn->query ($stmt);
if (DB::isError ($result))
	print ("Member entry was not updated.\n");
else
	print ("Member entry was updated successfully.\n");
printf ("<br /><a href=\"%s\">Edit another member record</a>\n",
		script_name ());
}

 

 

[kokkentor]

My question obviously didn't make sense .... so I tried, by help of a reply, to elaborate here: http://www.phpbuilder.com/board/showthread.php?t=10365247