[SOLVED] Maintain NULL database values when submitting form

[Mr Rich UK]

Hi, was wondering if someone could give me a tip on what I can do.  I have a page where I am editing a record in a database, and five of the fields could have up to a 2 digit numeric value put in them.  If it does have a value above zero then it appears on the relevant page, and the numeric value signifies the order in which the records are presented when the query is run.

 

Now, this page to edit them is obviously part of the backend of my site that I have created.  When editing the record however, these five records (called beauty, visual, cars, kids and comedy) are actually by default NULL in the database.  I would like to keep them as null if no value has been inserted into the form.  But instead at the moment when a record is edited, the NULL values get inserted as zero.  This is not the worst problem in the world as if the value is zero, then it does not appear on any pages anyway, but I would prefer it if they stayed NULL when the form is submitted and the record is changed.  Does anyone have any suggestions on what I could do.

 

Here are the relevant parts of the script that may need changing:

 

// Check if the form has been submitted.
if (isset($_POST['submitted'])) {

$client = $_POST['client'];
$title = $_POST['title'];
$director = $_POST['director'];
$file_name = $_POST['file_name'];
$width = $_POST['width'];
$beauty = $_POST['beauty'];
$visual = $_POST['visual'];
$cars = $_POST['cars'];
$kids = $_POST['kids'];
$comedy = $_POST['comedy'];

 

Here's the query to update

 

$query = "UPDATE films SET client='$client', title='$title', director='$director', file_name='$file_name', width='$width', BEAUTY='$beauty', VISUAL='$visual', CARS='$cars', KIDS='$kids', COMEDY='$comedy' WHERE film_id=$filmid";

 

Here's the form section.

 

<form action="edit_film.php" method="post">
<p><b>Client</b> (Enter name of client in capital letters):<br /><input type="text" name="client" size="40" maxlength="40" value="' . $row[1] . '" /></p>
<p><b>Title</b> (Enter title of commercial in capitals with apostrophes either side:<br /><input type="text" name="title" size="40" maxlength="40" value="' . $row[2] . '" /></p>
<p><b>Director</b> (Enter first name, and initial of surname if applicable, all in lower case with no spaces):<br /><input type="text" name="director" size="20" maxlength="20" value="' . $row[3] . '" /></p>
<p><b>File name</b> (Enter file name without the file extension on the end. Filename<br />
of jpg and mov files should match. E.g. add real_football_43):<br /><input type="text" name="file_name" size="50" maxlength="50" value="' . $row[4] . '"  /> </p>
<p><b>Width</b> (Enter width of quicktime. If 16:9 add 596, if 4:3 add 447):<br /><input type="text" name="width" size="3" maxlength="3" value="' . $row[5] . '"  /> </p>
<p><b>Beauty</b> (Enter number for which position it should appear in reel - If 0 it will not appear):<br /><input type="text" name="beauty" size="2" maxlength="2" value="' . $row[6] . '"  /> </p>
<p><b>Visual</b> (Enter number for which position it should appear in reel - If 0 it will not appear):<br /><input type="text" name="visual" size="2" maxlength="2" value="' . $row[7] . '"  /> </p>
<p><b>Cars</b> (Enter number for which position it should appear in reel - If 0 it will not appear):<br /><input type="text" name="cars" size="2" maxlength="2" value="' . $row[8] . '"  /> </p>
<p><b>Kids</b> (Enter number for which position it should appear in reel - If 0 it will not appear):<br /><input type="text" name="kids" size="2" maxlength="2" value="' . $row[9] . '"  /> </p>
<p><b>Comedy</b> (Enter number for which position it should appear in reel - If 0 it will not appear):<br /><input type="text" name="comedy" size="2" maxlength="2" value="' . $row[10] . '"  /> </p>
<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="film_id" value="' . $filmid . '" />
</form>';

 

Many thanks in advance.

[Mr Rich UK]

I'd been looking for the answer for ages, but I have just found it now.

 

Changed part of the query that needed to maintain NULL if empty as:

 

BEAUTY=IF('$beauty'='',NULL,'$beauty')