SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast earthlingzed's Avatar
    Join Date
    Jul 2004
    Location
    athens, ga
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL: Select Stuff Where Not Null

    I am trying to grab some records from MySQL WHERE the field is not Null and am getting syntax errors. As far as I can tell from research on the MySQL site, my SQL should be correct but it is not working.

    PHP Code:
    $sql "SELECT * FROM px_items WHERE read NOT NULL"
    Anyone know how to do this?

  2. #2
    SitePoint Enthusiast Refresh's Avatar
    Join Date
    Jul 2004
    Location
    Lausanne, Switzerland
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by earthlingzed
    I am trying to grab some records from MySQL WHERE the field is not Null and am getting syntax errors. As far as I can tell from research on the MySQL site, my SQL should be correct but it is not working.

    PHP Code:
    $sql "SELECT * FROM px_items WHERE read NOT NULL"
    Anyone know how to do this?

    Try with
    PHP Code:
    $sql "SELECT * FROM px_items WHERE read IS NOT NULL"

  3. #3
    SitePoint Enthusiast earthlingzed's Avatar
    Join Date
    Jul 2004
    Location
    athens, ga
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I gave that a shot and I am still getting a syntax error:

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'read IS NOT NULL' at line 1

  4. #4
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check the mysql reserved words list...
    http://dev.mysql.com/doc/mysql/en/Reserved_words.html

    Best not to use 'read' in this case
    Lats...

  5. #5
    SitePoint Enthusiast earthlingzed's Avatar
    Join Date
    Jul 2004
    Location
    athens, ga
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lats
    Check the mysql reserved words list...
    http://dev.mysql.com/doc/mysql/en/Reserved_words.html

    Best not to use 'read' in this case
    Wow, that would ahve never crossed my mind. I will try using something else.

  6. #6
    SitePoint Zealot
    Join Date
    May 2004
    Location
    houston
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found that if you are using a 'reserved' word, you can use the table name in front of it... like...

    Code:
    SELECT table.* FROM table WHERE table.read NOT NULL
    That would probably work, i was doing something along the same lines the other day, but not the same reserved word, although its probably not good programming practice to use the words.. hehe.. we can all circumvent that though can we not? -grins-

  7. #7
    SitePoint Enthusiast earthlingzed's Avatar
    Join Date
    Jul 2004
    Location
    athens, ga
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It looks like that was the problem. Thanks for the tip!

  8. #8
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    362
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could also wrap backticks (` which is next to the 1 key) around the field name.

  9. #9
    SitePoint Enthusiast earthlingzed's Avatar
    Join Date
    Jul 2004
    Location
    athens, ga
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks all for the help on this. Much good advice!


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
  •