Set Empty variables NULL

[bravo14]

Hi Guys

 

I have a form that I use to update a table in a database, and if some of the fields are empty I want to set the value of the variable to NULL.  The code I have used is below

 

 

<?php
if(isset($_POST['submit'])){
    $fixture_date=date("Y-m-d",strtotime($_POST['fixture-date']));
 echo $fixture_date;
    $club=$_POST['club'];
    if(empty($_POST['team_score'])){ $brummies=NULL;} else {$brummies=$_POST['team_score'];}
 $opposition=$_POST['opposition'];
    if(empty($_POST['opp_points'])){ $opposition_points=NULL;} else {$opposition_points=$_POST['opp_points'];}
 if(empty($_POST['points'])){$points=NULL;} else {$points=$_POST['points'];}
 if(empty($_POST['bonus'])){$bonus=NULL;} else {$bonus=$_POST['bonus'];}
 $homeaway=$_POST['homeaway'];
 $match_id=$_POST['match_id'];
 if(!empty($_POST['team_score'])&&!empty($_POST['opp_points'])){
 if($brummies>$opposition_points){
 $result="Won";
 }
 elseif($brummies<$opposition_points){
 $result="Lost";
 }
 else{
 $result="Draw";
 }
 }
 else{
 $result = NULL;
 }
 echo $result;
 
    //add event to db
    $add_event=("UPDATE `tbl_fixtures`
                                   SET
           `Opposition`='$opposition',
           `match_date`='$fixture_date',
           `HomeAway`='$homeaway',
           `Result`='$result',
           `brummies`='$brummies',
           `opposition_points`='$opposition_points',
           `points`='$points',
           `bonus_points`='$bonus'
                                   WHERE `match_id`='$match_id'");
           echo $add_event;
           $add_event_sql=mysql_query($add_event)or die(mysql_error());
    $message='The fixture/result has been updated.';
}
?>

 

This is the resultant MySQL query

 

UPDATE `tbl_fixtures` SET `Opposition`='Poole', `match_date`='2013-06-13', `HomeAway`='Away', `Result`='', `brummies`='', `opposition_points`='', `points`='', `bonus_points`='' WHERE `match_id`='14'

 

I want the fields brummies, opposition_points, bonus_points, and points to be set to NULL if they are blank, it currently sets the field to 0.

 

Any help would be much appreciated

[denno020]

I think it would be more on the database side that you would need to set this up.. Make sure that they field is allowed to have NULL values, and then make NULL the default, so if no value is entered, it will be NULL..

 

So you do all that in your database manager (which I assume might be phpmyadmin?).

 

Denno

[bravo14]

The db structure allows for NULL and NULL is set as the default, yet it still inputs 0 if the field is left blank

[Barand]

If you want the field to remain null when it is blank exclude it from the update so you do not provide a value.

[bravo14]

How could I do it as an option, so that if the fields are left blank they are excluded from the query but if they are not blank then they are included in the query.

[DavidAM]

On an UPDATE statement, you really can't leave it to the database. There may already be a value there that needs to be cleared out.

 

You will have to build your query string dynamically. One way to do it is:

$columns = array();
$columns[] = "Opposition = " . ($opposition === null ? "NULL" : "'$opposition'");
$columns[] = "match_date = " . ($fixture_date === null ? "NULL" : "'$fixture_date'");
# ... and so on
$columns[] = "bonus_posting = " . ($bonus === null ? "NULL" : $bonus);

$sql = "UPDATE tbl_fixtures SET " .
		implode(", ", $columns) .
		"WHERE match_id = '$match_id'";

Notes:

- I don't use back-ticks. They are not needed unless the column or table name is a mySql reserved word

- We can't really use empty for the tests since empty() will be true for a zero value or an empty string; so we have to use the exact comparison (three equal-signs).

- You should not put quotes around numeric values in the query

- There are NO quotes around the term NULL, if you put quotes around it (in the query) it will be a string of four characters.