Jump to content

Set Empty variables NULL


bravo14

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.