ZERO's and NULL's in form fields

[MargateSteve]

I have got another of those problems that should be easy to solve but I cannot get my head round it!

 

I have a form to enter data into a table and one of the fields can be left blank. However, I'd data is entered in the field it can be a Zero. This is leaving me a problem as I cannot find a way to get the script to insert NULL if the field is blank but actually put the Zero in if that is what is entered.

 

I have tried several permutations of nested if's using empty or isset, but whatever I try, the same result is inserted in both scenarios.

 

The arguments I need to pass are......

If field is empty INSERT null

If field is 0 INSERT 0

If field is not empty and not 0 INSERT field

 

Thanks in advance for any suggestions.

 

Steve

[Pikachu2000]

If I understand you correctly, this should be what you're looking for.

 

$value = isset($_POST['field']) && ctype_digit($_POST['field'])  ? (int) $_POST['field'] : NULL;

[MargateSteve]

That did not work unfortunately, unless I have tried to use it in the wrong context.

 

Whether I leave the field blank, or enter a zero, it still enters a zero into the table.

 

$hg = isset($_POST['homegoals']) && ctype_digit($_POST['homegoals'])  ? (int) $_POST['homegoals'] : NULL;

$thismatch = $_POST['match'];

mysql_query
  (
  "
  UPDATE 
  all_games 
   SET 
   home_goals = ' " .$hg. " '
   WHERE 
   all_games_id = '".$thismatch."' 
  ") or die(mysql_error());  
header("Location: matchupdate.php?thedate=".$_POST['date']."");

[Pikachu2000]

Is there a default value of 0 for the field by chance?

[MargateSteve]

Is there a default value of 0 for the field by chance?

The actual field is DEFAULT NULL and when a new entry is made it shows up as null. There is no need to put a zero in on INSERT as the fields are intended to be initally blank (they are soccer fixtures and will remain blank until the game is played) so I have only tested it via UPDATE.

 

I have actually come up with a solution that is far from elegant but does the job....

if(!empty($_POST['homegoals']))
{
	$hg = "'".$_POST['homegoals']."'";
}
else if ($_POST['homegoals'] == '0')
{
	$hg = '0';
}
else
{
	$hg = 'NULL';	
}

**SNIPPED OTHER POST TESTS**

$thismatch = $_POST['match'];

mysql_query
  (
  "
  UPDATE 
  all_games 
   SET 
   home_goals = " .$hg. ", 
   away_goals = " .$ag. ", 
   attendance = " . $att . " 
   WHERE 
   all_games_id = '".$thismatch."' 
  ") or die(mysql_error());  
header("Location: matchupdate.php?thedate=".$_POST['date']."");

}

[Pikachu2000]

I think I see what was causing the problem with the code I posted. The quotes in the query string around $hg cause the value to be interpreted as a string, so it was attempting to set the field to the string literal 'NULL'.

[MargateSteve]

I think I see what was causing the problem with the code I posted. The quotes in the query string around $hg cause the value to be interpreted as a string, so it was attempting to set the field to the string literal 'NULL'.

 

So if I used your code with home_goals = $hg in the query string it should work? It is late now and am off to bed but will give that a go in the morning.

 

Thanks

Steve

[Pikachu2000]

I believe it should. Give it a try and let me know what happens tomorrow.

[Pikachu2000]

Actually, it's probably ending up as an empty string because of the quotes, instead of the string 'NULL'. Either way, as long as the expected value is numeric, removing the quotes should fix it.

[MargateSteve]

I tried it again and it threw up a MySQL error on that part, so I tried again but wrapped the NULL in the code you provided in single quotes

$hg = isset($_POST['homegoals']) && ctype_digit($_POST['homegoals'])  ? (int) $_POST['homegoals'] : 'NULL';

 

Now it works perfectly. All that is left is for me to understand what it is doing! My assumption is....

 

$hg = isset($_POST['homegoals']) && ctype_digit($_POST['homegoals'])

Checks if the field is set and if it is set is it a number?

 

? (int) $_POST['homegoals']

If it is a number, insert the field

 

: 'NULL';

If not set or not a number, enter NULL

 

Thanks for the help.

Steve

[Pikachu2000]

Pretty close. That's called ternary syntax, and it's essentially an abbreviated if/else conditional. There's more about it here]http://us.php.net/manual/en/language.operators.comparison.php]here. Scroll down a little bit to example #2.