#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    2
    Rep Power
    0
    In the New Riders MySQL book (which is awesome, by the way) page 99, there's a reference to using a CHAR(0) field as a super-efficient ON/OFF flag (it only uses one bit).

    CHAR(0) can have two values, either NULL or the empty string. Using IS NULL, I can detect my column RType (which I set to CHAR(0)) as being NULL or empty string - this does work, as I've checked it with known NULL and non-null values (I've also tried IFNULL and ISNULL() as well).

    However, it seems I can't set RType to anything other than NULL

    In this example, I create a record where Rtype is NULL.

    Then I try to set all the RType values to an empty string ('') to get it to be somethign other than NULL (I even tried setting RType to '1').


    Finally, I do a search for all records where RType is NULL. I'm thinking that this should return NO records, but I get them all back.

    Is there another way to set a column's value(I'm using MySQL 3.23.7) ?

    _____________

    <this is in PHP>

    $ query = " INSERT INTO R2000 (Qno, RType) VALUES (333,NULL) ";

    $result= mysql_query($query);

    $query = "UPDATE R2000 SET RType='' ";

    $result= mysql_query($query);

    $query1 = " SELECT * FROM R2000 WHERE RType IS NULL" ;

    etc ...etc.... (I omitted the error checking stuff from this posting for brevity's sake).

    Thanks !
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You are not defining a WHERE clause in your update. I believe that is required in update statements.

    TAICBW
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    RTFM, Rod. Yes, I was wrong, you don't need a where clause.

    I'm assuming it WILL work in the opposite direction? i.e. that you can set it to an empty string on insert and then change to null?
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    2
    Rep Power
    0
    No, actually that doesn't work either - it seems like I can't actually alter the value of a CHAR(0) field with a PHP MySQL query, which just doesn't sound right !

Similar Threads

  1. Replies: 5
    Last Post: January 11th, 2004, 08:19 PM
  2. error in returned object
    By davidl in forum Java Help
    Replies: 6
    Last Post: October 9th, 2003, 04:34 PM
  3. Problem to insert records
    By carlmty in forum PHP Development
    Replies: 2
    Last Post: March 18th, 2003, 11:04 AM
  4. Product Catalog Woes
    By kingmike in forum MySQL Help
    Replies: 6
    Last Post: December 26th, 2002, 01:32 AM
  5. mySQL insert query woes
    By bhlinder in forum MySQL Help
    Replies: 3
    Last Post: October 26th, 2002, 09:19 PM

IMN logo majestic logo threadwatch logo seochat tools logo