subject:"Re\: trouble with field NULL"

Re: trouble with field NULL

2002-05-12 Thread Neil Zanella

I will guess that the NULL field you defined is of a numeric
type such as INT. In standard SQL the empty string '' is not
the same as the special value NULL, and this is also true in
MySQL, but not in Oracle. Probably you are inserting a ''
into the database column but MySQL correctly interprets
it as a string. The default conversion for string to
integers in MySQL is to turn the string into a 0.
This is probably what is happening.

I would suggest writing your own string function in your
application which converts instances of '' into instances
of NULL. And by the way, quoting numeric data in MySQL
as well as in PostgreSQL and Oracle is acceptable but
not required.

Bye,

Neil

On Fri, 10 May 2002, raphael k wrote:

> I created a table with a field define as NULL, however when I insert
> values NULL , Mysql puts 0 instead of nothing ,
> 
> I don't understand why I have this trouble , 
> 
>  
> 
> Thanks 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: trouble with field NULL

2002-05-10 Thread Paul DuBois


At 13:59 -0500 5/10/02, Steve Buehler wrote:
>At 01:22 PM 5/10/2002, Paul DuBois wrote:
>>At 13:11 -0500 5/10/02, Steve Buehler wrote:
>>>I have found that I can NOT do this:
>>>$null = "NULL" or even $null = NULL than put that into a statement 
>>>like this:
>>>UPDATE games SET game_id = '$null';
>>>can't do it like this either
>>>UPDATE games SET game_id = $null;
>>>I have to do it like this:
>>>UPDATE games SET game_id = NULL;
>>>NO QUOTES of any kind.  Somebody else might have a way around 
>>>this. In that case, I would like to hear it too.  That would let 
>>>me get around the following:
>>
>>None of those will work, because you test for NULL using
>>IS NULL, not = NULL.
>
>The above ones aren't testing for NULL, they are trying to assign 
>NULL to a $var.  The below ones aren't either.  The below code is 
>testing for "".  Might be the same thing, but it was the only way I 
>could get it to work for me.  I didn't try:
>if($loc_id IS NULL)
>This code is in PHP.

Ah, yeah.  Sheesh.

Okay, two solutions, one of which you indicate above:

- SET col_name = NULL, with no quotes around NULL (because otherwise it'll
   be taken as a string consisting of the word "NULL"
- Fake (emulate) some kind of placeholder mechanism, and then bind either
   an unset value (e.g., unset ($var)) or the PHP 4 value NULL to a placeholder
   that you want to be NUL>.  But this actually works out to the same thing,
   because the placeholder mechanism would have to put the unquoted word NULL
   into the query string when it recognizes the PHP value that you define by
   convention to represent SQL NULL values.

>
>>>$searchStmt = "UPDATE games SET
>>>date = '$date',
>>>time = '$time',";
>>>if($loc_id == ""){$searchStmt .= "loc_id = 
>>>NULL,";}else{$searchStmt .= "loc_id = '$loc_id',";}
>>>if($hteam_pt == ""){$searchStmt .= "hteam_pt = 
>>>NULL,";}else{$searchStmt .= "hteam_pt = '$hteam_pt',";}
>>>if($vteam_pt == ""){$searchStmt .= "vteam_pt = 
>>>NULL,";}else{$searchStmt .= "vteam_pt = '$vteam_pt',";}
>>>if($sea_id == ""){$searchStmt .= "sea_id = 
>>>NULL,";}else{$searchStmt .= "sea_id = '$sea_id',";}
>>>if($hteam == ""){$searchStmt .= "hteam = NULL,";}else{$searchStmt 
>>>.= "hteam = '$hteam',";}
>>>if($vteam == ""){$searchStmt .= "vteam = NULL,";}else{$searchStmt 
>>>.= "vteam = '$vteam',";}
>>>if($hteam_forfeit == ""){$searchStmt .= "hteam_forfeit = 
>>>NULL,";}else{$searchStmt .= "hteam_forfeit = '1',";}
>>>if($vteam_forfeit == ""){$searchStmt .= "vteam_forfeit = 
>>>NULL,";}else{$searchStmt .= "vteam_forfeit = '1',";}
>>>if($official_1 == ""){$searchStmt .= "official_1 = 
>>>NULL,";}else{$searchStmt .= "official_1 = '$official_1',";}
>>>if($official_2 == ""){$searchStmt .= "official_2 = 
>>>NULL,";}else{$searchStmt .= "official_2 = '$official_2',";}
>>>if($official_3 == ""){$searchStmt .= "official_3 = 
>>>NULL,";}else{$searchStmt .= "official_3 = '$official_3',";}
>>>if($official_4 == ""){$searchStmt .= "official_4 = 
>>>NULL,";}else{$searchStmt .= "official_4 = '$official_4',";}
>>>if($official_5 == ""){$searchStmt .= "official_5 = NULL 
>>>";}else{$searchStmt .= "official_5 = '$official_5' ";}
>>>$searchStmt .= "where game_id = '$game_id'" ;
>>>
>>>The previous is in a PHP script calling a MySQL DB.
>>>
>>>Steve
>>>
>>>
>>>At 12:22 PM 5/10/2002, raphael k wrote:
I created a table with a field define as NULL, however when I insert
values NULL , Mysql puts 0 instead of nothing ,

I don't understand why I have this trouble ,


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: trouble with field NULL

2002-05-10 Thread Steve Buehler


At 01:22 PM 5/10/2002, Paul DuBois wrote:
>At 13:11 -0500 5/10/02, Steve Buehler wrote:
>>I have found that I can NOT do this:
>>$null = "NULL" or even $null = NULL than put that into a statement like this:
>>UPDATE games SET game_id = '$null';
>>can't do it like this either
>>UPDATE games SET game_id = $null;
>>I have to do it like this:
>>UPDATE games SET game_id = NULL;
>>NO QUOTES of any kind.  Somebody else might have a way around this. In 
>>that case, I would like to hear it too.  That would let me get around the 
>>following:
>
>None of those will work, because you test for NULL using
>IS NULL, not = NULL.

The above ones aren't testing for NULL, they are trying to assign NULL to a 
$var.  The below ones aren't either.  The below code is testing for 
"".  Might be the same thing, but it was the only way I could get it to 
work for me.  I didn't try:
if($loc_id IS NULL)
This code is in PHP.

>>$searchStmt = "UPDATE games SET
>>date = '$date',
>>time = '$time',";
>>if($loc_id == ""){$searchStmt .= "loc_id = NULL,";}else{$searchStmt .= 
>>"loc_id = '$loc_id',";}
>>if($hteam_pt == ""){$searchStmt .= "hteam_pt = NULL,";}else{$searchStmt 
>>.= "hteam_pt = '$hteam_pt',";}
>>if($vteam_pt == ""){$searchStmt .= "vteam_pt = NULL,";}else{$searchStmt 
>>.= "vteam_pt = '$vteam_pt',";}
>>if($sea_id == ""){$searchStmt .= "sea_id = NULL,";}else{$searchStmt .= 
>>"sea_id = '$sea_id',";}
>>if($hteam == ""){$searchStmt .= "hteam = NULL,";}else{$searchStmt .= 
>>"hteam = '$hteam',";}
>>if($vteam == ""){$searchStmt .= "vteam = NULL,";}else{$searchStmt .= 
>>"vteam = '$vteam',";}
>>if($hteam_forfeit == ""){$searchStmt .= "hteam_forfeit = 
>>NULL,";}else{$searchStmt .= "hteam_forfeit = '1',";}
>>if($vteam_forfeit == ""){$searchStmt .= "vteam_forfeit = 
>>NULL,";}else{$searchStmt .= "vteam_forfeit = '1',";}
>>if($official_1 == ""){$searchStmt .= "official_1 = 
>>NULL,";}else{$searchStmt .= "official_1 = '$official_1',";}
>>if($official_2 == ""){$searchStmt .= "official_2 = 
>>NULL,";}else{$searchStmt .= "official_2 = '$official_2',";}
>>if($official_3 == ""){$searchStmt .= "official_3 = 
>>NULL,";}else{$searchStmt .= "official_3 = '$official_3',";}
>>if($official_4 == ""){$searchStmt .= "official_4 = 
>>NULL,";}else{$searchStmt .= "official_4 = '$official_4',";}
>>if($official_5 == ""){$searchStmt .= "official_5 = NULL 
>>";}else{$searchStmt .= "official_5 = '$official_5' ";}
>>$searchStmt .= "where game_id = '$game_id'" ;
>>
>>The previous is in a PHP script calling a MySQL DB.
>>
>>Steve
>>
>>
>>At 12:22 PM 5/10/2002, raphael k wrote:
>>>I created a table with a field define as NULL, however when I insert
>>>values NULL , Mysql puts 0 instead of nothing ,
>>>
>>>I don't understand why I have this trouble ,


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: trouble with field NULL

2002-05-10 Thread Paul DuBois


At 13:11 -0500 5/10/02, Steve Buehler wrote:
>I have found that I can NOT do this:
>$null = "NULL" or even $null = NULL than put that into a statement like this:
>UPDATE games SET game_id = '$null';
>can't do it like this either
>UPDATE games SET game_id = $null;
>I have to do it like this:
>UPDATE games SET game_id = NULL;
>NO QUOTES of any kind.  Somebody else might have a way around this. 
>In that case, I would like to hear it too.  That would let me get 
>around the following:

None of those will work, because you test for NULL using
IS NULL, not = NULL.

>$searchStmt = "UPDATE games SET
>date = '$date',
>time = '$time',";
>if($loc_id == ""){$searchStmt .= "loc_id = NULL,";}else{$searchStmt 
>.= "loc_id = '$loc_id',";}
>if($hteam_pt == ""){$searchStmt .= "hteam_pt = 
>NULL,";}else{$searchStmt .= "hteam_pt = '$hteam_pt',";}
>if($vteam_pt == ""){$searchStmt .= "vteam_pt = 
>NULL,";}else{$searchStmt .= "vteam_pt = '$vteam_pt',";}
>if($sea_id == ""){$searchStmt .= "sea_id = NULL,";}else{$searchStmt 
>.= "sea_id = '$sea_id',";}
>if($hteam == ""){$searchStmt .= "hteam = NULL,";}else{$searchStmt .= 
>"hteam = '$hteam',";}
>if($vteam == ""){$searchStmt .= "vteam = NULL,";}else{$searchStmt .= 
>"vteam = '$vteam',";}
>if($hteam_forfeit == ""){$searchStmt .= "hteam_forfeit = 
>NULL,";}else{$searchStmt .= "hteam_forfeit = '1',";}
>if($vteam_forfeit == ""){$searchStmt .= "vteam_forfeit = 
>NULL,";}else{$searchStmt .= "vteam_forfeit = '1',";}
>if($official_1 == ""){$searchStmt .= "official_1 = 
>NULL,";}else{$searchStmt .= "official_1 = '$official_1',";}
>if($official_2 == ""){$searchStmt .= "official_2 = 
>NULL,";}else{$searchStmt .= "official_2 = '$official_2',";}
>if($official_3 == ""){$searchStmt .= "official_3 = 
>NULL,";}else{$searchStmt .= "official_3 = '$official_3',";}
>if($official_4 == ""){$searchStmt .= "official_4 = 
>NULL,";}else{$searchStmt .= "official_4 = '$official_4',";}
>if($official_5 == ""){$searchStmt .= "official_5 = NULL 
>";}else{$searchStmt .= "official_5 = '$official_5' ";}
>$searchStmt .= "where game_id = '$game_id'" ;
>
>The previous is in a PHP script calling a MySQL DB.
>
>Steve
>
>
>At 12:22 PM 5/10/2002, raphael k wrote:
>>I created a table with a field define as NULL, however when I insert
>>values NULL , Mysql puts 0 instead of nothing ,
>>
>>I don't understand why I have this trouble ,


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: trouble with field NULL

2002-05-10 Thread Steve Buehler


I have found that I can NOT do this:
$null = "NULL" or even $null = NULL than put that into a statement like this:
UPDATE games SET game_id = '$null';
can't do it like this either
UPDATE games SET game_id = $null;
I have to do it like this:
UPDATE games SET game_id = NULL;
NO QUOTES of any kind.  Somebody else might have a way around this.  In 
that case, I would like to hear it too.  That would let me get around the 
following:

$searchStmt = "UPDATE games SET
date = '$date',
time = '$time',";
if($loc_id == ""){$searchStmt .= "loc_id = NULL,";}else{$searchStmt .= 
"loc_id = '$loc_id',";}
if($hteam_pt == ""){$searchStmt .= "hteam_pt = NULL,";}else{$searchStmt .= 
"hteam_pt = '$hteam_pt',";}
if($vteam_pt == ""){$searchStmt .= "vteam_pt = NULL,";}else{$searchStmt .= 
"vteam_pt = '$vteam_pt',";}
if($sea_id == ""){$searchStmt .= "sea_id = NULL,";}else{$searchStmt .= 
"sea_id = '$sea_id',";}
if($hteam == ""){$searchStmt .= "hteam = NULL,";}else{$searchStmt .= "hteam 
= '$hteam',";}
if($vteam == ""){$searchStmt .= "vteam = NULL,";}else{$searchStmt .= "vteam 
= '$vteam',";}
if($hteam_forfeit == ""){$searchStmt .= "hteam_forfeit = 
NULL,";}else{$searchStmt .= "hteam_forfeit = '1',";}
if($vteam_forfeit == ""){$searchStmt .= "vteam_forfeit = 
NULL,";}else{$searchStmt .= "vteam_forfeit = '1',";}
if($official_1 == ""){$searchStmt .= "official_1 = NULL,";}else{$searchStmt 
.= "official_1 = '$official_1',";}
if($official_2 == ""){$searchStmt .= "official_2 = NULL,";}else{$searchStmt 
.= "official_2 = '$official_2',";}
if($official_3 == ""){$searchStmt .= "official_3 = NULL,";}else{$searchStmt 
.= "official_3 = '$official_3',";}
if($official_4 == ""){$searchStmt .= "official_4 = NULL,";}else{$searchStmt 
.= "official_4 = '$official_4',";}
if($official_5 == ""){$searchStmt .= "official_5 = NULL ";}else{$searchStmt 
.= "official_5 = '$official_5' ";}
$searchStmt .= "where game_id = '$game_id'" ;

The previous is in a PHP script calling a MySQL DB.

Steve


At 12:22 PM 5/10/2002, raphael k wrote:
>I created a table with a field define as NULL, however when I insert
>values NULL , Mysql puts 0 instead of nothing ,
>
>I don't understand why I have this trouble ,
>
>
>
>Thanks
>
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: trouble with field NULL

2002-05-10 Thread Joseph Bueno


raphael k wrote :
> 
> I created a table with a field define as NULL, however when I insert
> values NULL , Mysql puts 0 instead of nothing ,
> 
> I don't understand why I have this trouble ,
> 
> 
> 
> Thanks
> 

Hi,

If you don't show us your query, it will be hard to help you ...

Regards
--
Joseph Bueno
NetClub/Trader.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: trouble with field NULL

Re: trouble with field NULL

Re: trouble with field NULL

Re: trouble with field NULL

Re: trouble with field NULL

Re: trouble with field NULL

6 matches

Site Navigation

Mail list logo

Footer information