| Author |
Topic |
|
mbosecke
Starting Member
3 Posts |
 Posted - 2010-05-27 : 15:47:15
|
| In the database I have a column for gender which is a bit data type. If I set the value to null (UPDATE Persons SET Gender = null WHERE Person_ID = '538') then the change IS reflected in the database, I double checked. The problem is that when I try to retrieve the value again (SELECT Gender FROM Persons WHERE Person_ID='538') it gives me a 0! And a zero represents female. In other words, I can't have genderless people for some reason. Why doesn't the SELECT statement differentiate between the bit type being null or zero? How can I solve or get around this? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-27 : 16:23:02
|
I have tested it.My select always returns NULL.What is the difference / what happens between:the change IS reflected in the database, I double checkedand The problem is that when I try to retrieve the value again
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
mbosecke
Starting Member
3 Posts |
Posted - 2010-05-27 : 16:51:07
|
If I do SELECT Gender, Person_ID FROM Persons WHERE Gender=null then it will return the following:Array( [gender] => 0 [person_id] => 538) The gender for this entry is obviously stored as null in the database, and the SELECT statement is obviously retrieving it properly as expected. The problem has been reduced to the fact that it's printing a 0 when it should print a null. The rest of my php program can't differentiate between these. I'm using the PEAR framework and now I'm thinking the problem lies there, and this question is now in the wrong forum. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-27 : 16:53:44
|
quote:
I'm using the PEAR framework and now I'm thinking the problem lies there, and this question is now in the wrong forum.
agreed 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
mbosecke
Starting Member
3 Posts |
Posted - 2010-05-27 : 16:54:35
|
Whether I find out exactly why it's doing this doesn't really matter anymore, I have found a workaround. To find out the gender, instead of:SELECT Gender WHERE Person_ID='538' I will just have to:SELECT COUNT(*) WHERE Person_ID='538' and Gender=null And check the value returned by COUNT. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-27 : 17:00:02
|
cool idea
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-05-28 : 03:05:34
|
| Shouldn't Gender=null be Gender is null?MadhivananFailing to plan is Planning to fail |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-28 : 03:12:10
|
That's right!In MySQL too.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-05-28 : 03:34:06
|
| why operate at bit level at all? susrely the requirement to save diskspace isn't an issue these days. gender can only be a 2 state value - M or F...what's wrong with the traditional CHAR(1) field containing just 2 values?Going down the 0/1 route means you are using coded values for the real state of this data item - and all future coders using this field need to remember 0 = female, 1 = male..... F&M as values for this field are far simpler to handle and remember. If F&M are not local to your language then use some other letters. |
|
|
|
|
Null bit returns as zero