Hi guys,

I am trying to insert a NULL value from a php form into the database when the body_waist field is left blank. At the moment when I do this it just inserts '0'.

The database field for body_waist is INT(3) with NULL set to yes.

This is the edit details code when the ID is passed to this page from the previous page in the url string.

<!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=iso-8859-1" />

<style>
body {font-family:arial;}
.error {font-weight:bold; color:#FF0000;}
</style>

<title>Edit Weight Details</title>
</head>

<body>

<h2>Edit weight details:</h2>
<?
//connect to the database
include 'includes/db_conn.inc.php';

// has the form been submitted?
if ($_POST) {
	foreach($_POST as $k => $v) {
		$v = trim($v);
		$$k = $v;
	}



// build UPDATE query
$update = "UPDATE weight SET
	date='$date', body_weight='$weightkg',
	body_fat='$bodyfat', body_water='$bodywater', body_waist='$bodywaist'
	WHERE Id=$id";


// execute query and check for success
if (!mysqli_query($link, $update)) {
	$msg = "Error updating data";
} else {
	$msg = "Record successfully updated:";

	// write table row confirming data
	$table_row = '
	<tr>
		<td>' . $date . '</td>
		<td>' . $weightkg . '</td>
		<td>' . $bodyfat . '</td>
		<td>' . $bodywater . '</td>
		<td>' . $bodywaist . '</td>
	</tr>';
	}

	// if not posted, check that an Id has been
	// passed via the URL
	} else {
	if (!IsSet($_GET['id'])) {
		$msg = "No customer selected!";
	} else {
		$id = $_GET['id'];

		//build and execute the query
		$select = "SELECT date, body_weight, body_fat, body_water, body_waist FROM weight WHERE Id=$id";
		$result = mysqli_query($link, $select);

		// check that the record exists
		if (mysqli_num_rows($result)<1) {
			$msg = "No customer with that ID found!";
		} else {
		// set vars for form code
		$form_start = "<form method=\"post\"action=\"" . $_SERVER['PHP_SELF'] . "\">";
		$form_end = '
		<tr>
			<td colspan="2"><input type="submit" value="Submit changes" /></td>
			<td colspan="3"><input type="reset" value="Cancel" /></td>
		</tr>
		</form>';

	// assign the results to an array
	while ($row = mysqli_fetch_array($result)) {
		$date = $row['date'];
		$weightkg = $row['body_weight'];
		$bodyfat = $row['body_fat'];
		$bodywater = $row['body_water'];
		$bodywaist = $row['body_waist'];

		// write table row with form fields
		$table_row = '
		<tr>
			<td><input type="text" name="date" value="' . $date . '" size="10" /></td>
			<td><input type="text" name="weightkg" value="' . $weightkg . '" size="10" /></td>
			<td><input type="text" name="bodyfat" value="' . $bodyfat . '" size="10" /></td>
			<td><input type="text" name="bodywater" value="' . $bodywater . '" size="10" /></td>
			<td><input type="text" name="bodywaist" value="' . $bodywaist . '" size="10" /></td>
		</tr>';
	}
	// end 'if record exists' if
	}
//end 'if ID given in URL' if
}
// end 'if form posted' if
}

// close connection
mysqli_close($link);

// print error/success message
echo (IsSet($msg)) ? "<div class=\"error\">$msg</div>" : "";
?>

<table border="1" cellpadding="5">
<!-- Show start-of-form code if form needed -->
<? echo (IsSet($form_start)) ? $form_start : "";
?>

<input type="hidden" name="id" value="<? echo $id ?>" />
	<tr>
		<th>Date</th>
		<th>Weight (Kg)</th>
		<th>Body Fat (%)</th>
		<th>Water (%)</th>
		<th>Waist (cm)</th>
	</tr>
	<!-- Show appropriate table row code (none set if there were errors) -->
	<? echo (IsSet($table_row)) ? $table_row : "";						
?>

<!-- Show end-of-form code if we are displaying the form -->
<? echo (IsSet($form_end)) ? $form_end : ""; ?>
</table>

<br /><a href="index.php">Back to customer list</a>
</body>
</html>

    Try inserting NULL instead of an empty string (that's a plain NULL, not the string 'NULL'). Or leave that field out when you write the INSERT statement.

      I've tried typing NULL into the form but that still ends up as 0 in the database table. The problem is that sometimes I want to add data to this field and other times I was to leave it blank so that it enters NULL into the field.

        I've managed to resolve this issue now. I have changed the UPDATE to the following:

        $update = "UPDATE weight SET 
date='$date', body_weight='$weightkg', body_fat='$bodyfat', body_water='$bodywater', body_waist=IF('$bodywaist'='',NULL,'$bodywaist')
		WHERE Id=$id";
          mundo wrote:

          I've tried typing NULL into the form

          That's not what Weedpacket was suggesting; typing the letters N-U-L-L in sequence has nothing to do with a NULL value.

            Write a Reply...