Re: [PHP-DB] NULL VALUE
first off it would help if you specified what DB you are using and if you are using a DB abstraction lib like PEAR::DB or something similar. also have you tried running these lines directly in a cmdline sql client? is the status column variable width? if not then the value may be 'Active ' (number of padded spaces dependent on length of the field) instead of 'Active'. LightBulbMoment (tm): 5 seconds of searching on the MYSQL site tells me STATUS is a keyword. try either renaming your field or using backticks to escape the name e.g.: $query = 'UPDATE EMPLOYEE SET `STATUS`=\'Inactive\' where `STATUS` IS NULL'; I'm willing to bet you haven't bother to: a. RT(F)M. b. check the errors returned. Perry, Matthew (Fire Marshal's Office) wrote: My status column in my Employee table should have two values Active and Inactive. Right now all the active employees have the value Active and the rest have a NULL value. Why is it that the following commands do nothing? UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS != 'Active'; UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS 'Active'; these should work - expect for the STATUS keyword issue. UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS IS NULL'; this one has a typo (extra ' at the end) UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS = ''; UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS = NULL; these 2 shouldn't work at all. - Matthew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] NULL VALUE
I have a feeling that 'Status' field may be the problem. The word Status is used by MySQL for other sql commands. Either place the fieldname in backticks (the key under the ESC key) like so: UPDATE `EMPLOYEE` SET `STATUS`='Inactive' where `STATUS` != 'Active'; or change the fieldname to emp_status or something like that. The additional benefit of changing the name is to be able to link it quickly to the table that the field comes from emp_status from employees table and so on. Also, set the default value for these types of columns. ( Note: to save db space, use 1 for Active and 0 for Inactive) Bastien From: Perry, Matthew (Fire Marshal's Office) [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] NULL VALUE Date: Mon, 3 Jan 2005 11:40:52 -0600 My status column in my Employee table should have two values Active and Inactive. Right now all the active employees have the value Active and the rest have a NULL value. Why is it that the following commands do nothing? UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS != 'Active'; UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS 'Active'; UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS IS NULL'; UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS = ''; UPDATE EMPLOYEE SET STATUS='Inactive' where STATUS = NULL; - Matthew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] NULL VALUE
-Original Message- From: Jochem Maas [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 12:16 PM Subject: Re: [PHP-DB] NULL VALUE LightBulbMoment (tm): 5 seconds of searching on the MYSQL site tells me STATUS is a keyword. try either renaming your field or using backticks to escape the name e.g.: http://dev.mysql.com/doc/mysql/en/Reserved_words.html doesn't list status, although I am familiar with its various forms e.g. show status. You're probably right, but I don't exactly see this as a 'clearly documented'. Still, backticking all field names is a good idea anyway. Builds character. Cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL VALUE
Norland, Martin wrote: -Original Message- From: Jochem Maas [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 12:16 PM Subject: Re: [PHP-DB] NULL VALUE LightBulbMoment (tm): 5 seconds of searching on the MYSQL site tells me STATUS is a keyword. try either renaming your field or using backticks to escape the name e.g.: http://dev.mysql.com/doc/mysql/en/Reserved_words.html doesn't list status, although I am familiar with its various forms e.g. show status. I didn't even go as far looking there I made the assumption that mysql was crapping out on 'STATUS' because of its use in 'SHOW STATUS' query (and the like) - also its hard to determine whats going wrong with someones setup when you have no idea what version of stuff they are running - bare in mind we assume the chap (Matthew Perry) in question is using MySQL but he did not state this so that may be incorrect. at any rate dumping the output of mysql_error() or mysqli_error() would probably have saved him an email to this list. ;-) You're probably right, but I don't exactly see this as a 'clearly documented'. Still, backticking all field names is a good idea anyway. I didn't mean to say it was clearly documented (IMHO reading understanding documentation is one of the hardest parts of programming!) but it took me exactly 5 seconds to determine with a 95% certainty that 'STATUS' was causing his query to crap out. HINT TO 'BEGINNERS': 1. give background to your problem. (context, software versions etc) 2. show that you have taken the time to research it. 3. google on how to successfully pose a question on a mailing list - there are a number of very explicit essays on the subject! 4. don't give up, we have all struggled for hours/days/weeks on the tiniest of problems. 5. don't assume somebody will do it for you if you can't be bothered! 6. expect the occasional RTFM, even when you're doing everything right ;-) Builds character. :-) Cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] NULL VALUE
I apologize for my lack of information concerning this problem and I
appreciate the advice for beginners.
I also did not mean to post it on this list (If you notice my original
message also contained a thank you for a question never asked in this list).
I am terribly sorry about this error.
HOWEVER: The question can regardless be addressed in this list since it is
not appear to be version or database specific. I am using MySQL 4.1 but
have experienced the same problem with MS SQL Server.
The issue here is NOT whether STATUS is a keyword. Yes STATUS is a keyword
and indeed it would probably be wise if I did not use keywords as column
names. The reason I chose to leave this column named STATUS is that it is
linked to other databases (ouch!) and such attempts have proven to cause
even more problems.
But in theory this does not matter. One can use keywords as column names in
all recent versions of MySQL even though this is unadvised (you have to
search the MySQL site for longer than 5 seconds to learn this). The problem
exists for all column names. I have experimented with other test tables
with more standard column names and have had the same result.
mysql_error() or mysqli_error() also do not help here. Even if you enter
the SQL command directly it posts a result that changes 0 rows and the query
completes without any errors.
I believe this problem lies in how SQL handles NULL values. Let me change
the column name to avoid the question concerning STATUS as a keyword.
UPDATE EMPLOYEE SET STATUS_lsdkrjg='Inactive' where STATUS != 'Active';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS 'Active';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS IS NULL';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS = '';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS = NULL;
None of these work to update table Employee.
According to some of my SQL reference manuals they should!
I have tried the following and had success with one of these options:
create table TEST (
testA varchar(30),
testB varchar(30)
);
insert into TEST values ('a', 'a');
insert into TEST values (NULL, 'a');
+++
| testA | testB |
+++
| a | a |
| [NULL] | a |
+++
UPDATE TEST set testA='b' where testA='';
UPDATE TEST set testA='b' where testA'a';
+++
| testA | testB |
+++
| a | a |
| [NULL] | a |
+++
Finally I tried this:
UPDATE TEST set testA='b' where testA IS NULL;
+++
| testA | testB |
+++
| a | a |
| b | a |
+++
But the last example does not work for ANY fields in my EMPLOYEES table. I
thought at first the problem might have arisen with the fact that this table
is linked to other databases, but even after I removed the links this
problem exists!
I think the problem still lies with my understanding of NULL.
What am I forgetting about the nature of NULL values?
Why won't the following work for NULL values?
UPDATE TEST set testA='b' where testA='';
UPDATE TEST set testA='b' where testA'a';
How is it possible that all NULL values equal any string?
If this question does not belong in this list I will make no additional
postings concerning this topic and will continue researching other sources.
Once again I apologize for the original posting. It was meant to go to one
of my associates (who also does not know the answer!).
- Matthew
-Original Message-
From: Jochem Maas [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 2:16 PM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] NULL VALUE
Norland, Martin wrote:
-Original Message-
From: Jochem Maas [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 12:16 PM
Subject: Re: [PHP-DB] NULL VALUE
LightBulbMoment (tm): 5 seconds of searching on the MYSQL site tells
me
STATUS is a keyword. try either renaming your field or using backticks
to escape the name e.g.:
http://dev.mysql.com/doc/mysql/en/Reserved_words.html doesn't list
status, although I am familiar with its various forms e.g. show
status.
I didn't even go as far looking there I made the assumption that
mysql was crapping out on 'STATUS' because of its use in 'SHOW STATUS'
query (and the like) - also its hard to determine whats going wrong with
someones setup when you have no idea what version of stuff they are
running - bare in mind we assume the chap (Matthew Perry) in question is
using MySQL but he did not state this so that may be incorrect.
at any rate dumping the output of mysql_error() or mysqli_error() would
probably have saved him an email to this list. ;-)
You're probably right, but I don't exactly see this as a 'clearly
documented'. Still, backticking all field names is a good idea anyway.
I didn't mean to say it was clearly documented (IMHO reading
understanding documentation is one of the hardest parts
Re: [PHP-DB] NULL VALUE
Hi Matthew,
there are some more comments below but just for the record could you
confirm that you are retrieving one or more rows when you issue the
following SQL:
SELECT * FROM `employee` WHERE `status` IS NULL;
if you are getting no rows back then the obviously updating based on the
status field being NULL will update nothing.
Perry, Matthew (Fire Marshal's Office) wrote:
I apologize for my lack of information concerning this problem and I
appreciate the advice for beginners.
I also did not mean to post it on this list (If you notice my original
message also contained a thank you for a question never asked in this list).
I am terribly sorry about this error.
well don't be too sorry :-) ... spilt milk and all that!
HOWEVER: The question can regardless be addressed in this list since it is
not appear to be version or database specific. I am using MySQL 4.1 but
have experienced the same problem with MS SQL Server.
ok. Don't have any experience with MSSQL. (and I'm more of a
firebird/interbase kinda guy these days)
The issue here is NOT whether STATUS is a keyword. Yes STATUS is a keyword
and indeed it would probably be wise if I did not use keywords as column
names. The reason I chose to leave this column named STATUS is that it is
linked to other databases (ouch!) and such attempts have proven to cause
even more problems.
ouch indeed.
But in theory this does not matter. One can use keywords as column names in
all recent versions of MySQL even though this is unadvised (you have to
search the MySQL site for longer than 5 seconds to learn this). The problem
;-) you are correct in this, although I would never ever use keywords as
column names without quoting (i.e. backticks in MySQL) those names in
all queries (even if it does work without quotes on certains
versions/dbs/platforms/etc)
exists for all column names. I have experimented with other test tables
with more standard column names and have had the same result.
mysql_error() or mysqli_error() also do not help here. Even if you enter
the SQL command directly it posts a result that changes 0 rows and the query
completes without any errors.
I believe this problem lies in how SQL handles NULL values. Let me change
lets be clear here: SQL in a generic term, there are various ANSI
standards that define the SQL specifications BUT no one implementation
full supports any standard - the handling of the NULL value is DB
dependent (that is to say there will be probably inconsistencies)
[the great thing about standard is there are so many to choose from ;-)]
the column name to avoid the question concerning STATUS as a keyword.
UPDATE EMPLOYEE SET STATUS_lsdkrjg='Inactive' where STATUS != 'Active';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS 'Active';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS IS NULL';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS = '';
UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS = NULL;
None of these work to update table Employee.
that does not surprise me, only half of the 'STATUS' fieldnames in those
queries have had their names changed
According to some of my SQL reference manuals they should!
I have tried the following and had success with one of these options:
create table TEST (
testA varchar(30),
testB varchar(30)
);
insert into TEST values ('a', 'a');
insert into TEST values (NULL, 'a');
+++
| testA | testB |
+++
| a | a |
| [NULL] | a |
+++
UPDATE TEST set testA='b' where testA='';
UPDATE TEST set testA='b' where testA'a';
+++
| testA | testB |
+++
| a | a |
| [NULL] | a |
+++
Finally I tried this:
UPDATE TEST set testA='b' where testA IS NULL;
this last statement is the proper way of doing it.
+++
| testA | testB |
+++
| a | a |
| b | a |
+++
But the last example does not work for ANY fields in my EMPLOYEES table. I
thought at first the problem might have arisen with the fact that this table
is linked to other databases, but even after I removed the links this
problem exists!
I think the problem still lies with my understanding of NULL.
you should realise that you can never do something like:
SELECT * FROM mytable WHERE myfield=NULL
the reason being that the NULL value is akin to 'unknown' and having an
unknown value be equal to another unknown value is rather illogical (at
least that how the gurus of old decided we should live - if you catch my
drift)
in short NULL is not equal to anything at all, ever - NULL is not even
equal to NULL.
What am I forgetting about the nature of NULL values?
Why won't the following work for NULL values?
UPDATE TEST set testA='b' where testA='';
NULL is not equal to an empty string. see above. also check out the
searchable online version of the manual at mysql.com where they (try to)
explain this in alot more
