Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Dec 2010
    Posts
    36
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Need to check if vaule is NOT NULL

    Hi, I need to check a database table to see which rows already have a value assigned to a certain field.

    So if there are 3 fields and field 2 has 2 possibilities, either empty, "" or it could be anything up to 10 random characters "group1" or "group10".

    I want to retrieve the rows that have a group assigned to them.

    One option I had thought of was to retrieve all the records from the table and handle them through PHP but was wondering if there was an easier way.

    Sorry if I didn't explain this very well, I find it hard explaining things sometimes

    Any help would be appreciated with this!

    Many Thanks,
    Dave.

  • #2
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Code:
    SELECT values FROM table WHERE column NOT NULL;

  • #3
    New Coder
    Join Date
    Dec 2010
    Posts
    36
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MattF View Post
    Code:
    SELECT values FROM table WHERE column NOT NULL;
    Thanks for your response.

    This is what I tried:
    Code:
    SELECT * FROM characters WHERE GroupName NOT NULL;
    And this is the error message that I got from trying it:
    Code:
    #1064 - 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 'NULL LIMIT 0, 30' at line 1
    Not sure where I'm going wrong. Any ideas?

    EDIT: Sorted it, it was missing the "IS" before "NOT NULL", full working query:
    Code:
    SELECT `CharName` FROM characters WHERE `GroupName` IS NOT NULL
    Again, Many thanks for your help, I didn't know you could do that at all, a little more research is what I needed to do! Sorry for the trouble
    Last edited by david56connor; 06-18-2011 at 12:32 AM.

  • #4
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Code:
    SELECT * FROM characters WHERE GroupName IS NOT NULL;

    Edit: Just noticed your update above. A bit of rustiness on my part with the SQL syntax was at fault there.
    Last edited by MattF; 06-18-2011 at 12:36 AM.

  • #5
    New Coder
    Join Date
    Dec 2010
    Posts
    36
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MattF View Post
    Code:
    SELECT * FROM characters WHERE GroupName IS NOT NULL;

    Edit: Just noticed your update above. A bit of rustiness on my part with the SQL syntax was at fault there.
    Hah, it's my own fault too, I had about 50% of the SQL statement in my thread title!

    Were only human!

  • #6
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    not sure if I understood the detail of your db column but, where there is no value to be stored, it is better to make the column nullable rather than storing an empty value or a 0.

    And as a side thought, if all the occupied values are group1, group2 group3 etc, you might be better off with a different column nmae and then drop the word 'group' from the column content.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #7
    New Coder
    Join Date
    Dec 2010
    Posts
    36
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bazz View Post
    not sure if I understood the detail of your db column but, where there is no value to be stored, it is better to make the column nullable rather than storing an empty value or a 0.

    And as a side thought, if all the occupied values are group1, group2 group3 etc, you might be better off with a different column nmae and then drop the word 'group' from the column content.

    bazz
    Yea after I knew how to retrieve values that are not null I made the column nullable.

    The user can enter whatever they want as the group name so your 2nd suggestion isn't really an option.

    Thanks for your interest!

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    As a minor refinement on all this...

    If you *also* want to exclude rows where GroupName is a blank string ('') you can do this:
    Code:
    SELECT * FROM characters WHERE IFNULL(GroupName,'') <> ''
    Look up IFNULL in the MySQL docs. It's really handy for this kind of thing.

  • #9
    New Coder
    Join Date
    Dec 2010
    Posts
    36
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    As a minor refinement on all this...

    If you *also* want to exclude rows where GroupName is a blank string ('') you can do this:
    Code:
    SELECT * FROM characters WHERE IFNULL(GroupName,'') <> ''
    Look up IFNULL in the MySQL docs. It's really handy for this kind of thing.
    Alright cheers, though I think for the sake of simplicity I will stick with either having a value, or being NULL.

    Thanks,
    David.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Well, you said that people could enter any name they wanted for a group. What happens if they enter (say) a string of 6 space?

    If you also use TRIM() then you can handle that case:
    Code:
    SELECT * FROM characters WHERE TRIM(IFNULL(GroupName,'')) <> ''
    But of course if you validated the entries on the way in, this is indeed a waste of code.


  •  

    Posting Permissions

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