SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IF NULL THEN statement

    the IF statement can't recognize a "NULL" in a column of INT?


    Code:
    FETCH c INTO x;
    
    # lets say the first x = NULL.
    
    IF x = NULL THEN
    ...
    END IF;
    That IF statement won't execute

    next when:
    x=1
    Code:
    IF x = 1 THEN
     ...
     END IF;
    the If statement executes. What's up with that?

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,604
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    NULL is never equal to anything else not even NULL.

    use ISNULL() to test if something is NULL.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i prefer COALESCE
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,457
    Mentioned
    160 Post(s)
    Tagged
    1 Thread(s)
    Are you sure it's NULL and not empty?

    @felgall, I use
    PHP Code:
        if ( mysql_result($result,$i,'fieldname') != NULL )
        {
            echo 
    "..... mysql_result($result,$i,'fieldname') ..... 
    and it seems to work OK. That is, it doesn't echo the "....."s when the field is NULL

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,604
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Are you sure it's NULL and not empty?

    @felgall, I use
    PHP Code:
        if ( mysql_result($result,$i,'fieldname') != NULL )
        {
            echo 
    "..... mysql_result($result,$i,'fieldname') ..... 
    and it seems to work OK. That is, it doesn't echo the "....."s when the field is NULL
    A mySQL NULL value and a PHP NULL value are not exactly the same thing and it may have something to do with the difference between the way NULLs are implemented in both. I think you'll find that trying to compare to NULL within the SQL is where it will not work. I was assuming that the query was with regard to testing the field for NULL in the SQL itself.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,457
    Mentioned
    160 Post(s)
    Tagged
    1 Thread(s)
    :d'oh: of course. I'm too darn PHP-centric at times.
    I tried finding info on FETCH without any luck (yet), but I did see documentation for queries like
    Where field IS NULL
    so the "IS" instead of the "=" is what to use for MySQL as far as NULLs are concerned.

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,604
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Mittineague View Post
    I tried finding info on FETCH without any luck (yet),
    FETCH is used where you have opened a cursor to hold the results of a query and you want to return the next record from the results in the cursor.

    Either x IS NULL or ISNULL(x) should work to test if x contains null. I suppose which of them to use would depend on which fitted better with the way the surrounding SQL is coded. I wouldn't expect it would make any difference to the efficiency of the processing (although I could be wrong as I have never actually tested it).
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mittineague View Post
    so the "IS" instead of the "=" is what to use for MySQL as far as NULLs are concerned.
    and every other database system, too
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks everyone, i understand now, thanks for the suggestions.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •