Jump to content

Set Empty variables NULL


bravo14
Go to solution Solved by DavidAM,

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

  • Solution

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.