CODEWITHSUNDEEP
mysql
Mark Henry
Mark Henry

Reputation: 2699

Insert record without values being 0

I use this query to insert record and prevent 0 being inserted for the snowdepth. I use NULL but is this the best way to do it?

if ($snow_mountain==0) {$snow_mountain=null;}
if ($snow_valley_min==0) {$snow_valley_min=null;}
if ($snow_valley_max==0) {$snow_valley_max=null;}

        $rQuery13 = "UPDATE sv_snow 
SET snow_valley_min=$snow_valley_min, snow_valley_max=$snow_valley_max,snow_mountain=$snow_mountain, templo='$templo', temphi='$temphi', lifts_open='$lifts', ski_id=$ski_id 
WHERE res_id=$res_id AND lud='$today'";

      echo $rQuery13;
    $rResult13 = mysql_query($rQuery13);

Upvotes: 0

Views: 57

Answers (3)

Uours
Uours

Reputation: 2492

Better way is to avoid using NULL and instead of NULL to consider some value you would never expect it to have . Mostly I end up using a negative value .

But for some reason if you have to use NULL , first change the column to allow NULL , example :

ALTER TABLE  `sv_snowalert`
      CHANGE `snow_valley_min`
             `snow_valley_min` INT( 11 ) NULL DEFAULT NULL ;

Then you can insert NULL value , example :

INSERT INTO `sv_snowalert` ( `snow_valley_min` ) VALUES ( NULL );

Also you could use IF( ) function in your UPDATE query :

$snow_mountain = 0;             // For test
$res_id = 1;                        // For test
$today = '2013-11-11 00:00:00'; // For test

$rQuery13 = "
UPDATE
    sv_snow
SET
    snow_mountain
        = IF( '" .$snow_mountain ."' = '0' ,NULL ,'" .$snow_mountain ."' )
WHERE
        res_id = '" .$res_id ."'
    AND lud = '" .$today ."'";

echo $rQuery13;

Upvotes: 1

Krish R
Krish R

Reputation: 22711

It is all depends on the datatype of the column, if you defined as int , then it default value is 0 and defined asvarchar with null, then by its default value is null.

                $rQuery13 = "UPDATE sv_snowalert
                SET snow_valley_min='".$snow_valley_min."', snow_valley_max='".$snow_valley_max."',snow_mountain='".$snow_mountain."', templo='".$templo."', temphi='".$temphi."', lifts_open='".$lifts."', ski_id='".$ski_id."'
                WHERE res_id='".$res_id."' AND lud='".$today."' ";

                echo $rQuery13;
                $rResult13 = mysql_query($rQuery13);

Upvotes: 1

Esqarrouth
Esqarrouth

Reputation: 39181

You can always use set null as default in the database settings and not specify any attributes for those 3.

But null can sometimes be messy, I usually give -1 or false or -1000 something really specific.

Upvotes: 0

Related Questions