...

View Full Version : Need to check if vaule is NOT NULL



david56connor
06-17-2011, 10:59 PM
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.

MattF
06-18-2011, 12:08 AM
SELECT values FROM table WHERE column NOT NULL;

david56connor
06-18-2011, 12:27 AM
SELECT values FROM table WHERE column NOT NULL;


Thanks for your response.

This is what I tried:

SELECT * FROM characters WHERE GroupName NOT NULL;

And this is the error message that I got from trying it:

#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:

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 :)

MattF
06-18-2011, 12:34 AM
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. :D

david56connor
06-18-2011, 12:49 AM
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. :D

Hah, it's my own fault too, I had about 50% of the SQL statement in my thread title! :)

Were only human!

bazz
06-18-2011, 01:43 AM
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

david56connor
06-18-2011, 01:51 AM
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! :)

Old Pedant
06-18-2011, 05:56 AM
As a minor refinement on all this...

If you *also* want to exclude rows where GroupName is a blank string ('') you can do this:


SELECT * FROM characters WHERE IFNULL(GroupName,'') <> ''


Look up IFNULL in the MySQL docs. It's really handy for this kind of thing.

david56connor
06-18-2011, 07:48 PM
As a minor refinement on all this...

If you *also* want to exclude rows where GroupName is a blank string ('') you can do this:


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.

Old Pedant
06-18-2011, 09:08 PM
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:


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum